FairCom Java & .NET Stored Procedures

Handling Null Values

Stored procedures need to routinely set and detect null values:

• Stored procedures may need to set the values of a FairCom DB SQL statement input parameters or procedure result fields to null.
• Stored procedures must check if the value of a field in a FairCom DB SQL result set is null before assigning it through the SQLCursor.getValue() method. (FairCom DB SQL generates a runtime error if the result-set field specified in getValue() is null.)

Setting SQL Statement Input Params & Procedure Result SetFields to Null

Both the setParam() method (see “The setParam Method: Pass Input Values to SQL Statements”) and set() method (see “Returning a Procedure Result Set: the RESULT Clause and DhSQLResultSet”) take objects as their value arguments. You can pass a null reference directly to the method or pass a variable which has been assigned the null value. The following example shows using both techniques to set a FairCom DB SQL input parameter to null.

Example Passing Null Values to setParam()

CREATE PROCEDURE test_nulls( )

BEGIN

Integer pvar_int1 = new Integer(0);

Integer pvar_int2 = new Integer(0);

Integer pvar_int3;

pvar_int3 = null;

SQLIStatement insert_t1 = new SQLIStatement

( "INSERT INTO ADMIN.t1 (c1,c2, c3) values (?,?,?) ");

insert_t1.setParam(1, new Integer(1)); // Set to non-null value

insert_t1.setParam(2, null); // Set directly to null

insert_t1.setParam(3, pvar_int3); // Set indirectly to null

insert_t1.execute();

END

Assigning Null Values from Result Sets:SQLCursor.wasNULL Method

If the value of the field argument to the SQLCursor.getValue() method is null, FairCom DB SQL returns a runtime error:

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

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

The SQLCuror.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 wasNULL().

Example Result Sets for Null Values with wasNULL()

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_t2.getValue(2, INTEGER);

if ((select_t1.wasNULL(3)) == true)

pvar_int3 = null;

else

pvar_int3 = (Integer)select_t13getValue(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