JDBC Reference
Page 10 of 31

java.sql.CallableStatement

Cloudscape users do not typically use CallableStatements. In a Java program, it is more natural and easier to call methods with Statements or PreparedStatements and to retrieve the results, if any, through a ResultSet. For example, here's an example of calling a static method that does not return a value:

String archiveRecords = "CALL " +
    HotelStay.archiveRecords(CAST (? AS DATE))";
ps = conn.prepareStatement(archiveRecords);
ps.setDate(1, aDate.getDay(12));
ps.executeUpdate();

Here's an example of calling a static method that returns a value (using a VALUES clause):

Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(
    " VALUES City.findCity(getCurrentConnection(), "+
    " CAST (? AS INT)");
rs.next();
City c = (city) rs.getObject(1);

However, Cloudscape allows you to use CallableStatements to execute methods and retrieve values, even though it is more natural to do so as demonstrated above.

Cloudscape supports all the JDBC 1.2 methods of CallableStatement:

  • getBoolean()
  • getByte()
  • getBytes()
  • getDate()
  • getDouble()
  • getFloat()
  • getInt()
  • getLong()
  • getObject()
  • getShort()
  • getString()
  • getTime()
  • getTimestamp()
  • registerOutParamter()
  • wasNull()

CallableStatements and OUT Parameters

Cloudscape supports OUT parameters and CALL statements that return values, as in the following example:

CallableStatement cs = conn.prepareCall(
   "? = CALL City.findCity(getCurrentConnection(), CAST (? AS INT))");
cs.registerOutParameter(1, java.sql.Types.OTHER);
cs.setInt(2, 35);
cs.executeUpdate();
City s = (City) cs.getObject(1);

NOTE: Using a CALL statement with a method that returns a value is only supported with the ? = syntax.

Register the output type of the parameter before executing the call.

CallableStatements and INOUT Parameters

INOUT parameters are not a good fit for Java. Because of this, INOUT parameters map to an array of the parameter type in Java. (The method must take an array as its parameter.) This conforms to the recommendations of the SQLJ standard.

Given the following example:

CallableStatement call = conn.prepareCall(
    "{CALL (CLASS JBMSTours.Util).doubleMyInt(?)}"); 
// for inout parameters, it is good practice to 
// register the outparameter before setting the input value
call.registerOutParameter(1, java.sql.Types.INTEGER); call.setInt(1,10); call.executeQuery(); int retval = call.getInt(1);

the method doubleIt should take a one-dimensional array of ints. Here is sample source code for that method:

public static void doubleMyInt(int[] i) {
    i[0] *=2;
    /* Cloudscape returns the first element of the array.*/
}

NOTE: The return value is not wrapped in an array even though the parameter to the method is.

Table 6-2 INOUT Parameter Type Correspondence

JDBC Type

Array Type for Method Parameter

Value and Return Type

BIGINT

long[]

long

BINARY

byte[][]

byte[]

BIT

boolean[]

boolean

DATE

java.sql.Date[]

java.sql.Date

DOUBLE

double[]

double

FLOAT

double[]

double

INTEGER

int[]

int

LONGVARBINARY

byte[][]

byte[]

REAL

float[]

float

SMALLINT

short[]

short

TIME

java.sql.Time[]

java.sql.Time

TIMESTAMP

java.sql.Timestamp[]

java.sql.Timestamp

TINYINT

byte[]

byte

VARBINARY

byte[][]

byte[]

OTHER

yourType[]

yourType

Register the output type of the parameter before executing the call. For INOUT parameters, it is good practice to register the output parameter before setting its input value.