SQL processing

The database-dependent layer of the LN Oracle database driver dynamically generates SQL statements. Because LN applications are dynamic, you cannot know in advance which tables will be used at run time; therefore, you cannot prepare the queries before run time.

In the LN Oracle database driver, the procedure to process SQL consists of several steps; these steps are described in this section.

If the LN Oracle driver receives a query from the application virtual machine, the query is translated into a format suitable for Oracle. To transfer the query to Oracle, you must use OCI function calls. In the LN Oracle database driver, you can allocate an Oracle cursor, and assign the query to the cursor. You must then parse the SQL statement, bind the input and output variables, and use the cursor to run the query. After you run the query, you perform a fetch operation and place the resulting column values in the bound output variables. The rows that Oracle returns are passed to the database independent layer of the LN Oracle database driver, which sends the results back to the application virtual machine.

If you must re-execute a statement, the cursor from the previous execution closes and the result 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 columns, and the query is re-executed.

For re-execution, no reparse of the statement or re-bind of input and output parameters is required, which improves the overall performance.

If array fetching is enabled, multiple rows are fetched in one call to the driver. To buffer multiple rows fetched in one operation, space is allocated in the driver. You can fetch multiple rows to the buffer, and, when requested, the rows are returned to the application virtual machine. If no rows are left in the buffer and more rows are requested, another array fetch operation is carried out.

You can also buffer inserts. If array inserting is enabled, the driver places the rows to be inserted in a buffer. If the buffer is full, or if necessitated by some other event, the rows are flushed to Oracle. The rows in the buffer are inserted with a multirow insert.

Note: To manually place data into the database, you can use the LN utility bdbpost6.2; you can use this utility to place data into a new database table or to append data to an existing database table. If you use bdbpost6.2, you can set particular options. If you use bdbpost6.2, by default the rows are buffered and flushed when the array buffer is full. You must specify the array size; otherwise, no buffering is carried out. To specify the array buffer size in the tabledef file on a per-table basis or globally, you can use an environment variable. The following sections describe environment variables, resource variables, and storage files. The section on environment variables briefly describes the tabledef file.