Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## 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();
    ...

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index