SQLJ and JDBC in the same application

You can combine SQLJ clauses and JDBC calls in a single program.

To do this effectively, you need to be able to do the following things:
  • Use a JDBC Connection to build an SQLJ ConnectionContext, or obtain a JDBC Connection from an SQLJ ConnectionContext.
  • Use an SQLJ iterator to retrieve data from a JDBC ResultSet or generate a JDBC ResultSet from an SQLJ iterator.
Building an SQLJ ConnectionContext from a JDBC Connection: To do that:
  1. Execute an SQLJ connection declaration clause to create a ConnectionContext class.
  2. Load the driver or obtain a DataSource instance.
  3. Invoke the SQLJ DriverManager.getConnection or DataSource.getConnection method to obtain a JDBC Connection.
  4. Invoke the ConnectionContext constructor with the Connection as its argument to create the ConnectionContext object.
Obtaining a JDBC Connection from an SQLJ ConnectionContext: To do this,
  1. Execute an SQLJ connection declaration clause to create a ConnectionContext class.
  2. Load the driver or obtain a DataSource instance.
  3. Invoke the ConnectionContext constructor with the URL of the driver and any other necessary parameters as its arguments to create the ConnectionContext object.
  4. Invoke the JDBC ConnectionContext.getConnection method to create the JDBC Connection object.

See "Connect to a data source using SQLJ" for more information on SQLJ connections.

Retrieving JDBC result sets using SQLJ iterators: Use the iterator conversion statement to manipulate a JDBC result set as an SQLJ iterator. The general form of an iterator conversion statement is: 
#sql iterator={CAST :result-set};
Before you can successfully cast a result set to an iterator, the iterator must conform to the following rules:
  • The iterator must be declared as public.
  • If the iterator is a positioned iterator, the number of columns in the result set must match the number of columns in the iterator. In addition, the data type of each column in the result set must match the data type of the corresponding column in the iterator.
  • If the iterator is a named iterator, the name of each accessor method must match the name of a column in the result set. In addition, the data type of the object that an accessor method returns must match the data type of the corresponding column in the result set.

The code in Figure 1 builds and executes a query using a JDBC call, executes an iterator conversion statement to convert the JDBC result set to an SQLJ iterator, and retrieves rows from the result table using the iterator.

Figure 1. Converting a JDBC result set to an SQLJ iterator
#sql public iterator ByName(String LastName, Date HireDate);      1 
public void HireDates(ConnectionContext connCtx, String whereClause)
{

  ByName nameiter;           // Declare object of ByName class
  Connection conn=connCtx.getConnection();
                             // Create JDBC connection
  Statement stmt = conn.createStatement();                        2 
  String query = "SELECT LASTNAME, HIREDATE FROM EMPLOYEE";
  query+=whereClause;   // Build the query
  ResultSet rs = stmt.executeQuery(query);                        3 
  #sql [connCtx] nameiter = {CAST :rs};                           4 
  while (nameiter.next())
  {
    System.out.println( nameiter.LastName() + " was hired on " 
      + nameiter.HireDate());   

  }
  nameiter.close();                                               5 
  stmt.close();                    
}

Notes to Figure 1:

Note Description
 1  This SQLJ clause creates the named iterator class ByName, which has accessor methods LastName() and HireDate() that return the data from result table columns LASTNAME and HIREDATE.
 2  This statement and the following two statements build and prepare a query for dynamic execution using JDBC.
 3  This JDBC statement executes the SELECT statement and assigns the result table to result set rs.
 4  This iterator conversion clause converts the JDBC ResultSet rs to SQLJ iterator nameiter, and the following statements use nameiter to retrieve values from the result table.
 5  The nameiter.close() method closes the SQLJ iterator and JDBC ResultSet rs.

Generating JDBC ResultSets from SQLJ iterators: Use the getResultSet method to generate a JDBC ResultSet from an SQLJ iterator. Every SQLJ iterator has a getResultSet method. After you access the ResultSet that underlies an iterator, you need to fetch rows using only the ResultSet.

The code in Figure 2 generates a positioned iterator for a query, converts the iterator to a result set, and uses JDBC methods to fetch rows from the table.

Figure 2. Converting an SQLJ iterator to a JDBC ResultSet
#sql iterator EmpIter(String, java.sql.Date);
{
…
  EmpIter iter=null;
  #sql [connCtx] iter= 
    {SELECT LASTNAME, HIREDATE FROM EMPLOYEE};                1 
  ResultSet rs=iter.getResultSet();                           2 
  while (rs.next())                                           3 
  { System.out.println(rs.getString(1) + " was hired in " +
      rs.getDate(2));
  }
  rs.close();                                                 4 
}

Notes to Figure 2:

Note Description
 1  This SQLJ clause executes the SELECT statement, constructs an iterator object that contains the result table for the SELECT statement, and assigns the iterator object to variable iter.
 2  The getResultSet() method accesses the ResultSet that underlies iterator iter.
 3  The JDBC getString() and getDate() methods retrieve values from the ResultSet. The next() method moves the cursor to the next row in the ResultSet.
 4  The rs.close() method closes the SQLJ iterator as well as the ResultSet.
Rules and restrictions for using JDBC ResultSets in SQLJ applications: When you write SQLJ applications that include JDBC result sets, observe the following rules and restrictions:
  • You cannot cast a ResultSet to an SQLJ iterator if the ResultSet and the iterator have different holdability attributes.

    A JDBC ResultSet or an SQLJ iterator can remain open after a COMMIT operation. For a JDBC ResultSet, this characteristic is controlled by the IBM® Data Server Driver for JDBC and SQLJ property resultSetHoldability. For an SQLJ iterator, this characteristic is controlled by the with holdability parameter of the iterator declaration. Casting a ResultSet that has holdability to an SQLJ iterator that does not, or casting a ResultSet that does not have holdability to an SQLJ iterator that does, is not supported.

  • Close the iterator or the underlying ResultSet object as soon as the program no longer uses the iterator or ResultSet, and before the end of the program.

    Closing the iterator also closes the ResultSet object. Closing the ResultSet object also closes the iterator object. In general, it is best to close the object that is used last.

  • For the IBM Data Server Driver for JDBC and SQLJ, which supports scrollable iterators and scrollable and updatable ResultSet objects, the following restrictions apply:
    • Scrollable iterators have the same restrictions as their underlying JDBC ResultSet objects.
    • You cannot cast a JDBC ResultSet that is not updatable to an SQLJ iterator that is updatable.