ODBC Server Reference Manual

Stored Procedures
HP NonStop ODBC Server Reference Manual—429151-002
5-15
Developing a Stored Procedure in C
The ADD PROCEDURE command registers the stored procedure
SELECT_INPUT_DEPT for user SQL.ODBC in the NonStop ODBC Server catalog
\TESS.$DATA1.DB, with the Pathmon process name of $EMPSP, server name of
EMP-DATA-DML, one result set, and a maximum reply buffer length of 2000 bytes.
The ADD PROCEDURE_COLUMNS command specifies one input parameter for
the stored procedure.
6. Set up the sample database (if not already installed):
Create the NonStop SQL/MP sample database by obeying the file DBCREATE and
load the data by obeying the file DBLOAD. The catalog for the sample database is
created in $xxx.PERSNL. The tables EMPLOYEE, DEPT, and so on, are created
and registered in the same location.
7. Execute the Stored Procedure:
a. Start the Pathway server; for example:
Pathmon /name $empsp/
Pathcom /in PATHIN/ $empsp
To add a new stored procedure after the Pathmon process has been started,
you need to freeze the server, stop the server, and start the server again.
b. Send in a request from the client to execute the stored procedure.
Coding a Stored Procedure in C
This subsection covers Steps 1 and 2 from Developing a Stored Procedure in C on
page 5-13 in greater detail, using the sample code for the stored procedure
SELECT_INPUT_DEPT.
1. Create the initialization routine.
This routine is called when the stored procedure is first started. This step is where
the SQLDA structures used within the stored procedure are set up, along with any
other data structures that need to be initialized.
In the sample file SP21C, the routine INITIALIZE_SPE_21 is used for initializing
the stored procedure SELECT_INPUT_DEPT. The following explains that
initialization code.
•
First, there are some global pointers set up for the SQLDA data structures that
have been declared for the input parameter, result set, and column headings:
/* -- number of columns -- */
#define SPE_21_max_entries 4
/* -- number of inputs -- */
#define SPE_21_max_inputs 1
EXEC SQL BEGIN DECLARE section;
/* ------------------------------------------------------
Set up SQLDA for the input param & return results