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

dbProcedureOpen

 
 

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

Returns: A specific procedure of type dbProcedure.


Declaration

define external dbProcedure function dbProcedureOpen
             value  dbDatabase  database
  procedure  value  stream      procedure

or

define external dbProcedure function dbProcedureOpen
                  value      dbDatabase  database
       procedure  value      stream      procedure
 parameter-types  read-only  int32       parameters (variable)
      data-types  read-only  int32       datatypes  (variable)
      data-sizes  read-only  int32       datasize   (variable)

Argument definitions

database
is a dbDatabase 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 dbProcedureOpen to open a connection to a stored procedure in the database.

Requirements

You must include the following line at the beginning of your OmniMark program:

  include "omdb.xin"

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 macro-defined parameter types are allowed:

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" dbField parameter in dbProcedureExecute.

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.

  do
  
     ; process parameter values
     local stream CustomerID initial { '7734' }
     local stream ProductID  initial { '34-RTS-485643' }
     local stream ItemCount  initial { '47' }
  
     ; local variables
     local dbProcedure purchase-item
     local stream in-val variable
     local dbField out-val variable
     local dbField res-val variable
     local integer types variable initial
        {  SQL_RETURN_VALUE       with key 'RETURN_VALUE'
        ,  SQL_PARAM_INPUT        with key 'CustomerID'
        ,  SQL_PARAM_INPUT        with key 'ProductID'
        ,  SQL_PARAM_INPUT_OUTPUT with key 'ItemCount'
        ,  SQL_PARAM_OUTPUT       with key 'InventoryCount'
        }
     local integer datatypes variable initial
        {  SQL_DOUBLE  with key 'RETURN_VALUE'
        ,  SQL_DECIMAL with key 'CustomerID'
        ,  SQL_VARCHAR with key 'ProductID'
        ,  SQL_DECIMAL with key 'ItemCount'
        ,  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 dbProcedureOpen inventory-db
        procedure '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
     dbProcedureExecute 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 dbFieldValue out-val{ 'ItemCount' }      ||
            ' units were sold%n'
     output dbFieldValue out-val{ 'InventoryCount' } ||
            ' units remaining in inventory%n'
     output 'Total value of the sale was $'        ||
            dbFieldValue res-val{'RETURN_VALUE'}     || '%n'
  
  done

        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.