swirl
Guide to OmniMark 8   OmniMark home
docs home 
IndexConceptsTasksSyntaxLibrariesLegacy LibrariesErrors
 
      Other Library Functions  
function  

dbStatementStreamingExecute

 
 

Library: Database access (OMDB) legacy library
Include: omdb.xin

Declaration

define external function dbStatementStreamingExecute
            value       dbStatement statement
   with     value       dbField     insert-handle
   record   modifiable  dbField     record   optional

Argument definitions

statement
is a compiled dbStatement object.
insert-handle
is a dbField which will be given as an argument to dbFieldPrepare and dbFieldSink to refer to the current streaming execute operation.
record
is a shelf of dbField items bound to result set fields.


Purpose

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.

Requirements

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).

Usage Notes

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.

Database Interface Notes

Example #1:

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

        Other Library Functions
   dbClose
   dbCommit
   dbDatabase
   dbExecute
   dbField
   dbFieldPrepare
   dbFieldSink
   dbFieldValue
   dbIsVersionCompatible
   dbLibraryVersion
   dbLibraryVersionOCI8
   dbLibraryVersionOCI8i
   dbLibraryVersionODBC
   dbOpenOCI8
   dbOpenOCI8i
   dbOpenODBC
   dbProcedure
   dbProcedureClose
   dbProcedureExecute
   dbProcedureOpen
   dbQuery
   dbRecordExists
   dbRecordMove
   dbRecordMoveDynamic
   dbRecordSetMove
   dbRollback
   dbSetTransactionType
   dbStatement
   dbStatementCompile
   dbStatementDiscard
   dbStatementExecute
   dbStatementStreamingExecute
   dbTable
   dbTableClose
   dbTableDelete
   dbTableInsert
   dbTableOpen
   dbTableUpdate
 
 

Top [ INDEX ] [ CONCEPTS ] [ TASKS ] [ SYNTAX ] [ LIBRARIES ] [ LEGACY LIBRARIES ] [ ERRORS ]

OmniMark 8.2.0 Documentation Generated: March 13, 2008 at 3:27:39 pm
If you have any comments about this section of the documentation, please use this form.

Copyright © Stilo International plc, 1988-2008.