SQL processing

SQL statements are dynamically generated by the database dependent (specific) layer of the LN MSQL database driver. Because the application virtual machine interprets code dynamically and the LN tools allow for modifications to the applications, it is not known in advance which tables are accessed at run time; therefore it is not practical to prepare the queries before run time.

When the LN MSQL driver receives a query from the application virtual machine, the query is translated into a format suitable for SQL Server. The text of the query is transferred to the SQL Server using ODBC function calls. The database driver makes a function call to the ODBC driver manager to allocate a statement handle, and the query is executed by assigning it to the statement handle and calling the ODBC query-execute function. Sometimes, SQL Server opens a server cursor internally for query execution. After the query is executed, a fetch operation is done and the resulting column values are placed in the bound result variables. Control is returned to the database independent layer of the LN MSQL database driver, which sends the results back to the application virtual machine.

When a statement must be re-executed, the cursor from the previous execution is closed. The resulting rows are discarded, whether the re-execution is with the same input parameters or not. If new input values are required, the new values are assigned to the input parameter bind variables and the query is re-executed. For re-execution, no re-parse of the statement or re-bind of input and output parameters is typically required. Because binding is an expensive operation, avoiding it when possible can improve overall performance.

When array fetching is enabled, multiple rows can be fetched in one ODBC call to the server. Space is allocated within the driver to buffer multiple rows that are fetched in one operation. Multiple rows can be fetched to the buffer; and they are returned to the application virtual machine when requested. When no rows are left in the buffer and more rows are requested, another fetch operation is executed.

Inserts can also be buffered. When array inserting is enabled, the driver places the rows to be inserted in a buffer. When the buffer is full, or when some other event necessitates it, the rows are flushed to the database. The rows in the buffer are inserted with a multi-row insert.

For example, when using bdbpost, the rows are buffered by default and are flushed when the array buffer is full. The array size must be specified; otherwise buffering is not done. The array buffer size can be specified globally, with an environment variable or resource variable. The environment variables and resource variables are explained in the next section.