Interacting with databases using the JavaCompute node

Access databases from Java™ code included in the JavaCompute node.

Choose from the following options for database interaction:

The databases that you access using MbSQLStatement can participate in globally coordinated transactions. In all other cases, database access cannot be globally coordinated.

MbSQLStatement

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.

Create instances of the MbSQLStatement class using the createSQLStatement() method of MbNode, passing to the method the ODBC data source, a broker EQSL statement, and, optionally, the transaction mode.
  • Calling select() on this object returns the results of the query.
  • Calling execute() on this object executes a query where no results are returned, such as updating a table.
The following Java code shows how to access a database using MbSQLStatement:
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); 

JDBC API

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.

SQLJ

SQLJ is a Java extension that you can use to embed static SQL statements within Java code. Create SQLJ files using the workbench. The broker resource manager does not coordinate database access when using SQLJ.
  1. Enable SQLJ capability in the workbench:
    1. Switch to the Broker Application Development perspective.
    2. Select Window > Preferences.
    3. Expand Workbench.
    4. Select Capabilities.
    5. Expand Database Developer.
    6. Select SQLJ Development.
    7. Click OK.
  2. Create a new SQLJ file within a Java project:
    1. Right-click the Java project in which you want to create the file.
    2. Select New > Other.
    3. Expand Data.
    4. Expand SQLJ.
    5. Select SQLJ File.
    6. Click Next.
    7. Follow the directions given by the New SQLJ File wizard to generate the SQLJ file.
You can now reference the class in this SQLJ file from a JavaCompute node class in this project or in another referenced project.
Related concepts
Project references
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2009Copyright IBM Corporation 1999, 2009.
Last updated : 2009-01-07 15:20:38

ac30494_