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

db.open-procedure

 
 

Library: Database access (OMDB)
Import: omdb.xmd
Return type: db.procedure

Returns: A specific procedure of type db.procedure.


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.


Purpose

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

Requirements

procedure must:

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

The keys may be in a different order.

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

These are exported from omdb.xmd as constants.

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

Usage Notes

The key names of output and inout parameters are used to key the "out" db.field parameter in db.execute. In addition, an item keyed "RETURN-VALUE" will be added to the shelf if the procedure returns a value.

The following SQL datatypes are supported by this function:

However, some of these datatypes may not be supported on some databases and ODBC drivers (external exception OMDB501 if not supported).

Database Interface Notes

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.

Example 1


    do
       ; local variables
       local db.procedure purchase-item
       local stream in-val variable
       local db.field out-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
  
       ; display the processing results
       output 'Customer '  || in-val{ 'CustomerID' } ||
              ' requested ' || in-val{ 'ItemCount' } ||
              ' units of ' || in-val{ '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 out-val{ 'RETURN_VALUE' } || '%n'
  
    done

Example 2 - parameter types not required


   do
       ; local variables
       local db.procedure purchase-item
       local stream in-val variable
       local db.field out-val variable
  
       ; open the procedure
       set purchase-item to db.open-procedure
          in inventory-db
          named 'PurchaseItem'
  
       ; 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
  
       ; display the processing results
       output 'Customer '  || in-val{ 'CustomerID' } ||
              ' requested ' || in-val{ 'ItemCount' } ||
              ' units of ' || in-val{ '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 out-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.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
 
 

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.