function
Library: Database access (OMDB)
Import : omdb.xmd |
Returns: A specific procedure of type db.procedure. |
define external db.procedure function db.open-procedure in value db.database database named value stream procedure parameter-types read-only integer parameters optional data-types read-only integer datatypes optional data-sizes read-only integer datasize optional
Argument definitions
Use db.open-procedure to open a connection to a stored procedure in the database.
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 parameter types are allowed:
export
ed from omdb.xmd as constant
s.
Values supplied for datasizes cannot be negative numbers (else external exception OMDB412).
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:
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 ; 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
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