Retrieving rows as byte data in JDBC applications

You can use the DB2ResultSet.getDBRowDataAsBytes method to retrieve an entire row from a table as raw bytes, and retrieve the column data from the returned rows.

Procedure

Create a Statement or PreparedStatement object.
Invoke the Statement.executeQuery method or PreparedStatement.executeQuery method to obtain a ResultSet object.
Cast the ResultSet object as a DB2ResultSet object.
Repeat the following steps until there are no rows left to retrieve:
1. Move the cursor to the next row.
2. Call the DB2ResultSet.getDBRowDataAsBytes method to retrieve an Object array that contains the row data.
3. Cast the first element of the Object array as a byte array.
  This byte array contains the data for each column in the row. See the description of getDBRowDataAsBytes in DB2ResultSet interface for the data format.
4. Cast the second element of the Object array as an int array.
  Each integer in this array contains the offset into the row data byte array of the beginning of the data for a column.
5. Call the DB2ResultSet.getDBRowDescriptor method to retrieve an int array that contains the row data.
  This array contains descriptive information about each column in the row. See the description of getDBRowDescriptor in DB2ResultSet interface for the data format.
6. Use the offset value for each column to locate the column data, and retrieve each byte of the column data.
7. Use the information that is returned from DB2ResultSet.getDBRowDescriptor to convert the bytes into a value of the column type.

Example

Suppose that table MYTABLE is defined like this:

CREATE TABLE MYTABLE (
 INTCOL1 INTEGER NOT NULL,
 INTCOL2 INTEGER NOT NULL)

The following program retrieves rows of data as raw bytes, and retrieves the column values from each returned row. The numbers to the right of statements correspond to the previously described steps.

import java.sql.*;
import com.ibm.db2.jcc.*;

Connection conn;
…
String sql1="select INTCOL, CHARCOL FROM MYTABLE";
int colSqltype;
int colCcsid
int colLen;
int colRep;
Object obj[];
byte data[];
int returnedInfo[];
int numberOfColumns;
int j;
int offsets[];
byte b1;
byte b2;
byte b3
byte b4;
int intVal;

try {
 Statement stmt = conn.createStatement ();                     1 
 DB2ResultSet rs = (DB2ResultSet)stmt.executeQuery(sql1);      2,3 
 int rowNum=0;
 while(!rs.isLast())
 {
  rs.next();                                                   4a 
  rowNum++;
  obj[] = rs.getDBRowDataAsBytes();                            4b 
  //********************************************
  // Retrieve the data and offsets.             
  // The code for checking the row indicator is 
  // not shown. Assume that the row indicator   
  // indicates that the data is valid.          
  //********************************************
  data[]=(byte[])obj[0];                                       4c 
  offsets[]= (int [])obj[1];                                   4d 
  //********************************************
  // Retrieve the metadata for each column.     
  // The first element in the array that is     
  // returned by getDBRowDescriptor contains    
  // the number of columns in the row.          
  //********************************************
  returnedInfo[] = rs.getDBRowDescriptor();                    4e 
  numberOfColumns=returnedInfo[0];
  for(j=0;j<numberOfColumns;j++) {
   //********************************************
   // Get the metadata for a column.             
   //********************************************
   colSqltype=returnedInfo[(4*j)+1];
   colCcsid=returnedInfo[(4*j)+2];
   colLen=returnedInfo[(4*j)+3];
   colRep=returnedInfo[(4*j)+4];
   //********************************************
   // Determine the type of the column. The code 
   // is not shown here.                         
   //********************************************
   …
   //********************************************
   // Suppose that the metadata indicates that a 
   // column is INT and Little Endian.            
   // The following code retrieves the four bytes 
   // of the value and converts them to an integer.
   //********************************************
   b1 = data[offsets[j]+5];                                    4f 
   b2 = data[offsets[j]+4];
   b3 = data[offsets[j]+3];
   b4 = data[offsets[j]+2];
   intVal = ((0xFF & b1) << 24) | ((0xFF & b2) << 16) |        4g 
               ((0xFF & b3) << 8) | (0xFF & b4);
   System.out.print("Row "+rowNum+" column "+(j+1)+" "+intVal);
  }
 }
}