An application can be designed to run in two parts, one on the client and the other on the server. The stored procedure is the part that runs at the database within the same transaction as the application. Stored procedures can be written in either embedded SQL or using the DB2 CLI functions (see Writing a Stored Procedure in CLI). In general, stored procedures have the following advantages:
In addition, stored procedures written in embedded static SQL have the following advantages:
Stored procedures are invoked from a DB2 CLI application by passing the following CALL statement syntax to SQLExecDirect() or to SQLPrepare() followed by SQLExecute().
.-,--------.
V |
>>-CALL---procedure-name--(-----+----+--+---)------------------><
'-?--'
| Note: |
Although the CALL statement cannot be prepared dynamically, DB2 CLI accepts the CALL statement as if it could be dynamically prepared. Stored procedures can also be called using the ODBC vendor escape sequence shown in Stored Procedure Call Syntax. |
The name (with no extension) of the procedure to execute. The procedure invoked is determined as follows.
Once the procedure is selected, DB2 will invoke the procedure defined by the external name.
The use of the exclamation sign allows the specification of a library name identified by procedure-name and the function to be executed is given by func-name. This allows similar function routines to be placed in the same stored procedure library.
The name of the stored procedure library is specified as a full path name. The function to be executed is given by func-name.
For more information regarding the use of the CALL statement and stored procedures, refer to the SQL Reference and the Application Development Guide.
If the server is DB2 Universal Database Version 2.1 or later, or DB2 for MVS/ESA V4.1 or later, SQLProcedures() can be called to obtain a list of stored procedures available at the database.
| Note: | For DB2 Universal Database, SQLProcedures() may not return all procedures, and applications can use any valid procedure, regardless of whether it is returned by SQLProcedures(). For more information, refer to Registering Stored Procedures and SQLProcedures - Get List of Procedure Names. |
The ? in the CALL statement syntax diagram denote parameter markers corresponding to the arguments for a stored procedure. All arguments must be passed using parameter markers; literals, the NULL keyword, and special registers are not allowed. However, literals can be used if the vendor escape call statement is used, ie. the call statement is surrounded by curly braces '{...}'.
The parameter markers in the CALL statement are bound to application variables using SQLBindParameter(). Although stored procedure arguments can be used both for input and output, in order to avoid sending unnecessary data between the client and the server, the application should specify on SQLBindParameter() the parameter type of an input argument to be SQL_PARAM_INPUT and the parameter type of an output argument to be SQL_PARAM_OUTPUT. Those arguments that are both input and output have a parameter type of SQL_PARAM_INPUT_OUTPUT.
If the server is DB2 Universal Database Version 2.1 or later, or DB2 for MVS/ESA V4.1 or later, an application can call SQLProcedureColumns() to determine the type of a parameter in a procedure call. For more information, refer to Registering Stored Procedures below and SQLProcedureColumns - Get Input/Output Parameter Information for A Procedure.
For DB2 Universal Database, the stored procedure must be registered on the server (in SYSCAT.PROCEDURES and SYSCAT.PROCPARMS) before SQLProcedures() and SQLProcedureColumns() can be invoked; otherwise, these two catalog function calls will return empty result sets. For information on registering stored procedures on the server, see Appendix H, Pseudo Catalog Table for Stored Procedure Registration.
If the stored procedure resides on a DB2 for MVS/ESA V4.1 or later server, the name of the stored procedure must be defined in the SYSIBM.SYSPROCEDURES catalog table. The pseudo catalog table used by DB2 Universal Database is a derivation and extension of the DB2 for MVS/ESA SYSIBM.SYSPROCEDURES catalog table).
If the stored procedure resides on a DB2 Universal Database for AS/400 V3.1 server, the application must know the actual path and name of the stored procedure ahead of time as there is no real or pseudo catalog table to retrieve information on stored procedures or their argument list.
Although stored procedures are, in most ways, like any other application, stored procedures written in CLI (and embedded SQL) must give special consideration to the SQLDA structure which contains the stored procedure arguments. The SQLDA structure is described in detail in the SQL Reference.
It is important to understand that all data stored in the SQLDA structure is stored as an SQL data type, and must be treated as such by the stored procedure. For example,
The suggested approach is for the stored procedure to interpret the SQLDA and move all input arguments to host language variables on entry, and from host language variables to the SQLDA on exit. This allows for SQLDA specific code to be localized within the stored procedure.
DB2 CLI provides the ability to retrieve one or more result sets from a stored procedure call, provided the stored procedure has been coded such that one or more cursors, each associated with a query, has been opened and left opened when the stored procedure exits. If more than one cursor is left open, multiple result sets are returned.
DB2 CLI applications can retrieve result sets after the execution of a stored procedure that has left cursor(s) open by doing the following:
DB2 Universal Database stored procedures must satisfy the following requirements to return one or more result sets to a CLI application:
In general, calling a stored procedure that returns a result set is equivalent to executing a query statement. The following restrictions apply:
Although embedded SQL stored procedures provide the most advantages, application developers who have existing DB2 CLI applications may wish to move components of the application to run on the server. In order to minimize the required changes to the code and logic of the application, these components can be implemented as stored procedures, written using DB2 CLI.
Since all the internal information related to a DB2 CLI connection is referenced by the connection handle and since a stored procedure runs under the same connection and transaction as the client application, it is necessary that a stored procedure written using DB2 CLI make a null SQLConnect() call to associate a connection handle with the underlying connection of the client application. A null SQLConnect() is where the ServerName, UserName, and Authentication argument pointers are all set to NULL and their respective length arguments all set to 0. Of course, in order that an SQLConnect() call can be made at all, the environment and connection handles must already be allocated.
| Note: | Stored procedures written using Embedded SQL must be precompiled with the DATETIME ISO option in order for DB2 CLI to deal with date-time values correctly. |
The following shows a stored procedure and an example that calls it. (The following example is an input example, see the outcli2.c, outsrv2.c samples for an output example.)
DB2 also includes a number of example programs that demonstrate stored procedures that return multi-row result sets (see the set of example programs that begin with mrsp: mrspcli.c, mrspcli2.c, mrspcli3.sqc, clicall.c, mrspsrv.c and mrspsrv2.sqc).
/* ... */
/********************************************************************
*
* PURPOSE: This sample program demonstrates stored procedures,
* using CLI. It is rewrite of the inpsrv.sqc embedded SQL
* stored procedure.
*
* There are two parts to this program:
* - the inpcli2 executable (placed on the client)
* - the inpsrv2 library (placed on the server)
* CLI stored procedures can be called by either CLI or embbeded
* applications.
*
* The inpsrv function will take the information
* received in the SQLDA to create a table and insert the
* names of the presidents.
*
* Refer to the inpcli2.c program for more details on how
* this program is invoked as the inpsrv2 function
* in the inpsrv2 library by the EXEC SQL CALL statement.
*
* The SQL CALL statement will pass in 2 identical SQLDA
* structures for input and output because all parameters
* on the CALL statement are assummed to have both the
* input and output attributes. However, only changes
* make to the data and indicator fields in the output SQLDA
* will be returned to the client program.
*
* NOTE: One technique to minimize network flow is to set the
* variables that returns no output to null on the server program
* before returning to the client program.
* This can be achieved by setting the value -128 to the
* indicator value associated with the data.
*
* The sqleproc API will call the inpsrv routine stored
* in the inpsrv library.
*
* The inpsrv routine will take the information received
* and create a table called "Presidents" in the "sample"
* database. It will then place the values it received in
* the input SQLDA into the "Presidents" table.
*
********************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcli1.h>
#include "samputil.h" /* Header file for CLI sample code */
int SQL_API_FN inpsrv2( struct sqlchar * input_data,
struct sqlda * input_sqlda,
struct sqlda * output_sqlda,
struct sqlca * ca
) {
/* Declare a local SQLCA */
struct sqlca sqlca ;
SQLCHAR table_stmt[80] = "CREATE TABLE " ;
SQLCHAR insert_stmt[80] = "INSERT INTO " ;
SQLCHAR insert_data[21] ;
SQLINTEGER insert_data_ind ;
/* Delare Miscellanous Variables */
int cntr ;
char * table_name ;
short table_name_length ;
char * data_item[3] ;
short data_item_length[3] ;
int num_of_data = 0 ;
/* Delare CLI Variables */
SQLHANDLE henv, hdbc, hstmt ;
SQLRETURN rc ;
/*-----------------------------------------------------------------*/
/* Assign the data from the SQLDA to local variables so that we */
/* don't have to refer to the SQLDA structure further. This will */
/* provide better portability to other platforms such as DB2 MVS */
/* where they receive the parameter list differently. */
/* Note: Strings are not null-terminated in the SQLDA. */
/*-----------------------------------------------------------------*/
table_name = input_sqlda->sqlvar[0].sqldata ;
table_name_length = input_sqlda->sqlvar[0].sqllen ;
num_of_data = input_sqlda->sqld - 1 ;
for ( cntr = 0; cntr < num_of_data; cntr++ ) {
data_item[cntr] = input_sqlda->sqlvar[cntr+1].sqldata ;
data_item_length[cntr] = input_sqlda->sqlvar[cntr+1].sqllen ;
}
/*-----------------------------------------------------------------*/
/* Setup CLI required environment */
/*-----------------------------------------------------------------*/
SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ;
/*-----------------------------------------------------------------*/
/* Issue NULL Connect, since in CLI we need a statement handle */
/* and thus a connection handle and environment handle. */
/* A connection is not established, rather the current */
/* connection from the calling application is used */
/*-----------------------------------------------------------------*/
SQLConnect( hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS ) ;
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
/*-----------------------------------------------------------------*/
/* Create President Table */
/* - For simplicity, we'll ignore any errors from the */
/* CREATE TABLE so that you can run this program even when the */
/* table already exists due to a previous run. */
/*-----------------------------------------------------------------*/
strncat( ( char * ) table_stmt,
( char * ) table_name,
table_name_length
) ;
strcat( ( char * ) table_stmt, " (name CHAR(20))" ) ;
SQLExecDirect( hstmt, table_stmt, SQL_NTS ) ;
SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ;
/*-----------------------------------------------------------------*/
/* Generate and execute a PREPARE for an INSERT statement, and */
/* then insert the three presidents. */
/*-----------------------------------------------------------------*/
strncat( ( char * ) insert_stmt,
( char * ) table_name,
table_name_length
) ;
strcat( ( char * ) insert_stmt, " VALUES (?)" ) ;
if ( SQLPrepare(hstmt, insert_stmt, SQL_NTS) != SQL_SUCCESS ) goto ext ;
/* Bind insert_data to parameter marker */
SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
20,
0,
insert_data,
21,
&insert_data_ind
) ;
for ( cntr = 0; cntr < num_of_data; cntr++ ) {
strncpy( ( char * ) insert_data,
( char * ) data_item[cntr],
data_item_length[cntr]) ;
insert_data_ind = data_item_length[cntr] ;
if ( SQLExecute( hstmt ) != SQL_SUCCESS ) goto ext ;
}
/*-----------------------------------------------------------------*/
/* Return to caller */
/* - Copy the SQLCA */
/* - Update the output SQLDA. Since there's no output to */
/* return, we are setting the indicator values to -128 to */
/* return only a null value. */
/* - Commit or Rollback the inserts. */
/*-----------------------------------------------------------------*/
ext:
rc = SQLGetSQLCA( henv, hdbc, hstmt, &sqlca ) ;
if ( rc != SQL_SUCCESS ) printf( "RC = %d\n", rc ) ;
memcpy( ca, &sqlca, sizeof( sqlca ) ) ;
if ( output_sqlda != NULL ) {
for ( cntr = 0; cntr < output_sqlda->sqld; cntr++ ) {
if ( output_sqlda->sqlvar[cntr].sqlind != NULL )
*( output_sqlda->sqlvar[cntr].sqlind ) = -128 ;
}
}
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
printf( ">Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS )
return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
}
/* ... */
SQLCHAR * stmt = "CALL inpsrv2(?, ?, ?, ?)" ;
/* ... */
rc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
9,
0,
Tab_Name,
10,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
2,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
10,
0,
Pres_Name[0],
11,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
3,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
10,
0,
Pres_Name[1],
11,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
4,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
10,
0,
Pres_Name[2],
11,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLExecute( hstmt ) ;
/* Ignore Warnings */
if ( rc != SQL_SUCCESS_WITH_INFO )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;