|
|||||
|
||||||
Other Library Functions |
function |
dbProcedureOpen |
Library: Database access (OMDB legacy)
Include: omdb.xin |
Return type: dbProcedure Returns: A specific procedure of type dbProcedure. |
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 integer parameters (variable) data-types read-only integer datatypes (variable) data-sizes read-only integer datasize (variable)
Argument definitions
Use dbProcedureOpen to open a connection to a stored procedure in the database.
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 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).
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:
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:
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
Copyright © Stilo International plc, 1988-2010.