You can code stored procedures to return one or more result sets to DB2 CLI, ODBC, JDBC, or SQLJ client applications. Aspects of this support include:
For additional details on handling result sets:
This sample stored procedure shows how to return a result set to the client application in the following supported languages:
This sample stored procedure accepts one IN parameter and returns one OUT parameter and one result set. The stored procedure uses the IN parameter to create a result set containing the values of the NAME, JOB, and SALARY columns for the STAFF table for rows where SALARY is greater than the IN parameter.
CREATE PROCEDURE RESULT_SET_CLIENT
(IN salValue DOUBLE, OUT sqlCode INTEGER)
DYNAMIC RESULT SETS 1
LANGUAGE C
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!one_result_set_to_client'
SQL_API_RC SQL_API_FN one_result_set_to_client
(double *insalary, sqlint32 *out_sqlerror)
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
l_insalary = *insalary;
*out_sqlerror = 0;
EXEC SQL DECLARE c3 CURSOR FOR (2)
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > :l_insalary
ORDER BY salary;
EXEC SQL OPEN c3; (2)
/* Leave cursor open to return result set */
return (0); (3)
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end one_result_set_to_client function */
public static void resultSetToClient
(double inSalaryThreshold, // double input
int[] errorCode, // SQLCODE output
ResultSet[] rs) // ResultSet output (4)
throws SQLException
{
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try {
// Get caller's connection to the database
Connection con =
DriverManager.getConnection("jdbc:default:connection");
// get salary result set using a parameter marker
String query = "SELECT name, job, CAST(salary AS DOUBLE) " +
"FROM staff " +
"WHERE salary > ? " +
"ORDER BY salary";
// prepare the SQL statement
PreparedStatement stmt = con.prepareStatement(query);
// set the value of the parameter marker (?)
stmt.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
rs[0] = stmt.executeQuery(); (2)
// to return a result set to the client, do not close ResultSet
con.close(); (3)
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
This sample client application shows how to accept a result set from a stored procedure in the following supported languages:
This sample client application calls the RESULT_SET_CLIENT stored procedure and accepts one result set. The client application then displays the contents of the result set.
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlcli1.h>
#include <sqlca.h>
#include "utilcli.h" /* Header file for CLI sample code */
SQLCHAR stmt[50];
SQLINTEGER out_sqlcode;
char out_buffer[33];
SQLINTEGER indicator;
struct sqlca sqlca;
SQLRETURN rc,rc1 ;
char procname[254];
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handle */
SQLHANDLE hstmt1; /* statement handle */
SQLHANDLE hstmt2; /* statement handle */
SQLRETURN sqlrc = SQL_SUCCESS;
double out_median;
int oneresultset1(SQLHANDLE);
int main(int argc, char *argv[])
{
SQLHANDLE hstmt; /* statement handle */
SQLHANDLE hstmt_oneresult; /* statement handle */
char dbAlias[SQL_MAX_DSN_LENGTH + 1] ;
char user[MAX_UID_LENGTH + 1] ;
char pswd[MAX_PWD_LENGTH + 1] ;
/* Declare variables for passing data to INOUT_PARAM */
double inout_median;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd );
if ( rc != 0 ) return( 1 ) ;
/* allocate an environment handle */
printf("\n Allocate an environment handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( sqlrc != SQL_SUCCESS )
{ printf( "\n--ERROR while allocating the environment handle.\n" ) ;
printf( " sqlrc = %d\n", sqlrc);
printf( " line = %d\n", __LINE__);
printf( " file = %s\n", __FILE__);
return( 1 ) ;
}
/* allocate a database connection handle */
printf(" Allocate a database connection handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ;
/* connect to the database */
printf( " Connecting to the database %s ...\n", dbAlias ) ;
sqlrc = SQLConnect( hdbc,
(SQLCHAR *)dbAlias, SQL_NTS,
(SQLCHAR *)user, SQL_NTS,
(SQLCHAR *)pswd, SQL_NTS
) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
printf( " Connected to the database %s.\n", dbAlias ) ;
/* set AUTOCOMMIT off */
sqlrc = SQLSetConnectAttr( hdbc,
SQL_ATTR_AUTOCOMMIT,
SQL_AUTOCOMMIT_OFF, SQL_NTS) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/* allocate one or more statement handles */
printf(" Allocate a statement handle.\n");
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt_oneresult ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ;
/********************************************************\
* Call oneresultsettocaller stored procedure *
\********************************************************/
rc = oneresultset1(hstmt_oneresult);
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt_oneresult ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
/* ROLLBACK, free resources, and exit */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
printf("\nStored procedure rolled back.\n\n");
/* Disconnect from Remote Database */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ;
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( SQL_ERROR ) ;
return( SQL_SUCCESS ) ;
}
int oneresultset1(hstmt)
SQLHANDLE hstmt; /* statement handle */
{
/********************************************************\
* Call one_result_set_to_client stored procedure *
\********************************************************/
double insalary = 20000;
SQLINTEGER salary_int;
SQLSMALLINT num_cols;
char name[40];
char job[10];
strcpy(procname, "RESULT_SET_CALLER"); (1)
printf("\nCALL stored procedure: %s\n", procname);
strcpy((char*)stmt,"CALL RESULT_SET_CALLER ( ?,? )");
rc = SQLPrepare(hstmt, stmt, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, rc);
/* Bind the parameter to application variables () */
rc = SQLBindParameter(hstmt, 1,
SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DOUBLE,0,
0, &insalary,
0, NULL);
rc = SQLBindParameter(hstmt, 2,
SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER,0,
0, &out_sqlcode,
0, NULL);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLExecute(hstmt);
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLNumResultCols( hstmt, &num_cols ) ;
STMT_HANDLE_CHECK( hstmt, rc);
printf("Result set returned %d columns\n", num_cols);
/* bind columns to variables */
rc = SQLBindCol( hstmt, 1, SQL_C_CHAR, name, 40, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindCol( hstmt, 2, SQL_C_CHAR, job, 10, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
rc = SQLBindCol( hstmt, 3, SQL_C_LONG, &salary_int, 0, &indicator);
STMT_HANDLE_CHECK( hstmt, rc);
/* fetch result set returned from stored procedure */
rc = SQLFetch( hstmt ); (2)
rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
STMT_HANDLE_CHECK( hstmt, rc);
printf("\n--------Name---------, --JOB--, ---Salary-- \n");
while (rc == SQL_SUCCESS && rc != SQL_NO_DATA_FOUND ) (3)
{
printf("%20s,%10s, %d\n",name,job,salary_int);
rc = SQLFetch( hstmt );
}
STMT_HANDLE_CHECK( hstmt, rc);
/* Check that the stored procedure executed successfully */
if (rc == SQL_SUCCESS) {
printf("Stored procedure returned successfully.\n");
}
else {
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
}
rc = SQLCloseCursor(hstmt);
return(rc);
}
// prepare the CALL statement for RESULT_SET_CLIENT
procName = "RESULT_SET_CLIENT";
sql = "CALL " + procName + "(?, ?)"; (1)
callStmt = con.prepareCall(sql);
// set input parameter to median value passed back by OUT_PARAM
callStmt.setDouble (1, outMedian);
// register the output parameter
callStmt.registerOutParameter (2, Types.INTEGER);
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
callStmt.execute();
// retrieve output parameter
outErrorCode = callStmt.getInt(2);
if (outErrorCode == 0) {
System.out.println(procName + " completed successfully");
ResultSet rs = callStmt.getResultSet(); (2)
while (rs.next()) {
fetchAll(rs); (3)
}
// close ResultSet
rs.close();
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE "
+ outErrorCode);
}
If a stored procedure application fails to execute properly, ensure that:
For example, in an OS/2 environment, the dynamic link library for a FENCED stored procedure is located in the instance_name\function directory on the database server.
For example, the database manager will execute the stored procedure myfunc contained in the Windows 32-bit operating system library myfunc.dll as a DB2DARI function, disregarding the values specified in its associated CREATE PROCEDURE statement.
| Note: | For more information on debugging Java stored procedures, see Debugging Stored Procedures in Java. |
You can use the debugger supplied with your compiler to debug a local FENCED stored procedure as you would any other application. Consult your compiler documentation for information on using the supplied debugger.
For example, to use the debugger supplied with Visual Studio(TM) on Windows NT, perform the following steps:
Step 1. | Set the DB2_STPROC_ALLOW_LOCAL_FENCED registry variable to true. |
Step 2. | Compile the source file for the stored procedure DLL with the -Zi and -Od flags, and then link the DLL using the -DEBUG option. |
Step 3. | Copy the resulting DLL to the instance_name \function directory of the server. |
Step 4. | Invoke the client application on the server with the Visual Studio debugger. For the client application outcli.exe, enter the following command: msdev spclient.exe |
Step 5. | When the Visual Studio debugger window opens, select Project --> Settings. |
Step 6. | Click the Debug tab. |
Step 7. | Click the Category arrow and select the Additional DLLs. |
Step 8. | Click the New button to create a new module. |
Step 9. | Click the Browse button to open the Browse window. |
Step 10. | Select the module spserver.dll and click OK to close the Settings window. |
Step 11. | Open the source file for the stored procedure and set a breakpoint. |
Step 12. | Click the Go button. The Visual Studio debugger stops when the stored procedure is invoked. |
Step 13. | At this point, you can debug the stored procedure using the Visual Studio debugger.
|
Refer to the Visual Studio product documentation for further information on using the Visual Studio debugger.