Assign null values from SQL result sets: the SQLCursor.wasNULL method

If the value of the field argument to the SQLCursor.getValue method is NULL, the SQL engine returns a run‑time error.

The following example illustrates the error returned when the argument to SQLCursor.getValue is NULL.

(error(-20144): Null value fetched.)

This means you must always check whether a value is null before attempting to assign a value in an SQL result set to a procedure variable or output parameter. The SQLCursor class provides the wasNULL method for this purpose.

The SQLCursor.wasNULL method returns TRUE if a field in the result set is null. It takes a single integer argument that specifies which field of the current row of the result set to check.

The following example illustrates using the wasNULL method.

CREATE PROCEDURE test_nulls2( )
RESULT ( res_int1 INTEGER ,
res_int2 INTEGER ,
res_int3 INTEGER )
BEGIN
Integer pvar_int1 = new Integer(0);
Integer pvar_int2 = new Integer(0);
Integer pvar_int3 = new Integer(0);
SQLCursor select_t1 = new SQLCursor
( "SELECT c1, c2, c3 from t1" );
select_t1.open();
select_t1.fetch();
while ( select_t1.found() )
{// Assign values from the current row of the SQL result set
// to the pvar_intx procedure variables. Must first check
// whether the values fetched are null: if they are, must set
// pvars explicitly to null.
if ((select_t1.wasNULL(1)) == true)
pvar_int1 = null;
else
pvar_int1 = (Integer) select_t1.getValue(1, INTEGER);
if ((select_t1.wasNULL(2)) == true)
pvar_int2 = null;
else
pvar_int2 = (Integer) select_t1.getValue(2, INTEGER);
if ((select_t1.wasNULL(3)) == true)
pvar_int3 = null;
else
pvar_int3 = (Integer) select_t1.getValue(3, INTEGER);
// Transfer the value from the procedure variables to the
// columns of the current row of the procedure result set.
SQLResultSet.set(1,pvar_int1);
SQLResultSet.set(2,pvar_int2);
SQLResultSet.set(3,pvar_int3);
// Insert the row into the procedure result set.
SQLResultSet.insert();
select_t1.fetch();
}// Close the SQL result set.
select_t1.close();
END