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).
/* From CLI sample inpsrv2.c */
/* ... */
/********************************************************************
*
* 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 ) ;
}
/* From CLI sample inpcli2.c */
/* ... */
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 ) ;