Accessing IMS data with the SQLInteractionSpec class

Use the SQLInteractionSpec class to retrieve, insert, update, and delete data from an IMS database using SQL queries with the IMS Universal Database resource adapter. The IMS Universal Database resource adapter supports the same SQL statement syntax and usage as the IMS Universal JDBC driver and has the same restrictions.

Before your application component can retrieve, insert, update, or delete data from an IMS database, you need to obtain a javax.resource.cci.Connection instance for the physical connection to the database.

To retrieve, insert, update, or delete data using the SQLInteractionSpec class:

Procedure

In your application component, create a new javax.resource.cci.Interaction instance using the Connection.createInteraction method.
For example, in the following code sample, con is a javax.resource.cci.Connection instance for an IMS database:
```
Interaction ix = con.createInteraction();
```
Create a new com.ibm.ims.db.cci.SQLInteractionSpec instance.
```
SQLInteractionSpec iSpec = new SQLInteractionSpec();
```
Set the SQL query string using the SQLInteractionSpec.setSQL method.
In the query, you can specify the qualification column values in the WHERE clause with a ? parameter marker, meaning that the values will provided later (similar to a PreparedStatement in JDBC).
- The following example shows how to specify the SELECT statement without using parameter markers, where iSpec is an instance of SQLInteractionSpec:
```
iSpec.setQuery("SELECT PATIENT.PATNAME, ILLNESS.ILLNAME "+
     "FROM pcb01.HOSPITAL, pcb01.PATIENT, pcb01.ILLNESS " + 
     "WHERE HOSPITAL.HOSPNAME='SANTA TERESA'");
```
- The following example shows how to perform the SELECT statement with parameter markers, where iSpec is an instance of SQLInteractionSpec:
```
iSpec.setQuery("SELECT PATIENT.PatName, WARD.WardName "+
     "FROM pcb01.HOSPITAL, pcb01.PATIENT, pcb01.WARD " + 
     "WHERE HOSPITAL.HospName=? AND WARD.DocCount>?");
```
Create a javax.resource.cci.RecordFactory instance using the ConnectionFactory.getRecordFactory method.
Creating a RecordFactory is not needed for SQL queries without parameter markers.
Create a javax.resource.cci.IndexedRecord instance using the RecordFactory.getIndexedRecord method. Pass the name of the record you want to create as an argument to this method.
For example, in the following code sample, rf is a javax.resource.cci.RecordFactory instance:
```
IndexedRecord input = rf.createIndexedRecord("myPatientRecord");
```
Creating a IndexedRecord is not needed for SQL queries without parameter markers.
If your query string uses parameter markers, use the IndexedRecord.add method to qualify the WHERE clause.
For example, using the same query string with parameter markers as in step 5, where input is an instance of IndexedRecord:
```
input.add(1, "Santa Teresa"); //HospName value is "Santa Teresa"
input.add(2, 5);              //DocCount value is greater than 5
```
Execute the query by calling the Interaction.execute method. Pass the SQLInteractionSpec object and the IndexedRecord object as arguments.
If your SQL query does not use parameter markers, the second argument in the execute method call is ignored (you can pass in a null). If the query is successful, the method returns a Record object with the query results. You can cast the Record instance to javax.resource.cci.ResultSet and process the results as tabular data in your application component. For example, in the following code sample, results is a javax.resource.cci.ResultSet instance, ix is a javax.resource.cci.Interaction instance, iSpec is a com.ibm.ims.db.cci.SQLInteractionSpec instance, and input is a javax.resource.cci.IndexedRecord instance:
```
results = (ResultSet)ix.execute(iSpec, input);
```

Example code for IMS data operations using the SQLInteractionSpec class

The following code example shows how to use the SQLInteractionSpec class to retrieve patient names from PATIENT records.

package client;

import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.resource.ResourceException;
import javax.resource.cci.Connection;
import javax.resource.cci.ConnectionFactory;
import javax.resource.cci.Interaction;
import javax.resource.cci.ResultSet;
import javax.transaction.UserTransaction;
import com.ibm.ims.db.cci.SQLInteractionSpec;

/**
 * Bean implementation class for Enterprise Bean: StatefulBeanManaged
 */
public class BeanManagedSampleSQLBean implements javax.ejb.SessionBean {

    private javax.ejb.SessionContext mySessionCtx;

    public void execute() throws Exception {
        InitialContext ic = new InitialContext();
        ConnectionFactory cf = 
					(ConnectionFactory) ic.lookup("java:comp/env/MyMCF");
        Connection conn = null;
        UserTransaction ut = null;
        
        try {
            ut = this.mySessionCtx.getUserTransaction();
            ut.begin();

            conn = cf.getConnection();
            Interaction ix = conn.createInteraction();
            SQLInteractionSpec iSpec = new SQLInteractionSpec();

            // This query will return the WARDNAME, PATCOUNT, DOCCOUNT, 
            // and NURCOUNT fields for the WARD with WARDNO = 51
            iSpec.setSQL("SELECT WARDNAME, PATCOUNT, DOCCOUNT, " +
						"NURCOUNT FROM PCB09.WARD WHERE WARDNO='51'");

            ResultSet rs = (ResultSet) ix.execute(iSpec, null);

            while (rs.next()) {
                System.out.println(rs.getString("WARDNAME"));
                System.out.println(rs.getString("PATCOUNT"));
                System.out.println(rs.getString("DOCCOUNT"));
                System.out.println(rs.getString("NURCOUNT"));
            }

            rs.close();
            ix.close();
            ut.commit();
            conn.close();
        } catch (ResourceException e) {
            ut.rollback();
            conn.close();
        } catch (SQLException e) {
            ut.rollback();
            conn.close();
        }
    }

    /**
     * getSessionContext
     */
    public javax.ejb.SessionContext getSessionContext() {
        return mySessionCtx;
    }

    /**
     * setSessionContext
     */
    public void setSessionContext(javax.ejb.SessionContext ctx) {
        mySessionCtx = ctx;
    }

    /**
     * ejbCreate
     */
    public void ejbCreate() throws javax.ejb.CreateException {
    }

    /**
     * ejbActivate
     */
    public void ejbActivate() {
    }

    /**
     * ejbPassivate
     */
    public void ejbPassivate() {
    }

    /**
     * ejbRemove
     */
    public void ejbRemove() {
    }
}