ODBC Server Reference Manual

Stored Procedures
HP NonStop ODBC Server Reference Manual—429151-002
5-24
Developing a Stored Procedure in COBOL
SP-BUFFER,
SP-REPLY,
SP-RC.
3. Set up the initialization code.
This code is called when the stored procedure is first executed. In this example, an
SQLDA is set up that will be used for returning the row descriptor and data, and
the SQL statement to be executed for querying the employee table is SQL
prepared.
1000-INITIALIZE.
MOVE "00" TO SQL-STATUS-SW.
MOVE "D1" TO EYE-CATCHER OF SDAO23.
MOVE SPE-23-MAX-ENTRIES TO NUM-ENTRIES OF SDAO23.
*-----------------------------------------------------------
* To retrieve column descriptions, a dynamic SQL DESCRIBE
* statement is needed, so you need to set up for the
* PREPARE/DESCRIBE stmts.
*-----------------------------------------------------------
MOVE SELECT-STMT TO SP-STMT.
EXEC SQL PREPARE SP23 FROM :SP-STMT END-EXEC.
*-----------------------------------------------------------
* DESCRIBE moves column definitions to sdaO23 and column
* names to colnames. This is used by ENCODE_ROW_DESCRIPTOR
*-----------------------------------------------------------
EXEC SQL
DESCRIBE SP23 INTO :SDAO23 NAMES INTO :SDAN23
END-EXEC.
EXEC SQL
DECLARE CUR23 CURSOR FOR SP23
END-EXEC.
4. Write the main code.
Three basic operations must be performed:
•
Call the initialization code, if needed.
•
Execute the main logic. The first item is to call the encode initialization routine.
For each result set returned, encode a row descriptor, then encode the row
data.
•
After returning all of the rows, encode an “end statement” message. Then
encode an “end procedure” message to indicate that the procedure execution
is completed, and set up the return code.
The following code illustrates these steps. The main loop is set to call the
initialization code (1000-INITIALIZE), execute the main logic (2000-PROCESS-
REQUEST and 3000-END-PROCESS), and then set up the return code.