Database Interface

To modify or query a relational database, programmers must find some way to create the often long English-like text sentences, known as Structured Query Language (SQL) by which one tells a database what to do. Fixed and unvarying SQL commands can be predeployed on the server as stored procedures. But this is more difficult for highly variable commands. Insert and update commands, for instance, will not always involve a fixed set of columns for a given table. The complete set of values may not even be known within a particular program scope. And so the developer could be forced into the situation of writing a lot of conditional text-related SQL command-generation code, splicing a variable set of values (or variable names) into SQL syntax words and phrases. This is painful, error-prone, difficult to debug, and brittle within a changing context.

Because of the text-only nature of SQL, non-text variables such as integers, dates, decimals and bits must be conveyed separately to the database in their native form (a process known as parameterization) in order, in part, to allow the database to cache the query for reuse. This means that not only must the developer come up with these complex and conditionally-shaped SQL text commands, but also, along side those commands, send discrete non-text values to the server, synchronizing the one with the other.

Further, client-side program code is not supposed to know enough about physical storage requirements to generate SQL statements in the first place. The middle tier server back at the datacenter is normally the authorized agent for such a task. The client must somehow inform the middle tier of its highly variable requirements without actually generating SQL. How is one to do this, particularly in a smart-client context? Sending a few fixed values back to the middle tier is one thing, but what happens when the range of possibilities is huge?

Ironically, this problem is not as acute in a thin-client browser context, because the real program code, even that of the user interface, is running at the server level anyway. The client merely manages a user interface projected to it from the server. But this leads to another problem, namely, preserving and updating the state of rows, relative to database activity, displayed on client screens. The default tendency for browser-based programming is for everything to forget everything between exchanges with the server and not to maintain state at all—at least, not without complex, kludged and expensive measures to do so.

Another problem involves managing the insertion of rows that contain a database-generated auto-incrementing identity value. This value, which becomes known only after the new row is inserted, must often be sent in two different directions. First, if the smart client has these same rows in memory (or if the browser application has gone to extraordinary lengths to maintain row state), the new identity values will need to be planted into each originating memory row, typically in cases involving a grid of rows that the user might continue to edit. Second, it must be sent to other child rows already at the server within the same update batch.

The database interface layer of the Veriquant Framework solves each of these problems. Developers do not write code to generate SQL; this, including parameterization, is done automatically by the Framework. To use the database, programmers need only write simple assignment statements and make method calls. The client formulates and transmits highly variable database commands to the middle tier in a simple form other than SQL. The Framework further manages identity value propagation and maintains database row state on the client.

Further information is available to logged-in users here.