Null-Handling

Java primitive types (such as int, double, or float) cannot have null values, which you must consider in choosing your result expression and host expression types.

Wrapper Classes for Null-Handling

SQLJ consistently enforces retrieving SQL nulls as Java nulls, in contrast to JDBC, which retrieves nulls as 0 or false for certain datatypes. Therefore, do not use Java primitive types in SQLJ for output variables in situations where a SQL null may be received, because Java primitive types cannot take null values.

This pertains to result expressions, output or input-output host expressions, and iterator column types. If the receiving Java type is primitive and an attempt is made to retrieve a SQL null, then a sqlj.runtime.SQLNullException is thrown, and no assignment is made.

To avoid the possibility of null values being assigned to Java primitives, use the following wrapper classes instead of primitive types:

• java.lang.Boolean

• java.lang.Byte

• java.lang.Short

• java.lang.Integer

• java.lang.Long

• java.lang.Double

• java.lang.Float

In case you must convert back to a primitive value, each of these wrapper classes has an xxxValue() method. For example, intValue() returns an int value from an Integer object and floatValue() returns a float value from a Float object. Do this as in the following example, presuming intobj is an Integer object:

int j = intobj.intValue();

Notes: SQLNullException is a subclass of the standard java.sql.SQLException class. See "Using SQLException Subclasses". Because Java objects can have null values, there is no need in SQLJ for indicator variables such as those used in other host languages (C, C++, and COBOL for example).

Examples of Null-Handling

The following examples show the use of the java.lang wrapper classes to handle null data.

Example: Null Input Host Variable

In the following example, a Float object is used to pass a null value to the database. You cannot use the Java primitive type float to accomplish this.

Example:

int empno = 7499; 
Float commission = null;

#sql { UPDATE emp SET comm = :commission WHERE empno = :empno };

Example: Null Iterator Rows

In the following example, a Double column type is used in an iterator to allow for the possibility of null data.

For each employee in the EMP table whose salary is at least $50,000, the employee name (ENAME) and commission (COMM) are selected into the iterator. Then each row is tested to determine if the COMM field is, in fact, null. If so, it is processed accordingly.

Presume the following declaration:

#sql iterator EmployeeIter (String ename, Double comm);

Example:

EmployeeIter ei;
#sql ei = { SELECT ename, comm FROM emp WHERE sal >= 50000 };

while (ei.next())
{
   if (ei.comm() == null) 
      System.out.println(ei.ename() + " is not on commission.");
}
ei.close();
...

---