SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › SIMULATE DATACOM PROCEDURE Statement

SIMULATE DATACOM PROCEDURE Statement

(DBSQLPR only) The SIMULATE DATACOM PROCEDURE statement provides support in DBSQLPR for SQL variables, user-controlled cursors, and complex program logic.

►►─ SIMULATE DATACOM PROCEDURE ─ compound-statement ──────────────────────────►◄

compound-statement

The compound statement is described in Compound Statement. The start-label of the compound statement is required in the context of the SIMULATE DATACOM PROCEDURE statement and becomes the SQL-name of an actual procedure that is created, executed, and then dropped.

To simulate a procedure that itself has parameters, either simulate the parameters by declaring SQL variables and assigning values or issue a CREATE PROCEDURE followed by a simulated call to that procedure as shown in the following example.

In the following example, SQL-names that do not consist of delimited identifiers are converted to upper-case by SQL. Also, if you need confirmation of the values returned into the ordersAccepted and ordersRejected variables, use the DATACOM DUMP statement (see DATACOM DUMP Statement) or insert the values into a table, for retrieval outside of the procedure, by subsequent SELECT statements. The ORDERREVIEW procedure called from this example is created by the example SQL procedure that begins in Examples.

 SIMULATE DATACOM PROCEDURE
 supplyVarsForCall: begin atomic
    declare ordersAccepted, ordersRejected int;
    CALL ORDERREVIEW(ordersAccepted, ordersRejected);
 end supplyVarsForCall@

Following is a SIMULATE PROCEDURE statement that calls the procedure created by the compound statement example in Example. This SIMULATE PROCEDURE statement contains the DATACOM DUMP example shown in Example.

 000034 simulate datacom procedure
 000035 supplyHostVarsForCall: begin atomic
 000036    declare errorMessage, result char(80) default 'N/A';
 000037 -- The default value for SQL vars is NULL
 000038    declare sqlstateLocal char(5);
 000039
 000040    declare continue handler for not found
 000041          set errorMessage = 'CUSTOMER HAS NO CREDIT RECORD';
 000042    declare continue handler for sqlexception, sqlwarning
 000043          get diagnostics sqlstateLocal = returned_sqlstate,
 000044                          errorMessage  = message_text;
 000045
 000046    datacom dump 'VALUES ON ENTRY:', result,errorMessage to pxxsql;
 000047    call creditLimitCheck(result, '00001', 999.99);
 000048    datacom dump 'VALUES ON EXIT:', result,sqlstateLocal,errorMessage to pxxsql;
 000049 --
 000050 -- In a more comprehensive example, procedure "creditLimitCheck" could have
 000051 -- returned an error message and sqlstate through the parameter list, with
 000052 -- the inclusion of an SQLEXCEPTION error handler containing a GET DIAGNOSTICS
 000053 -- call.  This would prevent the caller from having to code their own
 000054 -- GET DIAGNOSTICS statement to retrieve the information into SQL variables.
 000055 end supplyHostVarsForCall@