ROWIDs in SQLJ with the IBM Data Server Driver for JDBC and SQLJ

Db2® for z/OS® and Db2 for IBM® i support the ROWID data type for a column in a table. A ROWID is a value that uniquely identifies a row in a table.

Although IBM Informix® also supports rowids, those rowids have the INTEGER data type. You can select an IBM Informix rowid column into a variable with a four-byte integer data type.

If you use columns with the ROWID data type in SQLJ programs, you need to customize those programs.

JDBC 4.0 includes interface java.sql.RowId that you can use in iterators and in CALL statement parameters. If you do not have JDBC 4.0, you can use the IBM Data Server Driver for JDBC and SQLJ-only class com.ibm.db2.jcc.DB2RowID. For an iterator, you can also use the byte[] object type to retrieve ROWID values.

The following code shows an example of an iterator that is used to select values from a ROWID column:

Figure 1. Example of using an iterator to retrieve ROWID values

#sql iterator PosIter(int,String,java.sql.RowId);
                               // Declare positioned iterator
                               // for retrieving ITEM_ID (INTEGER), 
                               // ITEM_FORMAT (VARCHAR), and ITEM_ROWID (ROWID)
                               // values from table ROWIDTAB
{
  PosIter positrowid;          // Declare object of PosIter class
  java.sql.RowId rowid = null;
  int id = 0;
  String i_fmt = null;
                               // Declare host expressions
  #sql [ctxt] positrowid = 
    {SELECT ITEM_ID, ITEM_FORMAT, ITEM_ROWID FROM ROWIDTAB 
       WHERE ITEM_ID=3};
                               // Assign the result table of the SELECT
                               // to iterator object positrowid
  #sql {FETCH :positrowid INTO :id, :i_fmt, :rowid};
                               // Retrieve the first row
  while (!positrowid.endFetch())       
                               // Check whether the FETCH returned a row
    {System.out.println("Item ID " + id + " Item format " +  
       i_fmt + " Item ROWID ");  
       MyUtilities.printBytes(rowid.getBytes());
                          // Use the getBytes method to
                          // convert the value to bytes for printing.
                          // Call a user-defined method called
                          // printBytes (not shown) to print
                          // the value.
       #sql {FETCH :positrowid INTO :id, :i_fmt, :rowid};
                               // Retrieve the next row
    }
  positrowid.close();          // Close the iterator
}

The following code shows an example of calling a stored procedure that takes three ROWID parameters: an IN parameter, an OUT parameter, and an INOUT parameter.

Figure 2. Example of calling a stored procedure with a ROWID parameter

java.sql.RowId in_rowid = rowid;
java.sqlRowId out_rowid = null;
java.sql.RowId inout_rowid = rowid;
                                  // Declare an IN, OUT, and 
                                  // INOUT ROWID parameter
…
#sql [myConnCtx] {CALL SP_ROWID(:IN in_rowid,
                            :OUT out_rowid,
                            :INOUT inout_rowid)};
                                  // Call the stored procedure
System.out.println("Parameter values from SP_ROWID call: ");
System.out.println("OUT parameter value ");
MyUtilities.printBytes(out_rowid.getBytes());
                          // Use the getBytes method to
                          // convert the value to bytes for printing 
                          // Call a user-defined method called
                          // printBytes (not shown) to print
                          // the value.
System.out.println("INOUT parameter value ");
MyUtilities.printBytes(inout_rowid.getBytes());