Progress
SQL-92
Guide and Reference


Handling Null Values

Stored procedures routinely need to set and detect null values.

Setting SQL Statement Input Parameters and Procedure Result Set Fields to Null

Both the setParam method and set method take objects as their value arguments. For SQL statement input parameters; see the "Using the Progress SQL-92 Java Classes" section. For procedure result set fields, see the "Returning a Procedure Result Set to Applications: The RESULT Clause and DhSQLResultSet" section. You can pass a null reference directly to the method or pass a variable which has been assigned the null value.

EXAMPLE

This example shows using both techniques to set an SQL input parameter to null:

CREATE TABLE t1 (
     c1 INTEGER,
     c2 INTEGER,
     c3 INTEGER)

CREATE PROCEDURE test_nulls( )
BEGIN
     Integer pvar_int ;
     pvar_int = null ;

     SQLIStatement insert_t1 = new SQLIStatement
     ( "INSERT INTO t1 (c1, c2, c3) values (?,?,?) ");
 
     // Set to non-null value
     insert_t1.setParam(1, new Integer(1));
 
     // Set directly to null
     insert_t1.setParam(2, null);
 
     // Set indirectly to null
     insert_t1.setParam(3, pvar_int);
 
     insert_t1.execute();
END 

Assigning 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 server returns a run-time error.

EXAMPLE

This 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.

EXAMPLE

The next 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);
       else
            select_t1.getValue(3,pvar_int3);
         // 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 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095