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();
SQLNullException
is a subclass of the standardjava.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();...