Access databases from Java™ code included in the JavaCompute node.
The databases that you access using MbSQLStatement can participate in globally coordinated transactions. In all other cases, database access cannot be globally coordinated.
The MbSQLStatement class provides full transactional database access using ESQL and ODBC. The broker resource manager coordinates database access when using MbSQLStatement. Global coordination is provided by WebSphere® MQ on distributed platforms, 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. The broker allows your JDBC connection code to invoke 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.
If you choose this method to access databases, the broker does not provide any support for managing the transactions; your code must manage the local commit and rollback of database changes. Your code must also manage the connection life cycle, 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 any interference with connections made by the broker. 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.