An application design that includes a stored procedure consists of separate client and server applications. The server application, called the stored procedure, is contained in a shared library or class library on the server. You must compile and access the stored procedure on the server instance where the database resides. The client application contains a CALL statement to the stored procedure. The CALL statement can pass parameters to and return parameters from the stored procedure. You can write the stored procedure and the client application using different languages. The client application can be executed on on a platform different from the stored procedure.
The client application performs the following tasks:
| Note: | While the stored procedure can issue COMMIT or ROLLBACK statements, the recommended practice is to have the client application issue to issue the COMMIT or ROLLBACK. This enables your client application to evaluate the data returned by the stored procedure and to decide whether to commit the transaction or roll it back. |
Note that you can code SQL statements in any of the above steps.
When invoked, the stored procedure performs the following tasks:
| Note: | While the stored procedure can issue COMMIT or ROLLBACK statements, the recommended practice is to have the client application issue the COMMIT or ROLLBACK statements. This enables your client application to evaluate the data returned by the stored procedure and to decide whether to commit the transaction or roll it back. |
The stored procedure executes when called by the client application. Control is returned to the client when the server procedure finishes processing. You can put several stored procedures into one library.
This chapter describes how to write stored procedures with the following parameter styles:
You must register each stored procedure for the previously listed parameter styles with a CREATE PROCEDURE statement. The CREATE PROCEDURE statement specifies the procedure name, arguments, location, and parameter style of each stored procedure. These parameter styles offer increased portability and scalability of your stored procedure code across the DB2 family.
For information on using the only styles of stored procedures supported by versions of DB2 prior to DB2 Universal Database Version 6, that is, the DB2DARI and DB2GENERAL parameter styles, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
The client application performs several steps before calling the stored procedure. It must be connected to a database, and it must declare, allocate, and initialize host variables or an SQLDA structure. The SQL CALL statement can accept a series of host variables, or an SQLDA structure. Refer to the SQL Reference for descriptions of the SQL CALL statement and the SQLDA structure. For information on using the SQLDA structure in a client application, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
Use the following steps to allocate the necessary input host variables on the client side of a stored procedure:
When writing the client portion of your stored procedure, you should attempt to overload as many of the host variables as possible by using them for both input and output. This will increase the efficiency of handling multiple host variables. For example, when returning an SQLCODE to the client from the stored procedure, try to use an input host variable that is declared as an INTEGER to return the SQLCODE.
| Note: | Do not allocate storage for these structures on the database server. The database manager automatically allocates duplicate storage based upon the storage allocated by the client application. Do not alter any storage pointers for the input/output parameters on the stored procedure side. Attempting to replace a pointer with a locally created storage pointer will cause an error with SQLCODE -1133 (SQLSTATE 39502). |
You can invoke a stored procedure stored at the location of the database by using the SQL CALL statement. Refer to the SQL Reference for a complete description of the CALL statement. Using the CALL statement is the recommended method of invoking stored procedures.
The client application must ensure that a database connection has been made before invoking the stored procedure, or an error is returned. After the database connection and data structure initialization, the client application calls the stored procedure and passes any required data. The application disconnects from the database. Note that you can code SQL statements in any of the above steps.
The stored procedure is invoked by the SQL CALL statement and executes using data passed to it by the client application. The parameter style with which you register the stored procedure in the database manager with the CREATE PROCEDURE statement determines how the stored procedure receives data from the client application.
To use the CREATE PROCEDURE statement, you must declare the following:
Your CREATE PROCEDURE should also declare the following:
You can find more information on the CREATE PROCEDURE statement, including its full syntax and options for DB2 family compatibility, in the SQL Reference. Descriptions of typical usages of the CREATE PROCEDURE statement follow.
You can overload stored procedures only by using the same name for procedures that accept a unique number of parameters. Since DB2 does not distinguish between data types, you cannot overload stored procedures based on parameter data types.
For example, issuing the following CREATE PROCEDURE statements will work because they accept one and two parameters, respectively:
CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER) ...
CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER, IN VAR2 INTEGER) ...
However, DB2 will fail to register the second stored procedure in the following example because it has the same number of parameters as the first stored procedure with the same name:
CREATE PROCEDURE OVERLOADFAIL (IN VAR1 INTEGER) ...
CREATE PROCEDURE OVERLOADFAIL (IN VAR2 VARCHAR(15)) ...
An explicit parameter is a parameter that you explicitly declare in the parameter list of the CREATE PROCEDURE statement. An implicit parameter is a parameter that is automatically supplied by DB2; for example, a PARAMETER STYLE GENERAL WITH NULLS stored procedure automatically supplies an array of null indicators for the explicit parameters. When you write a stored procedure, you must consider both the explicit and implicit parameters for your stored procedure. When you write a client application, you only have to handle the explicit parameters for the stored procedure. You must declare every explicit parameter as either an IN, OUT, or INOUT parameter with a name and SQL data type. For examples of CREATE PROCEDURE statements, see Variable Declaration and CREATE PROCEDURE Examples.
The EXTERNAL clause of the CREATE PROCEDURE statement tells the database manager the location of the library that contains the stored procedure. If you do not specify an absolute path for the library, or a jar name for Java stored procedures, the database manager searches the function directory. The function directory is a directory defined for your operating system as follows:
If DB2 does not find the stored procedure in instance_name\function, DB2 searches the directories defined by the PATH and LIBPATH environment variables.
For example, the function directory for a Windows 32-bit operating system server with DB2 installed in the C:\sqllib directory, where you have not set the DB2INSTPROF registry setting, is:
C:\sqllib\function
| Note: | You should give your library a name that is different than the stored procedure name. If DB2 locates the library in the search path, DB2 executes any stored procedure with the same name as the library which contains the stored procedure as a FENCED DB2DARI procedure. |
For LANGUAGE C stored procedures, specify:
On a UNIX-based system, for example, mymod!proc8 directs the database manager to the sqllib/function/mymod library and to use entry point proc8 within that library. On Windows 32-bit and OS/2 operating systems mymod!proc8 directs the database manager to load the mymod.dll file from the function directory and call the proc8() procedure in the dynamic link library (DLL).
For LANGUAGE JAVA stored procedures, use the following syntax:
[<jar-file-name>:]<class-name>.<method-name>
The following list defines the EXTERNAL keywords for Java stored procedures:
For example, if you specify MyPackage.MyClass.myMethod, the database manager uses the myMethod method in the MyClass class, within the MyPackage package. DB2 recognizes that MyPackage refers to a package rather than a jar file because it uses a period (.) delimiter instead of a colon (:) delimiter. DB2 searches the function directory for the MyPackage package.
For more information on the function directory, see Location.
For C/C++, declare LANGUAGE C in your CREATE PROCEDURE statement. For Java stored procedures, declare LANGUAGE JAVA. For OLE stored procedures on Windows 32-bit operating systems, declare LANGUAGE OLE. For COBOL stored procedures, declare LANGUAGE COBOL. For Fortran or REXX stored procedures, you must write the stored procedure as a DB2DARI stored procedure. For more information on writing DB2DARI stored procedures, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
C stored procedures of PROGRAM TYPE SUB accept arguments as subroutines. Pass numeric data type parameters as pointers. Pass character data types as arrays of the appropriate length. For example, the following C stored procedure signature accepts parameters of type INTEGER, SMALLINT, and CHAR(3):
int storproc (sqlint32 *arg1, short *arg2, char arg[4])
Java stored procedures can only accept arguments as subroutines. Pass IN parameters as simple arguments. Pass OUT and INOUT parameters as arrays with a single element. For example, the following Java stored procedure signature accepts an IN parameter of type INTEGER, an OUT parameter of type SMALLINT, and an INOUT parameter of type CHAR(3):
int storproc (int arg1, short arg2[], String arg[])
To write a stored procedure that accepts arguments like a main function in a C program, specify PROGRAM TYPE MAIN in the CREATE PROCEDURE statement. You must write stored procedures of PROGRAM TYPE MAIN to conform to the following specifications:
In PROGRAM TYPE MAIN stored procedures, DB2 sets the value of the first element in the argv array, (argv[0]), to the name of the stored procedure. The remaining elements of the argv array correspond to the parameters declared in the CREATE PROCEDURE statement for the stored procedure. For example, the following embedded C stored procedure passes in one IN parameter as argv[1] and returns two OUT parameters as argv[2] and argv[3].
The CREATE PROCEDURE statement for the PROGRAM TYPE MAIN example is as follows:
CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8),
OUT salary DOUBLE, OUT errorcode INTEGER)
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE MAIN
EXTERNAL NAME 'spserver!mainexample'
The following code for the stored procedure copies the value of argv[1] into the CHAR(8) host variable injob, then copies the value of the DOUBLE host variable outsalary into argv[2] and returns the SQLCODE as argv[3]:
EXEC SQL BEGIN DECLARE SECTION;
char injob[9];
double outsalary;
EXEC SQL END DECLARE SECTION;
SQL_API_RC SQL_API_FN main_example (int argc, char **argv)
{
EXEC SQL INCLUDE SQLCA;
/* argv[0] contains the procedure name, so parameters start at argv[1] */
strcpy (injob, (char *)argv[1]);
EXEC SQL SELECT AVG(salary)
INTO :outsalary
FROM employee
WHERE job = :injob;
memcpy ((double *)argv[2], (double *)&outsalary, sizeof(double));
memcpy ((sqlint32 *)argv[3], (sqlint32 *)&SQLCODE, sizeof(sqlint32));
return (0);
} /* end main_example function */
Table 9 summarizes the combinations of PARAMETER STYLE (horizontal
axis) and LANGUAGE (vertical axis) allowed in CREATE PROCEDURE statements for
DB2 Version 7.
Table 9. CREATE PROCEDURE: Valid Combinations of PARAMETER STYLE and LANGUAGE
| GENERAL, GENERAL WITH NULLS | JAVA | DB2SQL | DB2DARI | DB2GENERAL | |
|---|---|---|---|---|---|
| LANGUAGE C | Y | N | Y | Y | N |
| LANGUAGE JAVA | N | Y | N | N | Y |
| LANGUAGE OLE | N | N | Y | N | N |
| LANGUAGE COBOL | Y | N | Y | N | N |
DB2 Universal Database for OS/390 compatibility note: GENERAL is the equivalent of SIMPLE.
PARAMETER STYLE GENERAL stored procedures accept parameters in the manner indicated by the value of the PROGRAM TYPE clause. The following example demonstrates a PARAMETER STYLE GENERAL stored procedure that accepts two parameters using PROGRAM TYPE SUBROUTINE:
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;
EXEC SQL BEGIN DECLARE SECTION;
double l_insalary;
EXEC SQL END DECLARE SECTION;
l_insalary = *insalary;
*out_sqlerror = 0;
EXEC SQL DECLARE c3 CURSOR FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > :l_insalary
ORDER BY salary;
EXEC SQL OPEN c3;
/* Leave cursor open to return result set */
return (0);
/* 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 */
DB2 Universal Database for OS/390 compatibility note: GENERAL WITH NULLS is the equivalent of SIMPLE WITH NULLS.
PARAMETER STYLE GENERAL WITH NULLS stored procedures accept parameters in the manner indicated by the value of the PROGRAM TYPE clause, and allocate an array of null indicators with one element per declared parameter. The following SQL registers a PARAMETER STYLE GENERAL WITH NULLS stored procedure that passes one INOUT parameter and two OUT parameters using PROGRAM TYPE SUB:
CREATE PROCEDURE INOUT_PARAM (INOUT medianSalary DOUBLE,
OUT errorCode INTEGER, OUT errorLabel CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!inout_param'
The following C code demonstrates how to declare and use the null indicators required by a GENERAL WITH NULLS stored procedure:
SQL_API_RC SQL_API_FN inout_param (double *inoutMedian,
sqlint32 *out_sqlerror, char buffer[33], sqlint16 nullinds[3])
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
if (nullinds[0] < 0)
{
/* NULL value was received as input, so return NULL output */
nullinds[0] = -1;
nullinds[1] = -1;
nullinds[2] = -1;
}
else
{
int counter = 0;
*out_sqlerror = 0;
medianSalary = *inoutMedian;
strcpy(buffer, "DECLARE inout CURSOR");
EXEC SQL DECLARE inout CURSOR FOR
SELECT CAST(salary AS DOUBLE) FROM staff
WHERE salary > :medianSalary
ORDER BY salary;
nullinds[1] = 0;
nullinds[2] = 0;
strcpy(buffer, "SELECT COUNT INTO numRecords");
EXEC SQL SELECT COUNT(*) INTO :numRecords
FROM staff
WHERE salary > :medianSalary;
if (numRecords != 0)
/* At least one record was found */
{
strcpy(buffer, "OPEN inout");
EXEC SQL OPEN inout USING :medianSalary;
strcpy(buffer, "FETCH inout");
while (counter < (numRecords / 2 + 1)) {
EXEC SQL FETCH inout INTO :medianSalary;
*inoutMedian = medianSalary;
counter = counter + 1;
}
strcpy(buffer, "CLOSE inout");
EXEC SQL CLOSE inout;
}
else /* No records were found */
{
/* Return 100 to indicate NOT FOUND error */
*out_sqlerror = 100;
}
}
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end inout_param function */
sqlint16 nullinds[n], (1)
char sqlst[6], (2)
char qualname[28], (3)
char specname[19], (4)
char diagmsg[71], (5)
DB2 passes the following arguments to the stored procedure:
You can only specify DB2SQL when you also specify the LANGUAGE C or LANGUAGE COBOL option. For example, the following CREATE PROCEDURE statement registers a PARAMETER STYLE DB2SQL stored procedure:
CREATE PROCEDURE DB2SQL_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE)
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE DB2SQL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!db2sqlexample'
Write the stored procedure using the following conventions:
For example, the following embedded C stored procedure demonstrates the coding style for PARAMETER STYLE DB2SQL stored procedures:
SQL_API_RC SQL_API_FN db2sql_example (
char injob[9], /* Input - CHAR(8) */
double *salary, /* Output - DOUBLE */
sqlint16 nullinds[2],
char sqlst[6],
char qualname[28],
char specname[19],
char diagmsg[71]
)
{
EXEC SQL INCLUDE SQLCA;
if (nullinds[0] < 0)
{
/* NULL value was received as input, so return NULL output */
nullinds[1] = -1;
/* Set custom SQLSTATE to return to client. */
strcpy(sqlst, "38100");
/* Set custom message to return to client. */
strcpy(diagmsg, "Received null input on call to DB2SQL_EXAMPLE.");
}
else
{
EXEC SQL SELECT (CAST(AVG(salary) AS DOUBLE))
INTO :outsalary INDICATOR :outsalaryind
FROM employee
WHERE job = :injob;
*salary = outsalary;
nullinds[1] = outsalaryind;
}
return (0);
} /* end db2sql_example function */
The following embedded C client application demonstrates how to issue a CALL statement that invokes the DB2SQL_EXAMPLE stored procedure. Note that the example includes null indicators for each parameter in the CALL statement. The example sets the null indicator in_jobind to 0 to indicate that a non-NULL value is being passed to the stored procedure for the IN parameter represented by the host variable in_job. The null indicators for the OUT parameters are set to -1 to indicate that no input is being passed to the stored procedure for those parameters.
int db2sqlparm(char out_lang[9], char job_name[9])
{
int testlang;
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to DB2SQL_EXAMPLE */
char in_job[9];
sqlint16 in_jobind;
double out_salary = 0;
sqlint16 out_salaryind;
EXEC SQL END DECLARE SECTION;
/********************************************************\
* Call DB2SQL_EXAMPLE stored procedure *
\********************************************************/
testlang = strncmp(out_lang, "C", 1);
if (testlang != 0) {
/* Only LANGUAGE C procedures can be PARAMETER STYLE DB2SQL,
so do not call the DB2SQL_EXAMPLE stored procedure */
printf("\nStored procedures are not implemented in C.\n"
"Skipping the call to DB2SQL_EXAMPLE.\n");
}
else {
strcpy(procname, "DB2SQL_EXAMPLE");
printf("\nCALL stored procedure named %s\n", procname);
/* out_salary is an OUT parameter, so set the
null indicator to -1 to indicate no input value */
out_salaryind = -1;
strcpy(in_job, job_name);
/* in_job is an IN parameter, so check to
see if there is any input value */
if (strlen(in_job) == 0)
{
/* in_job is null, so set the null indicator
to -1 to indicate there is no input value */
in_jobind = -1;
printf("with NULL input, to return a custom
SQLSTATE and diagnostic message\n");
}
else
{
/* in_job is not null, so set the null indicator
to 0 to indicate there is an input value */
in_jobind = 0;
}
/* DB2SQL_EXAMPLE is PS DB2SQL, so pass
a null indicator for each parameter */
EXEC SQL CALL :procname (:in_job:in_jobind,
:out_salary:out_salaryind);
/* DB2SQL stored procedures can return a custom
SQLSTATE and diagnostic message, so instead of
using the EMB_SQL_CHECK macro to check the value
of the returned SQLCODE, check the SQLCA structure for
the value of the SQLSTATE and the diagnostic message */
/* Check value of returned SQLSTATE */
if (strncmp(sqlca.sqlstate, "00000", 5) == 0) {
printf("Stored procedure returned successfully.\n");
printf("Average salary for job %s = %9.2f\n",
in_job, out_salary);
}
else {
printf("Stored procedure failed with SQLSTATE %s.\n",
sqlca.sqlstate);
printf("Stored procedure returned the following
diagnostic message:\n");
printf(" \"%s\"\n", sqlca.sqlerrmc);
}
}
return 0;
}
For increased portability, you should write Java stored procedures using the PARAMETER STYLE JAVA conventions. See Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs for more information on writing DB2GENERAL parameter style stored procedures.
To increase portability across the DB2 family, you should write your LANGUAGE C stored procedures using the GENERAL or GENERAL WITH NULLS parameter styles. If you want to write DB2DARI parameter style stored procedures, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.
For LANGUAGE C stored procedures with a PARAMETER TYPE of GENERAL, GENERAL WITH NULLS, or DB2SQL, you have the option of writing your stored procedure to accept an additional parameter. You can specify DBINFO in the CREATE PROCEDURE statement to instruct the client application to pass a DBINFO structure containing information about the DB2 client to the stored procedure, along with the call parameters. The DBINFO structure contains the following values:
For more information on the DBINFO structure, see DBINFO Structure.
The following examples demonstrate the stored procedure source code and CREATE PROCEDURE statements you would use in hypothetical scenarios with the SAMPLE database.
Assume that you want to create a Java stored procedure GET_LASTNAME that, given empno (SQL type VARCHAR), returns lastname (SQL type CHAR) from the EMPLOYEE table in the SAMPLE database. You will create the procedure as the getname method of the Java class StoredProcedure, contained in the JAR installed as myJar. Finally, you will call the stored procedure with a client application coded in C.
String empid;
String name;
...
#sql { SELECT lastname INTO :empid FROM employee WHERE empno=:empid }
CREATE PROCEDURE GET_LASTNAME (IN EMPID CHAR(6), OUT NAME VARCHAR(15))
EXTERNAL NAME 'myJar:StoredProcedure.getname'
LANGUAGE JAVA PARAMETER STYLE JAVA FENCED
READS SQL DATA
EXEC SQL BEGIN DECLARE SECTION;
struct name { short int; char[15] }
char[7] empid;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL CALL GET_LASTNAME (:empid, :name);
For the following example, assume that you want to create a C stored procedure GET_MANAGER that, given deptnumb (SQL type SMALLINT), returns manager (SQL type SMALLINT) from the ORG table in the SAMPLE database.
EXEC SQL BEGIN DECLARE SECTION;
short onevar = 0;
EXEC SQL END DECLARE SECTION;
CREATE PROCEDURE GET_MANAGER (INOUT onevar SMALLINT)
EXTERNAL NAME 'stplib!getman'
LANGUAGE C PARAMETER STYLE GENERAL FENCED
READS SQL DATA
short onevar = 0;
...
#SQL { CALL GET_MANAGER (:INOUT onevar) };
Stored procedures can contain SQL statements. When you issue the CREATE PROCEDURE statement, you should specify the type of SQL statements the stored procedure contains, if any. If you do not specify a value when you register the stored procedure, the database manager uses MODIFIES SQL DATA. To restrict the type of SQL used in the stored procedure, you can use one of the following four options:
For more information on the CREATE PROCEDURE statement, refer to the SQL Reference.
Nested stored procedures are stored procedures that call another stored procedure. You can use this technique in your DB2 applications under the following restrictions:
Nested SQL procedures can return one or more result sets to the client application or to the calling stored procedure. To return a result set from an SQL procedure to the client application, issue the DECLARE CURSOR statement using the WITH RETURN TO CLIENT clause. To return a result set from an SQL procedure to the caller, where the caller is either a client application or a calling stored procedure, issue the DECLARE CURSOR statement using the WITH RETURN TO CALLER clause.
Nested embedded SQL stored procedures written in C and nested CLI stored procedures cannot return result sets to the client application or calling stored procedure. If a nested embedded SQL stored procedure or a nested CLI stored procedure leaves cursors open when the stored procedure exits, DB2 closes the cursors. For more information on returning result sets from stored procedures, see Returning Result Sets from Stored Procedures.
When you create a stored procedure, you must observe the following restrictions:
OLE (Object Linking and Embedding) automation is part of the OLE 2.0 architecture from Microsoft Corporation. DB2 can invoke methods of OLE automation objects as external stored procedures. For an overview of OLE automation, see Writing OLE Automation UDFs.
After you code an OLE automation object, you must register the methods of the object as stored procedures using the CREATE PROCEDURE statement. To register an OLE automation stored procedure, issue a CREATE PROCEDURE statement with the LANGUAGE OLE clause. The external name consists of the OLE progID identifying the OLE automation object and the method name separated by ! (exclamation mark).
The following CREATE PROCEDURE statement registers an OLE automation stored procedure called "median" for the "median" method of the OLE automation object "db2smpl.salary":
CREATE PROCEDURE median (INOUT sal DOUBLE)
EXTERNAL NAME 'db2smpl.salary!median'
LANGUAGE OLE
FENCED
PARAMETER STYLE DB2SQL
The calling conventions for OLE method implementations are identical to the conventions for procedures written in C or C++.
DB2 automatically handles the type conversions between SQL types and OLE automation types. For a list of the DB2 mappings between supported OLE automation types and SQL types, see Table 16. For a list of the DB2 mappings between SQL types and the data types of the OLE programming language, such as BASIC or C/C++, see Table 17.
Data passed between DB2 and OLE automation stored procedures is passed as call by reference. DB2 does not support SQL types such as DECIMAL or LOCATORS, or OLE automation types such as boolean or CURRENCY, that are not listed in the previously referenced tables. Character and graphic data mapped to BSTR is converted from the database code page to UCS-2 (also known as Unicode, IBM code page 13488) scheme. Upon return, the data is converted back to the database code page. These conversions occur regardless of the database code page. If code page conversion tables to convert from the database code page to UCS-2 and from UCS-2 to the database code page are not installed, you receive an SQLCODE -332 (SQLSTATE 57017).
Following is a sample program demonstrating the use of an OUT host variable. The client application invokes a stored procedure that determines the median salary for employees in the SAMPLE database. (The definition of the median is that half the values lie above it, and half below it.) The median salary is then passed back to the client application using an OUT host variable.
This sample program calculates the median salary of all employees in the SAMPLE database. Since there is no existing SQL column function to calculate medians, the median salary can be found iteratively by the following algorithm:
An application that uses neither the stored procedures technique, nor blocking cursors, must FETCH each salary across the network as shown in Figure 5.
Figure 5. Median Sample Without a Stored Procedure
|
Since only the salary at row n / 2 + 1 is needed, the application discards all the additional data, but only after it is transmitted across the network.
You can design an application using the stored procedures technique that allows the stored procedure to process and discard the unnecessary data, returning only the median salary to the client application. Figure 6 shows this feature.
Figure 6. OUT Parameter Sample Using a Stored Procedure
 |
OUT Client Description shows a sample OUT host variable client application and stored procedure. The sample programs are available in Java as:
The sample programs are available in C as:
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
import java.sql.*; // JDBC classes (1)
import java.math.BigDecimal; // BigDecimal support for packed decimal type
class Spclient
{
static String sql = "";
static String procName = "";
static String inLanguage = "";
static CallableStatement callStmt;
static int outErrorCode = 0;
static String outErrorLabel = "";
static double outMedian = 0;
static
{
try
{
System.out.println();
System.out.println("Java Stored Procedure Sample");
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
}
catch (Exception e)
{
System.out.println("\nError loading DB2 Driver...\n");
e.printStackTrace();
}
}
public static void main(String argv[])
{
Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
try
{
// connect to sample database
// connect with default id/password
con = DriverManager.getConnection(url); (2)
// turn off autocommit
con.setAutoCommit(false); (3)
outLanguage(con);
outParameter(con);
inParameters(con);
inoutParam(con, outMedian);
resultSet(con);
twoResultSets(con);
allDataTypes(con);
// rollback any changes to the database
con.rollback(); (8)
con.close();
}
catch (Exception e)
{
try { con.close(); } catch (Exception x) { }
e.printStackTrace ();
}
} // end main
public static void outParameter(Connection con)
throws SQLException
{
// prepare the CALL statement for OUT_PARAM
procName = "OUT_PARAM";
sql = "CALL " + procName + "(?, ?, ?)";
callStmt = con.prepareCall(sql);
// register the output parameter (4)
callStmt.registerOutParameter (1, Types.DOUBLE);
callStmt.registerOutParameter (2, Types.INTEGER);
callStmt.registerOutParameter (3, Types.CHAR);
// call the stored procedure (5)
System.out.println ("\nCall stored procedure named " + procName);
callStmt.execute();
// retrieve output parameters (6)
outMedian = callStmt.getDouble(1);
outErrorCode = callStmt.getInt(2);
outErrorLabel = callStmt.getString(3);
if (outErrorCode == 0) { (7)
System.out.println(procName + " completed successfully");
System.out.println ("Median salary returned from OUT_PARAM = "
+ outMedian);
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE "
+ outErrorCode);
System.out.println(procName + " failed at " + outErrorLabel);
}
}
}
#include <stdio.h> (1)
#include <stdlib.h>
#include <sql.h>
#include <sqlda.h>
#include <sqlca.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variable for stored procedure name */
char procname[254];
/* Declare host variables for stored procedure error handling */
sqlint32 out_sqlcode; (4)
char out_buffer[33];
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[]) {
EXEC SQL CONNECT TO sample; (2)
EMB_SQL_CHECK("CONNECT TO SAMPLE");
outparameter();
EXEC SQL ROLLBACK;
EMB_SQL_CHECK("ROLLBACK");
printf("\nStored procedure rolled back.\n\n");
/* Disconnect from Remote Database */
EXEC SQL CONNECT RESET; (8)
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
int outparameter() {
/********************************************************\
* Call OUT_PARAM stored procedure *
\********************************************************/
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for passing data to OUT_PARAM */
double out_median;
EXEC SQL END DECLARE SECTION;
strcpy(procname, "OUT_PARAM");
printf("\nCALL stored procedure named %s\n", procname);
/* OUT_PARAM is PS GENERAL, so do not pass a null indicator */
EXEC SQL CALL :procname (:out_median, :out_sqlcode, :out_buffer); (5) (6)
EMB_SQL_CHECK("CALL OUT_PARAM");
/* Check that the stored procedure executed successfully */
if (out_sqlcode == 0) (7)
{
printf("Stored procedure returned successfully.\n");
/***********************************************************\
* Display the median salary returned as an output parameter *
\***********************************************************/
printf("Median salary returned from OUT_PARAM = %8.2f\n", out_median);
}
else
{ /* print the error message, roll back the transaction */
printf("Stored procedure returned SQLCODE %d\n", out_sqlcode);
printf("from procedure section labelled \"%s\".\n", out_buffer);
}
return 0;
}
import java.sql.*; // JDBC classes
import COM.ibm.db2.jdbc.app.*; // DB2 JDBC classes
import java.math.BigDecimal; // Packed Decimal class
public class Spserver
{
public static void outParameter (double[] medianSalary,
int[] errorCode, String[] errorLabel) throws SQLException (1)
{ try
{
int numRecords;
int counter = 0;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel[0] = "GET CONNECTION";
String query = "SELECT COUNT(*) FROM staff";
errorLabel[0] = "PREPARE COUNT STATEMENT";
PreparedStatement stmt = con.prepareStatement(query);
errorLabel[0] = "GET COUNT RESULT SET";
ResultSet rs = stmt.executeQuery();
// move to first row of result set
rs.next();
// set value for the output parameter
errorLabel[0] = "GET NUMBER OF RECORDS";
numRecords = rs.getInt(1); (3)
// clean up first result set
rs.close();
stmt.close();
// get salary result set
query = "SELECT CAST(salary AS DOUBLE) FROM staff "
+ "ORDER BY salary";
errorLabel[0] = "PREPARE SALARY STATEMENT";
PreparedStatement stmt2 = con.prepareStatement(query);
errorLabel[0] = "GET SALARY RESULT SET";
ResultSet rs2 = stmt2.executeQuery(); (2)
while (counter < (numRecords / 2 + 1))
{
errorLabel[0] = "MOVE TO NEXT ROW";
rs2.next(); (4)
counter++;
}
errorLabel[0] = "GET MEDIAN SALARY";
medianSalary[0] = rs2.getDouble(1); (5)
// clean up resources
rs2.close();
stmt2.close();
con.close(); (6)
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
}
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlca.h>
#include <sqludf.h>
#include <sql.h>
#include <memory.h>
/* Declare function prototypes for this stored procedure library */
SQL_API_RC SQL_API_FN out_param (double *, sqlint32 *, char *); (1)
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
/* Declare host variables for basic error-handling */
sqlint32 out_sqlcode;
char buffer[33];
/* Declare host variables used by multiple stored procedures */
sqlint16 numRecords;
double medianSalary;
EXEC SQL END DECLARE SECTION;
SQL_API_RC SQL_API_FN out_param (double *outMedianSalary,
sqlint32 *out_sqlerror, char buffer[33])
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
int counter = 0;
*out_sqlerror = 0;
strcpy(buffer, "DECLARE c1");
EXEC SQL DECLARE c1 CURSOR FOR (2)
SELECT CAST(salary AS DOUBLE) FROM staff
ORDER BY salary;
strcpy(buffer, "SELECT");
EXEC SQL SELECT COUNT(*) INTO :numRecords FROM staff; (3)
strcpy(buffer, "OPEN");
EXEC SQL OPEN c1;
strcpy(buffer, "FETCH");
while (counter < (numRecords / 2 + 1)) {
EXEC SQL FETCH c1 INTO :medianSalary; (4)
/* Set value of OUT parameter to host variable */
*outMedianSalary = medianSalary; (5)
counter = counter + 1;
}
strcpy(buffer, "CLOSE c1");
EXEC SQL CLOSE c1;
return (0); (6)
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end out_param function */
The code page considerations depend on the server.
When a client program (using, for example, code page A) calls a remote stored procedure that accesses a database using a different code page (for example, code page Z), the following events occur:
Therefore, you must run the stored procedure using the same code page as the database, in this example, code page Z. It is a good practice to prep, compile, and bind the server procedure using the same code page as the database.
| Note: | If the parameter of the stored procedure is defined as FOR BIT DATA at the server, conversion does not occur for a CALL statement to DB2 Universal Database for OS/390 or DB2 Universal Database for AS/400, regardless of whether it is explicitly specified in the SQLDA. (Refer to the section on the SQLDA in the SQL Reference for details.) |
For more information on this topic, see Conversion Between Different Code Pages.
When writing a stored procedure in C++, you may want to consider declaring the procedure name using extern "C", as in the following example:
extern "C" SQL_API_RC SQL_API_FN proc_name( short *parm1, char *parm2)
The extern "C" prevents type decoration (or mangling) of the function name by the C++ compiler. Without this declaration, you have to include all the type decorations for the function name when you call the stored procedure.
Any stored procedure written in C or C++, that receives or returns graphic data through its parameter input or output should generally be precompiled with the WCHARTYPE NOCONVERT option. This is because graphic data passed through these parameters is considered to be in DBCS format, rather than the wchar_t process code format. Using NOCONVERT means that graphic data manipulated in SQL statements in the stored procedure will also be in DBCS format, matching the format of the parameter data.
With WCHARTYPE NOCONVERT, no character code conversion occurs between the graphic host variable and the database manager. The data in a graphic host variable is sent to, and received from, the database manager as unaltered DBCS characters. Note that if you do not use WCHARTYPE NOCONVERT, it is still possible for you to manipulate graphic data in wchar_t format in a stored procedure; however, you must perform the input and output conversions manually.
CONVERT can be used in FENCED stored procedures, and it will affect the graphic data in SQL statements within the stored procedure, but not through the stored procedure's interface. NOT FENCED stored procedures must be built using the NOCONVERT option.
In summary, graphic data passed to or returned from a stored procedure through its input or output parameters is in DBCS format, regardless of how it was precompiled with the WCHARTYPE option.
For important information on handling graphic data in C applications, see Handling Graphic Host Variables in C and C++. For information on EUC code sets and application guidelines, see Japanese and Traditional Chinese EUC and UCS-2 Code Set Considerations, and more specifically to Considerations for Stored Procedures.
Stored procedures that applications call with CONNECT TYPE 2 cannot issue a COMMIT or ROLLBACK, either dynamically or statically.