Interacting with databases by using the JavaCompute node

The MbSQLStatement class provides full transactional database access by using ESQL and ODBC. Global coordination is provided by IBM® MQ on distributed systems, and by RRS on z/OS®.

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. Therefore, you can 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, your JDBC connection code can call both type 2 and type 4 JDBC drivers in this environment, but they are not supplied. 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, managing the transactions is not supported; 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 that are made by the integration server. 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.