OTL stream concept

Any SQL statement, PL/SQL block or a stored procedure call is characterized by its input / output [variables].

Example 1. A SELECT statement has scalar input variables that are used in the WHERE clause of the statement. The SELECT statement also defines output columns. Potentially, the output columns are vector parameters since the SELECT statement may return multiple rows.

Example 2. An INSERT statement writes data into a table, i.e. it has input parameters. Same is true for UPDATE statemements.

Example 3. A DELETE statement deletes rows from a table. Deletion criteria needs to be entered, thus the DELETE statement has input.

Example 4. A stored procedure may have input and/or output parameters. Usually, stored procedure parameters are scalars. There is a special case, though: stored procedure returning a referenced cursor (Oracle) or a result set (MS SQL Server or Sybase).

Example 5. An arbitrary PL/SQL block may have input or/and output parameters that may be either scalars or vectors.

Industrial strength database servers have bulk (or array) operations:

• bulk INSERT
• bulk UPDATE
• bulk DELETE
• bulk SELECT

Therefore, parameters in INSERT/UPDATE/DELETE statement may be vectors if the statement is performed in bulk.

The picture is clear: any interaction with SQL or its procedural extension can be treated as a black box with input and/or output. It does not matter what the black box does inside (according to the definition of a black box). What matters is the input wires that send signals into the box and the output wires that receive signals from the box:

Some of the wires may be both input and output.

Why not combine the concept of data streams and SQL? Instead of multiplying constructs and making database API's too convoluted, why not unify and simplify them? The OTL gives an answer to those questions and the answer is the otl_stream class.

Since a SQL statement may be done in bulk, the otl_stream is a buffered stream. Conceptually, the otl_stream has two separate buffers: input and output. The input buffer is comprised of all input variables put together. Respectively, the output buffer is comprised of all output variables put together.

C++ streams are usually manipulated via operator >> and operator <<. The stream reference is on the left of the operator symbol:

   s>>variable;
s<<variable;

The double arrow shows the direction in which data goes:

• >> -- from the stream into the data container (variable)
• << -- from the data container (variable) into the stream

OTL streams are similar to buffered C++ streams . A SQL statement or stored procedure call is opened as an ordinary buffered stream. The logic of the OTL stream operations remains the same as the C++ stream operations with the only exception -- the OTL stream has separate input and output buffers which may overlap.

The OTL stream has a flush function for flushing its input buffer when the buffer gets full and a collection of >> and << operators for reading and writing objects of different data types. The most important advantage of the OTL streams is their unified interface to SQL statements and stored procedure call of any kind. This means that the application developer needs to remember just a few syntactical constructs and function names which he already got familiar with when he started working with C++ streams.

Inside the OTL stream there is a small parser for parsing declarations of bind variables and their data types. There is no need to declare C/C++ host variables and bind them with placeholders by special bind function calls. All necessary buffers are created dynamically inside the stream. The stream just needs to be opened for reading and writing values.

The OTL stream interface requires use of the OTL exceptions. This means that potentially any OTL stream operation can throw an exception of the otl_exception type. In order to intercept the exception and prevent the program from aborting, wrap up the OTL stream code with the corresponding try & catch block.

The functioning of the otl_stream is pretty much automatic: when all of the input variables of the stream are defined (in other words, the input buffer is filled out), it triggers the block box inside the stream to execute. The output buffer gets filled out in the process of the execution of the black box. After the execution is finished, the output values can be read from the stream. If it is a SELECT statement and it returns more rows than the output buffer can hold, after the whole output buffer is read, then the stream automatically fetches the next bacth of rows into the output buffer.