WMB JDBC Connectivity with Oracle
Introduction
Understanding how the WMB JDBC is critical to handle the connectivity with database. In this article, I have tried to explain the basis concept about JDBC in WMB and how it should be configured in different scenario.
Understanding JDBC Connectivity
1. Setting up a JDBC provider for type 4 connections
Use the mqsi
This is an example of JDBC provider properties.
JDBCProviders: DBName
Properties:
| |
|
Run the mqsirepo rtpr oper ties comm and to view the list of available JDBCProvider services.Substitute the name of your broker in place of broker_name.
mqsi
command to display the supplied Oracle definition:
mqsi
A JDBCProvider service has the following properties:
conn
jdbc
Example of creating JDBC provider with required parameters :
mqsi
2. Securing a JDBC type 4 connection
Set up security for the JDBC connection if required by the database provider otherwise you can pass the credentials in
- Identify the user ID that you want to associate with the JDBCconnection, or create a user ID with a password, following the appropriate instructions for your operating system and database.
- Run the mqsi
setd bpar msco mman d to associate the user ID and password with the security identity that is associated with the database that you will access using the JDBCProvider configurable service.
The following values and order of preference are used by the broker for the JDBC connection:
- The user ID and password that you have set for the specific database, by using the mqsisetdbparms and specifying the database in the -n parameter.
Use the following command format:
mqsisetdbparms broker_name -n security_identity -u userID -p password
3. Interacting with databases by using the
The broker supports type 4 drivers, but does not supply them means whatever database you are using, You must obtain these drivers from those database vendor.
Use the broker JDBC Provider to benefit from the following advantages:
- Use broker configuration facilities to define the connection, and to provide optional security, in preference to coding these actions.
- Configure the broker and the databases to coordinate access and updates with other resources that you access from your message flows, except when the broker is running on.
- Use the broker Java API
getJ DBCT ype4 Conn ecti on t o initiate the connection, and then perform SQL operations by using the standard JDBC APIs. The broker manages the connections, thread affinity, connection pooling, and life cycle in background so you dont need to worry about for all these handling. - By default connection pooling is zero(0) and you can configure it according to your database.
- If a connection is idle for approximately 1 minute, or if the message flow completes, the broker closes the connection.
When you have configured the JDBCProvider, you can establish a JDBC type 4 connection to the database by using th
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 {
// Clear the outMessage
outMessage.clearMessage();
}
}
}