CallableStatement methods handle OUT parameters in C function and Java user-defined routines (UDRs). Two registerOutParameter() methods specify the data type of OUT parameters to the driver. A series of getXXX() methods retrieves OUT parameters.
IBM Informix Dynamic Server, Version 9.2x and 9.3x, considers OUT parameters to be statement local variables (SLVs). SLVs are valid only for the life of a single statement and cannot be returned directly upon executing the routine. The JDBC CallableStatement interface provides a method for retrieving OUT parameters.
With IBM Informix Dynamic Server, Version 10.0 and later, the OUT parameter routine makes available a valid blob descriptor and data to the JDBC client for a BINARY OUT parameter. Using receive methods in IBM Informix JDBC Driver, Version 3.0 and later, supporting IDS 10.0 and later, you can use these OUT parameter descriptors and data provided by the server.
Exchange of descriptor and data between IDS and JDBC is consistent with the existing mechanism by which data is exchanged for the result set methods of JDBC, such as passing the blob descriptor and data through SQLI protocol methods. (SPL UDRs are the only type of UDRs supporting BINARY OUT parameters.)
For background information, refer to the following documentation:
Only Informix database servers versions 9.2 and later return an OUT parameter to IBM Informix JDBC Driver. IBM Informix Dynamic Server, Version 9.4 and later supports multiple OUT parameters.
For examples of how to use OUT parameters, see the CallOut1.java, CallOut2.java, CallOut3.java, and CallOut4.java example programs in the basic subdirectory of the demo directory where your IBM Informix JDBC Driver is installed.
This section describes the restrictions imposed by different versions of the 9.x and later Dynamic Server. It also describes enhancements made to the JDBC Driver and the restrictions imposed by it.
Versions 9.2x and 9.3x of IBM Informix Dynamic Server have the following requirements and limitations concerning OUT parameters:
IBM Informix Dynamic Server, Version 10.0, allows you to specify INOUT parameters (C, SPL, or Java UDRs).
These restrictions, for server versions 9.2x and 9.3x, are imposed whether users create C, SPL, or Java UDRs.
The functionality of the IBM Informix Dynamic Server, Version 9.4 allows:
You cannot specify INOUT parameters.
For more information on UDRs, see IBM Informix: User-Defined Routines and Data Types Developer's Guide and IBM Informix: J/Foundation Developer's Guide.
The CallableStatement object provides a way to call or execute UDRs in a standard way for all database servers. Results from the execution of these UDRs are returned as a result set or as an OUT parameter.
The following is a program that creates a user-defined function, myudr, with two OUT parameters and one IN parameter, and then executes the myudr() function. The example requires server-side support for multiple OUT parameters; hence it will only work for IBM Informix Dynamic Server, Version 9.4 or above. For more information on UDRs, see IBM Informix: User-Defined Routines and Data Types Developer's Guide and IBM Informix: J/Foundation Developer's Guide.
import java.sql.*;
public class myudr {
public myudr() {
}
public static void main(String args[]) {
Connection myConn = null;
try {
Class.forName("com.informix.jdbc.IfxDriver");
myConn = DriverManager.getConnection(
"jdbc:informix-sqli:MYSYSTEM:18551/testDB:"
+"INFORMIXSERVER=patriot1;user=USERID;"
+"password=MYPASSWORD");
}
catch (ClassNotFoundException e) {
System.out.println(
"problem with loading Ifx Driver\n" + e.getMessage());
}
catch (SQLException e) {
System.out.println(
"problem with connecting to db\n" + e.getMessage());
}
try {
Statement stmt = myConn.createStatement();
stmt.execute("DROP FUNCTION myudr");
}
catch (SQLException e){
}
try
{
Statement stmt = myConn.createStatement();
stmt.execute(
"CREATE FUNCTION myudr(OUT arg1 int, arg2 int, OUT arg3 int)"
+" RETURNS boolean; LET arg1 = arg2; LET arg3 = arg2 * 2;"
+"RETURN 't'; END FUNCTION;");
}
catch (SQLException e) {
System.out.println(
"problem with creating function\n" + e.getMessage());
}
Connection conn = myConn;
try
{
String command = "{? = call myudr(?, ?, ?)}";
CallableStatement cstmt = conn.prepareCall (command);
// Register arg1 OUT parameter
cstmt.registerOutParameter(1, Types.INTEGER);
// Pass in value for IN parameter
cstmt.setInt(2, 4);
// Register arg3 OUT parameter
cstmt.registerOutParameter(3, Types.INTEGER);
// Execute myudr
ResultSet rs = cstmt.executeQuery();
// executeQuery returns values via a resultSet
while (rs.next())
{
// get value returned by myudr
boolean b = rs.getBoolean(1);
System.out.println("return value from myudr = " + b);
}
// Retrieve OUT parameters from myudr
int i = cstmt.getInt(1);
System.out.println("arg1 OUT parameter value = " + i);
int k = cstmt.getInt(3);
System.out.println("arg3 OUT parameter value = " + k);
rs.close();
cstmt.close();
conn.close();
}
catch (SQLException e)
{
System.out.println("SQLException: " + e.getMessage());
System.out.println("ErrorCode: " + e.getErrorCode());
e.printStackTrace();
}
}
}
- - -
.../j2sdk1.4.0/bin/java ... myudr
return value from myudr = true
arg1 OUT parameter value = 4
arg3 OUT parameter value = 8
IBM Informix JDBC Driver has the following requirements and limitations concerning OUT parameters:
You can determine if a function contains an OUT parameter by calling the CallableStatement.getWarnings() method or by calling the IfmxCallableStatement.hasOutParameter() method, which return TRUE if the function has an OUT parameter.
If a function contains an OUT parameter, you must use the CallableStatement.registerOutParameter() method to register the OUT parameter, the setXXX() methods to register the IN and OUT parameter values, and the getXXX() method to retrieve the OUT parameter value.
CallableStatement cstmt = myConn.prepareCall("{call
myFunction(25, ?)}");
Instead, use a statement that does not specify literal parameters:
CallableStatement cstmt = myConn.prepareCall("{call
myFunction(?, ?)}");
Call the setXXX() methods for both parameters.
CallableStatement cstmt = myConn.prepareCall("{call
foo(?::lvarchar, ?)}";In addition, some Informix types do not map to java.sql.Types values. Extensions for setNull() and registerOutParameter() fix these problems. See IN and OUT Parameter Type Mapping next.
These restrictions apply to a JDBC application that handles C, SPL, or Java UDRs.
An exception is thrown by the registerOutParameter(int, int), registerOutParameter(int, int, int), or setNull(int, int) method if the driver cannot find a matching Informix type or finds a mapping ambiguity (more than one matching Informix type). The table that follows shows the mappings the CallableStatement interface uses. Asterisks ( * ) indicate mapping ambiguities.
IFX_TYPE_MULTISET
IFX_TYPE_SET
IFX_TYPE_UDTFIX
IFX_TYPE_BLOB
IFX_TYPE_CLOB
IFX_TYPE_LVARCHAR
1 This mapping is JDBC compliant. You can map the JDBC FLOAT data type to the Informix SMALLFLOAT data type for backward compatibility by setting the IFX_SET_FLOAT_AS_SMFLOAT connection property to 1.
To avoid mapping ambiguities, use the following extensions to CallableStatement, defined in the IfmxCallableStatement interface:
public void IfxRegisterOutParameter(int parameterIndex, int ifxType) throws SQLException; public void IfxRegisterOutParameter(int parameterIndex, int ifxType, String name) throws SQLException; public void IfxRegisterOutParameter(int parameterIndex, int ifxType, int scale) throws SQLException; public void IfxSetNull(int i, int ifxType) throws SQLException; public void IfxSetNull(int i, int ifxType, String name) throws SQLException;
Possible values for the ifxType parameter are listed in Using the IfxTypes Class.
IBM Informix Dynamic Server, Version 10.0, makes available to the JDBC client valid BLOB descriptors and data to support binary OUT parameters for SPL UDRs.
IBM Informix JDBC Driver, Version 3.0, can receive the OUT parameter descriptor and data provided by the server and use it in Java applications.