Notes to Figure 53:
1 This SQLJ clause declares the iterator named NameSal, which is used to retrieve
the rows that will be returned to the stored procedure caller in a result set.
2 The declaration for the stored procedure method contains declarations for a single
passed parameter, followed by the declaration for the result set object.
3 This SQLJ clause executes the SELECT to obtain the rows for the result set,
constructs an iterator object that contains those rows, and assigns the iterator
object to variable iter1.
4 This SQLJ clause retrieves a value into the parameter that is returned to the stored
procedure caller.
5 This statement uses the getResultSet method to assign the contents of the iterator
to the result set that is returned to the caller.
Related concepts
“Differences between Java routines and other routines” on page 176
“Retrieving multiple result sets from a stored procedure in an SQLJ
application” on page 134
Related tasks
“Retrieving multiple result sets from a stored procedure in a JDBC application”
on page 46
Techniques for testing a Java routine
The most common techniques for testing a Java routine are testing the routine as a
stand-alone program, using the DB2 Unified Debugger, enabling collection of DB2
debug information, and testing the routine as a stand-alone program, and writing
your own debug information from your routine.
package s1;
import sqlj.runtime.*;
import java.sql.*;
import java.math.*;
#sql iterator NameSal(String LastName, BigDecimal Salary); 1
public class S1Sal
{
public static void getSals(BigDecimal[] AvgSalParm,
java.sql.ResultSet[] rs) 2
throws SQLException
{
NameSal iter1;
try
{
#sql iter1 = {SELECT LASTNAME, SALARY FROM EMP 3
WHERE SALARY>0 ORDER BY SALARY DESC};
#sql {SELECT AVG(SALARY) INTO :(AvgSalParm[0]) FROM EMP}; 4
}
catch (SQLException e)
{
System.out.println("SQLCODE returned: " + e.getErrorCode());
throw(e);
}
rs[0] = iter1.getResultSet(); 5
}
}
Figure 53. Java stored procedure that returns a result set
Chapter 5. Java stored procedures and user-defined functions 179
To Next Page
Loading...
Need help?
General