Progress
SQL-92
Guide and Reference
Handling Null Values
Stored procedures routinely need to set and detect null values.
- Stored procedures might need to set the values of SQL statement input parameters or procedure result fields to null.
- Stored procedures must check if the value of a field in an SQL result set is null before assigning it through the SQLCursor.getValue method. The SQL server generates a run-time error if the result-set field specified in getValue is null.
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.
EXAMPLEThis example shows using both techniques to set an SQL input parameter to null:
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.
EXAMPLEThis example illustrates the error returned when the argument to SQLCursor.getValue is null:
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.
EXAMPLEThe next example illustrates using the wasNULL method:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |