Null-Handling

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

Wrapper Classes for Null-Handling

When receiving data from the database, SQL nulls are converted to Java null values. Therefore, do not use Java primitive types for output variables in situations where a SQL null may be received.

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

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.

Presume the following declarations:

int empno = 7499; 
Float commission = null;

Example:

#sql { UPDATE emp SET commission = :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 emps table whose salary is at least $50,000, the name and commission are selected into the iterator. Then each row is tested to determine if the commission field is, in fact, null. If so, it is processed accordingly.

Presume the following declarations:

#sql iterator EmployeeIter (String name, Double commission);
EmployeeIter ei;

Example:

#sql ei = { SELECT name, commission FROM emps WHERE salary >= 50000 };

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

---