Indicator variables in SQLJ applications

In SQLJ programs, you can use indicator variables to pass the NULL value to or from a data server, to pass the default value for a column to the data server, or to indicate that a host variable value is unassigned.

A host variable or host expression can be followed by an indicator variable. An indicator variable begins with a colon (:) and has the data type short. For input, an indicator variable indicates whether the corresponding host variable or host expression has the default value, a non-null value, the null value, or is unassigned. An unassigned variable in an SQL statement yields the same results as if the variable and its target column were not in the SQL statement. For output, the indicator variable indicates where the corresponding host variable or host expression has a non-null value or a null value.

In SQLJ programs, indicator variables that indicate a null value perform the same function as assigning the Java™ null value to a table column. However, you need to use an indicator variable to retrieve the SQL NULL value from a table into a host variable.

You can use indicator variables that assign the default value or the unassigned value to columns to simplify the coding in your applications. For example, if a table has four columns, and you might need to update any combination of those columns, without the use of default indicator variables or unassigned indicator variables, you need 15 UPDATE statements to perform all possible combinations of updates. With default indicator variables and unassigned indicator variables, you can use one UPDATE statement with all four columns in the SET statement to perform all possible updates. You use the indicator variables to indicate which columns you want to set to their default values, and which columns you do not want to update.

For input, SQLJ supports the use of indicator variables for INSERT, UPDATE, or MERGE statements.

If you customize your SQLJ application, you can assign one of the following values to an indicator variable in an SQLJ application to specify the type of the corresponding input host variable.

Indicator value	Equivalent constant	Meaning of value
-1	sqlj.runtime.ExecutionContext.DBNull	Null
-2, -3, -4, -6		Null
-5	sqlj.runtime.ExecutionContext.DBDefault	Default
-7	sqlj.runtime.ExecutionContext.DBUnassigned	Unassigned
`short-value` >=0	sqlj.runtime.ExecutionContext.DBNonNull	Non-null

If you do not customize the application, you can assign one of the following values to an indicator variable to specify the type of the corresponding input host variable.

Indicator value	Equivalent constant	Meaning of value
-1	sqlj.runtime.ExecutionContext.DBNull	Null
-7 <= `short-value` < -1		Null
0	sqlj.runtime.ExecutionContext.DBNonNull	Non-null
`short-value` >0		Non-null

For output, SQLJ supports the use of indicator variables for the following statements:

CALL with OUT or INOUT parameters
FETCH iterator INTO host-variable
SELECT … INTO host-variable-1,…host-variable-n

SQLJ assigns one of the following values to an indicator variable to indicate whether an SQL NULL value was retrieved into the corresponding host variable.

Indicator value	Equivalent constant	Meaning of value
-1	sqlj.runtime.ExecutionContext.DBNull	Retrieved value is SQL NULL
0		Retrieved value is not SQL NULL

You cannot use indicator variables to update result sets. To assign null values or default values to result sets, or to indicate that columns are unassigned, call ResultSet.updateObject on the underlying JDBC ResultSet objects of the SQLJ iterators.

The following examples demonstrate how to use indicator variables.

All examples require that the data server supports extended indicators.

Example of using indicators to assign the default value to columns during an INSERT:

In this example, the MGRNO and LOCATION columns need to be set to their default values. To do this, the code performs these steps:

Assigns the value ExecutionContext.DBNonNull to the indicator variables (deptInd, dNameInd, rptDeptInd) for the input host variables (dept, dName, rptDept) that send non-default values to the target columns.
Assigns the value ExecutionContext.DBDefault to the indicator variables (mgrInd, locnInd) for the input host variables (mgr, locn) that send default values to the target columns.
Executes an INSERT statement with the host variable and indicator variable pairs as input.

The numbers to the right of selected statements correspond to the previously described steps.

import sqlj.runtime.*;
…
String dept = "F01";
String dName = "SHIPPING";
String rptDept = "A00";
String mgr, locn = null;
short deptInd, dNameInd, mgrInd, rptDeptInd, locnInd;
// Set indicator variables for dept, dName, rptDept to non-null
deptInd = dNameInd = rptDeptInd = ExecutionContext.DBNonNull;    1 
mgrInd = ExecutionContext.DBDefault;                             2 
locnInd = ExecutionContext.DBDefault;
#sql [ctxt]                                                      3 
  {INSERT INTO DEPARTMENT
    (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION)
    VALUES (:dept :deptInd, :dName :dNameInd,:mgr :mgrInd,
    :rptDept :rptDeptInd, :locn :locnInd)};

Example of using indicators to assign the default value to leave column values unassigned during an UPDATE:

In this example, in rows for department F01, the MGRNO column needs to be set to its default value, the DEPTNAME column value needs to be changed to RECEIVING, and the DEPTNO, DEPTNAME, ADMRDEPT, and LOCATION columns need to remain unchanged. To do this, the code performs these steps:

Assigns the new value for the DEPTNAME column to the dName input host variable.
Assigns the value ExecutionContext.DBDefault to the indicator variable (mgrInd) for the input host variable (mgr) that sends the default value to the target column.
Assigns the value ExecutionContext.DBUnassigned to the indicator variables (deptInd, dNameInd, rptDeptInd, and locnInd) for the input host variables (dept, dName, rptDept, and locn) that need to remain unchanged by the UPDATE operation.
Executes an UPDATE statement with the host variable and indicator variable pairs as input.

The numbers to the right of selected statements correspond to the previously described steps.

import sqlj.runtime.*;
…
String dept = null;
String dName = "RECEIVING";                                      1 
String rptDept = null;
String mgr, locn = null;
short deptInd, dNameInd, mgrInd, rptDeptInd, locnInd;
dNameInd = ExecutionContext.DBNonNull;
mgrInd = ExecutionContext.DBDefault;                             2 
deptInd = rptDeptInd = locnInd = ExecutionContext.DBUnassigned;  3 
#sql [ctxt]                                                      4 
  {UPDATE DEPARTMENT
    SET DEPTNO = :dept :deptInd,
        DEPTNAME = :dName :dNameInd,
        MGRNO = :mgr :mgrInd,
        ADMRDEPT = :rptDept :rptDeptInd,
        LOCATION = :locn :locnInd
    WHERE DEPTNO = "F01"
  };

Example of using indicators to retrieve NULL values from columns:

In this example, the HIREDATE column can return the NULL value. To handle this case, the code performs these steps:

Defines an indicator variable to indicate when the NULL value is returned from HIREDATE.
Executes FETCH statements with the host variable and indicator variable pairs as output.
Checks the indicator variable to determine whether a NULL value was returned.

The numbers to the right of selected statements correspond to the previously described steps.

import sqlj.runtime.*;
…
#sql iterator ByPos(String, Date);  // Declare positioned iterator ByPos
{
  …
  ByPos positer;                    // Declare object of ByPos class
  String name = null;               // Declare host variables
  Date hrdate = null;
  short indhrdate = null;           // Declare indicator variable       1 
  #sql [ctxt] positer = 
    {SELECT LASTNAME, HIREDATE FROM EMPLOYEE};                         
                                    // Assign the result table of the SELECT
                                    // to iterator object positer
  #sql {FETCH :positer INTO :name, :hrdate :indhrdate };                2 
                                    // Retrieve the first row
  while (!positer.endFetch())       // Check whether the FETCH returned a row
  { if(indhrdate == ExecutionContext.DBNonNull {                        3 
     System.out.println(name + " was hired in " +
      hrdate); }
    else {
     System.out.println(name + " has no hire date "); }
    #sql {FETCH :positer INTO :name, :hrdate };
                                    // Fetch the next row
  }
  positer.close();                  // Close the iterator               5 
}

Example of assigning default values to result set columns:

In this example, the HIREDATE column in a result set needs to be set to its default value. To do this, the code performs these steps:

Retrieves the underlying ResultSet from the iterator that holds the retrieved data.
Executes the ResultSet.updateObject method with the DB2PreparedStatement.DB_PARAMETER_DEFAULT constant to assign the default value to the result set column.

The numbers to the right of selected statements correspond to the previously described steps.

#sql public iterator sensitiveUpdateIter 
 implements sqlj.runtime.Scrollable, sqlj.runtime.ForUpdate 
 with (sensitivity=sqlj.runtime.ResultSetIterator.SENSITIVE, 
 updateColumns="LASTNAME, HIREDATE") (String, Date);

  String name;                    // Declare host variables
  Date hrdate;

sensitiveUpdateIter iter = null;
#sql [ctx] iter = { SELECT LASTNAME, HIREDATE FROM EMPLOYEE};

iter.next();

java.sql.ResultSet rs =  iter.getResultSet();                       1 
rs.updateString("LASTNAME", "FORREST");
rs.updateObject
 (2, com.ibm.db2.jcc.DB2PreparedStatement.DB_PARAMETER_DEFAULT););  2,3 
rs.updateRow();
iter.close();