Library: Database access (OMDB) Import: omdb.xmd

Declaration

define external function db.streaming-execute
            value       db.statement statement
   using     value       db.field     insert-handle
   into     modifiable  db.field      record   optional

Argument definitions

statement

is a compiled db.statement object.

insert-handle

is a db.field which will be given as an argument to db.prepare and db.writer to refer to the current streaming execute operation.

record

is a shelf of db.field items bound to result set fields.

Use db.streaming-execute to execute a compiled SQL statement that was produced by the db.compile-statement function. This function provides a much more efficient manner of streaming large amounts of data into a database than db.execute. Each parameter in the compiled statement is streamed in individually and thus does not require being entirely read into memory beforehand.

Requirements

The db.database object supplied when the statement was compiled must be open (else external exception OMDB101).

The db.statement object statement must be open (else external exception OMDB704).

Any previous streaming execute on the supplied db.statement object must have already been completed (else external exception OMDB710).

If the db.statement object is a compiled query, it requires the db.field shelf argument record to receive the results (else external exception OMDB704). The members of the db.field 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 db.prepare 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 db.streaming-execute, a program needs to resemble the following once the SQL statement has been compiled:

  local db.statement stmt
  local db.field insert-handle
  local db.field out-vals variable
  local stream in-stream
  
  .  .  .
  db.streaming-execute stmt with insert-handle into out-vals
  repeat
     exit when not db.prepare insert-handle
  
     open in-stream as db.writer of 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 db.writer for that parameter, or do not write any data to the external output returned from db.writer. 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

• ODBC - support for streaming data into LONG fields depends on the ODBC driver and the destination database.
• OCI- supports streaming data into LONG and LOB fields, with the exception of multi-row updates on LONG fields (for example, "update mytable set LONGFIELD to :1 where NAME = 'Mark' or NAME = 'James'").

Example

Insert a date, a NULL, and a large binary file into an Oracle 8i (or newer) database using the OCI interface.

  import "omdb.xmd" prefixed by db.
  
  process
     local db.database  db
     local db.statement stmt
     local db.field     insert-handle
     local integer     param-types variable initial-size 0
  
     set db to db.open-oci8i "MyDatabaseServer" user "charley" password "chaplin"
  
     ; OCI interface requires that types be specified for compiled statements
     set new param-types{"DATEFIELD"} to db.datetime-type
     set new param-types{"TEXTFIELD"} to db.text-type
     set new param-types{"BLOBFIELD"} to db.blob-type
  
     set stmt to db.compile-statement in db sql "insert into MyTable values (:1, :2, :3)" types param-types
  
     db.streaming-execute stmt using insert-handle
     repeat
        exit when not db.prepare insert-handle
  
        using output as db.writer of insert-handle
        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
    again

Related Concepts    Using the OMDB library

Other Library Functions    db.advance-recordset    db.close    db.commit    db.compile-statement    db.database    db.delete    db.discard    db.document-writer-sedna    db.execute    db.execute-in    db.field    db.insert    db.is-null    db.move-dynamic-record    db.move-record    db.omdb-version    db.omdb-version-oci10g    db.omdb-version-oci11g    db.omdb-version-oci8i    db.omdb-version-oci9i    db.omdb-version-odbc    db.omdb-version-sedna    db.open-oci10g    db.open-oci11g    db.open-oci8i    db.open-oci9i    db.open-odbc    db.open-procedure    db.open-sedna    db.open-table    db.prepare    db.procedure    db.query    db.reader    db.record-exists    db.rollback    db.set-transaction-type    db.statement    db.streaming-execute    db.table    db.update    db.writer

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