Interacting with databases by using the JavaCompute node

You can establish JDBC type 4 connections to interact with databases from your JavaCompute nodes. The integration node supports type 4 drivers, but does not supply them. You must obtain these drivers from your database vendor; for information about supported databases, see IBM® Integration Bus system requirements.

If the integration node is running on a distributed system, you can configure the databases and the connections to be coordinated with other resource activity. Global coordination on distributed systems is provided by WebSphere® MQ, and can include interactions with local or remote databases, including remote databases that are defined on z/OS systems. If you establish a JDBC type 4 connection to a database from an integration node that is running on z/OS, coordination is not provided. For information about setting up connections and coordination, see Enabling JDBC connections to the databases.

Before you can include this function in the code that you write for the node, you must configure the required environment. Decide whether your database requires security of access, and whether you want the database updates to participate in globally coordinated transactions. For the required and optional tasks, see Enabling JDBC connections to the databases.

When you have configured the JDBCProvider, you can establish a JDBC type 4 connection to the database by using the getJDBCType4Connection call on the MbNode interface. The following code provides an example of its use:

public class MyJavaCompute extends MbJavaComputeNode {
    public void evaluate(MbMessageAssembly inAssembly) throws MbException {
      MbOutputTerminal out = getOutputTerminal("out");
      MbMessage inMessage = inAssembly.getMessage();

      // create new message
      MbMessage outMessage = new MbMessage(inMessage);
      MbMessageAssembly outAssembly = new MbMessageAssembly(inAssembly,outMessage);

      try {
        // Obtain a java.sql.Connection using a JDBC Type4 datasource - in this example for a 
        // JDBC broker configurable service called "MyDB2"  

        Connection conn = getJDBCType4Connection("MyDB2",
                     JDBC_TransactionType.MB_TRANSACTION_AUTO);

        // Example of using the Connection to create a java.sql.Statement  
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
        ResultSet srs0 = stmt.executeQuery("SELECT NAME, CITY FROM MySchema.MyTable");    

        stmt.executeUpdate("UPDATE MySchema.MyTable SET CITY = \"Springfield\" WHERE Name = \"Bart\"");
        .
        // Perform other database updates   
        . 

      } catch (SQLException sqx ){
        sqx.printStackTrace();
      } finally {
        // Close the artefacts
        if (stmt != null) stmt.close();
        if (srs0 != null) srs0.close()

        // Clear the outMessage
        outMessage.clearMessage();
      }     
    }  
  }

In this example:

• MyDB2 is the name of the JDBCProvider configurable service. Use the name of the service that you have created to connect to your database.
• MySchema is the name of the database schema (not the name of the database).
• MB_TRANSACTION_AUTO defines the level of transaction coordination that is required by the node. Only this value is supported, and indicates that the coordination in the node is inherited from the coordination configured at message flow level.

To indicate a failure (and roll back a transaction), issue an exception from the JavaCompute node and the integration node will handle the rollback.

The primary use of the getJDBCType4Connection call is in the evaluate() method of a JavaCompute node, where it is used to obtain a JDBC connection that is managed by the integration node.

When using the getJDBCType4Connection in the onitialize() method, ensure that any exceptions that might occur in this processing are handled. Any unhanded exception causes the deployment or startup of the message flow to fail. For more information, see JavaCompute node.

The MbSQLStatement class provides full transactional database access by using ESQL and ODBC. The integration node resource manager coordinates database access when using MbSQLStatement. Global coordination is provided by WebSphere MQ on distributed systems, and by RRS on z/OS. For information about how to set up the ODBC resources that are required, see Enabling ODBC connections to the databases.

MbMessage newMsg = new MbMessage(assembly.getMessage());
MbMessageAssembly newAssembly = new MbMessageAssembly(assembly, newMsg);

String table = "dbTable";

MbSQLStatement state = createSQLStatement( "dbName", 
	"SET OutputRoot.XMLNS.integer[] = PASSTHRU('SELECT * FROM " + table + "');" );

state.setThrowExceptionOnDatabaseError(false);
state.setTreatWarningsAsErrors(true);
state.select( assembly, newAssembly );

int sqlCode = state.getSQLCode(); 
if(sqlCode != 0)
{
	// Do error handling here 
}

getOutputTerminal("out").propagate(assembly); 

You can access standard Java APIs in the code that you write for your JavaCompute nodes, including JDBC calls. You can therefore use JDBC APIs to connect to a database, write to or read from the database, and disconnect from the database. On operating systems other than z/OS, the integration node supports your JDBC connection code calling both type 2 and type 4 JDBC drivers in this environment, but does not supply them. You must obtain these drivers from your database vendor. On z/OS, type 2 drivers are not supported.

If you choose this method to access databases, the integration node does not support managing the transactions; your code must manage the local commit and rollback of database changes. Your code must also manage the connection lifecycle, connection thread affinity, and connection pooling. You must also monitor the access to databases when you use this technique to ensure that these connections do not cause interference with connections made by the integration node. In particular, be aware that type 2 drivers bridge to an ODBC connection that might be in use in message flows that access databases from ESQL.