|
|||||
|
||||||
Other Library Functions |
function |
dbStatementStreamingExecute |
Library: Database access (OMDB legacy)
Include: omdb.xin |
define external function dbStatementStreamingExecute value dbStatement statement with value dbField insert-handle record modifiable dbField record optional
Argument definitions
Use dbStatementStreamingExecute to execute a compiled SQL statement that was produced by the dbStatementCompile function. This function provides a much more efficient manner of streaming large amounts of data into a database than dbStatementExecute. Each parameter in the compiled statement is streamed in individually and thus does not require being entirely read into memory beforehand.
You must include the following line at the beginning of your OmniMark program:
include "omdb.xin"
The dbDatabase object supplied when the statement was compiled must be open (else external exception OMDB101).
The dbStatement object statement must be open (else external exception OMDB704).
Any previous streaming execute on the supplied dbStatement object must have already been completed (else external exception OMDB710).
If the dbStatement object is a compiled query, it requires the dbField shelf argument record to receive the results (else external exception OMDB704). The members of the dbField shelf cannot be accessed until the streaming execute operation has completed (else external exception OMDB205).
Note that the order in which the parameters are requested depends on the database driver. When dbFieldPrepare returns true (indicating that there is another parameter to be streamed in), the key of the insert-handle object is set to the number of the parameter to be inserted. The leftmost parameter in the compiled statement is parameter "1", the second is parameter "2", and so on.
To make use of dbStatementStreamingExecute, a program needs to resemble the following once the SQL statement has been compiled:
local dbStatement stmt local dbField insert-handle local dbField out-vals variable local stream in-stream . . . dbStatementStreamingExecute stmt with insert-handle record out-vals repeat exit when not dbFieldPrepare insert-handle open in-stream as dbFieldSink insert-handle do select key of insert-handle case 1 ; output data to in-stream case 2 ; output data to in-stream . . . done close in-stream again ; Now 'out-vals' can be accessed
If the parameter you are currently streaming in is a date or time parameter, you must express it in the OmniMark Date and Time library format.
To pass in a NULL value for a parameter, either do not call dbFieldSink for that parameter, or do not write any data to the external output returned from dbFieldSink. The one exception is that when using LOB parameters with the OCI interface, an empty LOB will be inserted instead of a NULL value.
Insert a date, a NULL, and a large binary file into an Oracle 8i (or newer) database using the OCI interface.
include "omdb.xin" process local dbDatabase db local dbStatement stmt local dbField insert-handle local stream param-input local integer param-types variable initial-size 0 set db to dbOpenOCI8i "MyDatabaseServer" user "charley" password "chaplin" ; OCI interface requires that types be specified for compiled statements set new param-types{"DATEFIELD"} to DB_DATETIME set new param-types{"TEXTFIELD"} to DB_TEXT set new param-types{"BLOBFIELD"} to DB_BLOB set stmt to dbStatementCompile db SQL "insert into MyTable values (:1, :2, :3)" types param-types dbStatementStreamingExecute stmt with insert-handle repeat exit when not dbFieldPrepare insert-handle open param-input as dbFieldSink insert-handle using output as param-input do select key of insert-handle case 1 output "19990830161027-0500" ; Skip parameter "2" so that NULL is inserted case 3 output binary-mode file "mylargefile.bin" done close param-input again
Copyright © Stilo International plc, 1988-2010.