ODBC Server Reference Manual

Stored Procedures
HP NonStop ODBC Server Reference Manual—429151-002
5-14
Developing a Stored Procedure in C
The basic steps in developing a stored procedure are described as follows. A more
detailed description of how to create the code for the stored procedure appears later in
this subsection.
1. Create the stored procedure (SELECT_INPUT_DEPT):
a. Provide a routine, if needed, for any initialization that should be performed for
this stored procedure when the Pathway server is first started. For example,
INITIALIZE_SPE_21.
b. Write the code for the body of the stored procedure, such as
SELECT_INPUT_DEPT.
c. Provide a routine, if needed, for any clean-up that should be performed for this
stored procedure when the Pathway server is stopped. For example,
DISPOSE_SPE_21.
d. Generate an “H” file for this stored procedure that includes declarations for the
routines created in the previous steps. See SP21H for an example.
e. Compile the stored procedure and associated functions created in the
preceding steps.
2. Add the stored procedure to the sample program (CSAMPLEC):
a. Include the header file generated for the stored procedure being added.
b. Register the new stored procedure in the server program by calling the function
SPE_REGISTER.
3. Build the new sample program (CSAMPLEC):
a. Compile the sample program (to generate the object CSAMPLEO).
b. Rebind the sample program, CSAMPLE, adding in the object file for the new
stored procedure. See the file BINDFILE for a sample bind file.
c. SQL compile the new sample program. Use the SPCOMP macro from the
SPMACRO file for the compilation.
4. Set up a Pathway configuration for the new server. See the file PATHIN for a
sample configuration. In this example, the server class is named EMP-DATA-DML.
5. Register the stored procedure with the NonStop ODBC server using the ADD
PROCEDURE command. For example:
ADD PROCEDURE tess_data1_db.sql_odbc.select_input_dept
PATHMON_NAME $empsp SERVERCLASS EMP-DATA-DML
NUM_RESULT_SETS 1
MAX_BUFFER_LEN 2000
ADD PROCEDURE_COLUMNS
tess_data1_db.sql_odbc.select_input_dept
p1 SMALLINT