Example 2. An INSERT statement writes data into a table, i.e. it has input parameters. Same is true for UPDATE statements.
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, DB2, Sybase, etc.).
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:
Therefore, parameters in INSERT/UPDATE/DELETE statement may be vectors if the statement is performed in bulk.
Any interaction with SQL or its procedural extensions 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 provides an answer to such 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:
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 stream is its unified interface to SQL statements and stored procedure call of any kind. This means that the interface is drastically simplified and still familiar because of the use of the C++ stream idiom. As a result, the application developer will have more time for productive work.
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. 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 blocks.
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 batch of rows into the
output
buffer.
Copyright © 1996-2024, Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com .
Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.