DB2 Version 10.1 for Linux, UNIX, and Windows

Calling stored procedures in SQLJ applications

To call a stored procedure, you use an executable clause that contains an SQL CALL statement.

You can execute the CALL statement with host identifier parameters. You can execute the CALL statement with literal parameters only if the data server on which the CALL statement runs supports execution of the CALL statement dynamically.

The basic steps in calling a stored procedure are:

Assign values to input (IN or INOUT) parameters.
Call the stored procedure.
Process output (OUT or INOUT) parameters.
If the stored procedure returns multiple result sets, retrieve those result sets.

The following code illustrates calling a stored procedure that has three input parameters and three output parameters. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 1. Example of calling a stored procedure in an SQLJ application

String FirstName="TOM";            // Input parameters                  1 
String LastName="NARISINST";
String Address="IBM";
int CustNo;                        // Output parameters
String Mark;
String MarkErrorText;
…
#sql [myConnCtx] {CALL ADD_CUSTOMER(:IN FirstName,                      2 
                            :IN LastName,
                            :IN Address,
                            :OUT CustNo,
                            :OUT Mark,
                            :OUT MarkErrorText)};
                                  // Call the stored procedure
System.out.println("Output parameters from ADD_CUSTOMER call: ");
System.out.println("Customer number for " + LastName + ": " + CustNo);  3 
System.out.println(Mark);
If (MarkErrorText != null) 
  System.out.println(" Error messages:" + MarkErrorText);