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

Return type: dbProcedure Returns:  A specific procedure of type dbProcedure.

Declaration

  define external db.procedure function db.open-procedure
    in               value      db.database  database
    named            value      stream      procedure
    parameter-types  read-only  int32       parameters optional
    data-types       read-only  int32       datatypes  optional
    data-sizes       read-only  int32       datasize   optional

Argument definitions

database

is a db.database object representing an open database connection.

procedure

is the name of the stored procedure.

parameters

is a keyed list of the parameter modes used in the procedure.

datatypes

is a keyed list of the SQL data types of the procedure parameters.

datasizes

is a keyed list of the size (expressed as number of digits or number of characters) of the procedure parameters.

Use db.open-procedure to open a connection to a stored procedure in the database.

Requirements

procedure must:

• exist (else external exception OMDB402)
• have a name (else external exception OMDB408)

With some ODBC drivers, you must supply all three procedure parameter description arguments (else external exception OMDB409) if your procedure uses parameters. The procedure parameter description shelves (parameters, datatypes and datasizes) must

• contain the same number of items (else external exception OMDB410)
• have the same item keys (else external exception OMDB411)

The parameter types must be valid (else external exception OMDB403). The following macro-defined parameter types are allowed:

• db.return-value
• db.input-parameter
• db.input-output-parameter
• db.output-parameter

Values supplied for datasizes cannot be negative numbers (else external exception OMDB412).

Usage Notes

The key names of output parameters are used to key the "out" db.field parameter in dbProcedureExecute.

The following SQL datatypes are supported by this function:

• db.sql-char
• db.sql-varchar
• db.sql-decimal
• db.sql-numeric
• db.sql-smallint
• db.sql-integer
• db.sql-real
• db.sql-float
• db.sql-double
• db.sql-bit
• db.sql-tinyint
• db.sql-bigint
• db.sql-binary
• db.sql-varbinary
• db.sql-datetime
• db.sql-date
• db.sql-time
• db.sql-timestamp
• db.sql-type-date
• db.sql-type-time
• db.sql-type-timestamp

Database Interface Notes

• ODBC - whether or not the three descriptive arguments are required depends on the ODBC driver.
• OCI8 - LOB parameters are not supported. The three descriptive arguments are ignored.
• OCI8i - full support. The three descriptive arguments are ignored.

Example #1

In this example, a stored procedure that processes a customer purchase transaction is executed in the OmniMark program. The stored procedure, "PurchaseItem", takes four parameters:

1. CustomerID - a unique value identifying a single customer
2. ProductID - a unique value identifying a single product
3. ItemCount - the number of items requested by the customer
4. InventoryCount - the number of items remaining in inventory after the current sale

The first two parameters are only read by the procedure. The ItemCount parameter is read in the procedure and the total number of items sold to the customer is written into the variable at the end of the procedure. If there are fewer than the requested number of items in inventory, the written value will be less than the initial value. The inventory count is only written to by the procedure and it contains the total number of items remaining in inventory after the sale. The procedure also returns a value. (This type of stored procedure is called a stored function on some databases.) The value returned is the total cost to the customer for the purchase.

  do
     ; process parameter values
     local stream CustomerID initial { '7734' }
     local stream ProductID  initial { '34-RTS-485643' }
     local stream ItemCount  initial { '47' }

     ; local variables
     local db.procedure purchase-item
     local stream in-val variable
     local db.field out-val variable
     local db.field res-val variable
     local integer types variable initial
        {  db.return-value           with key 'RETURN_VALUE'
        ,  db.input-parameter        with key 'CustomerID'
        ,  db.input-parameter        with key 'ProductID'
        ,  db.input-output-parameter with key 'ItemCount'
        ,  db.output-parameter       with key 'InventoryCount'
        }
     local integer datatypes variable initial
        {  db.sql-double  with key 'RETURN_VALUE'
        ,  db.sql-decimal with key 'CustomerID'
        ,  db.sql-varchar with key 'ProductID'
        ,  db.sql-decimal with key 'ItemCount'
        ,  db.sql-decimal with key 'InventoryCount'
        }
     local integer datasizes variable initial
        {  15 with key 'RETURN_VALUE'
        ,  38 with key 'CustomerID'
        ,  50 with key 'ProductID'
        ,  38 with key 'ItemCount'
        ,  38 with key 'InventoryCount'
        }

     ; open the procedure
     set purchase-item to db.open-procedure
        in inventory-db
        named 'PurchaseItem'
        parameter-types types
        data-types datatypes
        data-sizes datasizes

     ; set the input values
     set new in-val{ 'CustomerID' } to '7734'
     set new in-val{ 'ProductID' }  to '34-RTS-485643'
     set new in-val{ 'ItemCount' }  to '47'

     ; execute the procedure
     db.execute purchase-item in in-val out out-val result res-val

     ; display the processing results
     output 'Customer %g(CustomerID '  ||
            'requested %g(ItemCount) ' ||
            'units of %g(ProductID)%n'
     output db.reader of out-val{ 'ItemCount' }      ||
            ' units were sold%n'
     output db.reader of out-val{ 'InventoryCount' } ||
            ' units remaining in inventory%n'
     output 'Total value of the sale was $'        ||
            db.reader of res-val{'RETURN_VALUE'}     || '%n'

  done

Related Syntax    db.database    dbProcedureClose    db.execute    db.procedure

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.execute    db.execute-in    db.field    db.insert    db.move-record    db.omdb-version    db.omdb-version-oci8    db.omdb-version-oci8i    db.omdb-version-odbc    db.open-oci8    db.open-oci8i    db.open-odbc    db.open-procedure    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 ] [ LEGACYLIBRARIES ] [ ERRORS ]