Sometimes when you’re in a map, you need a value from a database.
Unfortunately, non-SQL maps cannot connect to a database.
Fortunately, you can use an “object” variable, to call a user exit to connect instead.
Unfortunately, customer support does not support user exits.
Fortunately, I have an example that you can use on your own.
This example is for GIS/SI only. They use .jar files, for their user exits, where other products use other options.
I’ll give you the quick and dirty information first, for those that just want to grab it and go.
Here’s a link to the .jar file (I realize it’s redundant to zip it up, but it needs to be a .zip to store on developer works)
https://www.ibm.com/developerworks/community/blogs/mappingandtranslation/resource/SqlAccess.zip
You’ll want to unzip the file, and place the .jar on the server.
Stop the services for the instance, and from a command line, install using the install3rdParty(.cmd for windows or .sh for unix).
Change to the <install_dir>\bin directory and do the following command (change .cmd to .sh for unix):
install3rdParty.cmd IBM_DBAccess 1_0 -j <path-to-jar-file>
The jar file can be placed in the bin directory or a directory of your choice. It may be removed once the install is complete. You can verify the install by viewing existence of
<install_dir>/jar/IBM_DBAccess/1_0/SCI_MapUserExit.jar
I won’t go into the details behind creating JDBC pools here, but this user exit will need a pool, to connect to the database. It’s the exact same kind of pool you’ll use for a SQL map, or (lightweight)JDBC adapter.
Once you have the pool setup, restart your services.
There’s not much to the extended rules, to execute the user exit.
1. Declare an object to call the user exit
2. Declare strings to hold the SQL statement and result
3. Initialize the variables
4. Create a string for your SQL statement
5. Execute the SQL statement
Ex:
//declare
object dbutil;
string[100] return_value, sql_string;
//initialize
dbutil = new("com.ibm.mapping.dbaccess.SqlAccess");
return_value = “”;
sql_string = “”;
//create
sql_string = "select FIELD from TABLE where OTHER_FIELD = 'VALUE'";
//execute
return_value = dbutil.executeSQL(sql_string,"Pool_Name");
The initialization line for the object needs to be exactly as shown here.
The sql_string is just a string variable, and can be created with hardcoded values as in this example, or can be created using other string variables or string fields.
The Pool_Name in the execute line, should be replaced with the actual JDBC pool name.
The return_value could also be replaced with #string_field.
That should be all you need, to take it and run with it.
If you want to execute an update/insert/delete statement, it’s the exact same as above.
The only difference is that return_value will contain the number of rows affected.
Now here’s more information than you probably want to know…
The .java used to create the .jar contains the following:
package com.ibm.mapping.dbaccess;
/*
* Created on September 13, 2013
*/
import java.sql.*; // import java.net.*;
import com.sterlingcommerce.woodstock.util.frame.Manager;
import com.sterlingcommerce.woodstock.util.frame.jdbc.JDBCService;
/* JDBCService is listed as deprecated but per support it is still in use */
/*******************************************************************************
* Class to get connection and execute query in the DB. <br>
* <P> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* <br>
* Note that the select method in this class retrieves only one String
* column, and that <br>
* the method only works correctly when the sql returns only one row in the
* result <br>
* set. If multiple rows are retrieved, the first column of the LAST row is the
* value that will <br>
* be passed back to the calling method. <br> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*
* @author MFiorillo
* @version 20130913
******************************************************************************/
/*******************************************************************************
*
* Constructor is used to create the connection to the DB.
*
******************************************************************************/
public class SqlAccess {
Connection con;
/** DEBUG = true outputs selected text to noapp.log.
* Output statements are prefixed with "%#%-" to facilitate searching.
* When SCC is monitoring or when SOA messages are incoming, they clutter noapp.log. */
public static boolean DEBUG = false;
/***************************************************************************
* Constructor
************************************************************************* * /
SqlAccess() {
if (DEBUG) {
System.out.println("%#%-in constructor SqlAccess -- ");
}
// DEBUG = true;
}
*** no constructor used *** */
/***************************************************************************
* Execute Query with DB Connection. This Query only returns a string . So
* make sure the return value of this is a string.
**************************************************************************/
/**
* Executes SQL statement and returns ONE String value from first column of
* result row. <br>
* Limitation -- returned value is from LAST row in result set. SQL should
* be constructed <br>
* so that only one result row is returned, otherwise undesired value may be
* returned.
*/
/* sets debug on. Third parameter can be any String, only the presence
* of the third parameter is needed to trigger DEBUG ON
*/
public String executeSQL(String theSqlStmt, String theDbPool, String debug) {
DEBUG = true;
String rtn = executeSQL(theSqlStmt, theDbPool);
DEBUG = false;
return rtn;
}
public String executeSQL(String mySqlStmt, String myDbPool) {
if (DEBUG) {
System.out.println("%#%-Inside executeSQL");
}
if (con == null) {
if (DEBUG) {
System.out.println("%#%-in constructor SqlAccess -- getting connection");
}
con = getConnection(myDbPool);
}
/* if this is SELECT query, use the executeSelect() method, otherwise use executeAction() */
String teststatement = mySqlStmt.toLowerCase();
if (teststatement.substring(0,6).equals("select")){
return executeSelect(mySqlStmt, myDbPool);
}
else {
return executeAction(mySqlStmt, myDbPool);
}
}
/* executeAction() is used for update, insert, delete, and returns String representation
* of number of rows affected
*/
private String executeAction(String sqlStmt, String dbPool) {
int affected = 0;
DEBUG = true;
if (DEBUG) {
System.out.println("%#%-Inside executeAction");
}
try {
if (DEBUG) {
System.out.println("%#%-before con.createstatement");
System.out.println("%#%-ACTION-SQL = " + sqlStmt);
if (con == null) {
System.out.println("%#%-con is Null");
} else {
System.out.println("%#%-con was not null");
}
}
Statement stmt = con.createStatement();
if (DEBUG) {
System.out.println("%#%-stmt = con.createStatement() line 119");
}
affected = stmt.executeUpdate(sqlStmt);
if (DEBUG) {
System.out.println("%#%-Closing result set");
}
stmt.close();
if (DEBUG) {
System.out.println("%#%-Statement closed at line 128");
}
return Integer.toString(affected);
} catch (Exception ex) {
System.out.println("%#%-Exception in sqlstatement" + ex.getMessage());
ex.printStackTrace();
return "";
}
}
/* executeSelect() is used for SELECT queries only, and returns only
* the FIRST column of the LAST row in the result set
*/
private String executeSelect(String sqlStmt, String dbPool){
String value = "";
if (DEBUG) {
System.out.println("%#%-Inside executeSelect");
}
try {
if (DEBUG) {
System.out.println("%#%-before con.createstatement");
System.out.println("%#%-SELECT-SQL = " + sqlStmt);
if (con == null) {
System.out.println("%#%-con is Null");
} else {
System.out.println("%#%-con was not null");
}
}
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sqlStmt);
if (DEBUG) {
System.out.println("%#%-stmt = con.createStatement() line 159"
+ "\nrs = stmt.executeSelect() line 160");
}
/*
* note that lookup should return one result row. If lookup returns
* multiple rows, the while loop below will read all, and the final
* value of variable "value" will represent the LAST row in the
* result set
*/
while (rs.next()) {
value = rs.getString(1).trim();
if (DEBUG) {
System.out.println("%#%-rs.getString(1) Value: " + value);
if ((value.length() == '0') || (value == "")) {
System.out.println("%#%-I did not get anything from DB");
}
}
}
rs.close();
stmt.close();
if (DEBUG) {
System.out.println("%#%-closing rs and stmt lines 182-183");
}
return value;
} catch (Exception ex) {
if (DEBUG) {
System.out.println("%#%-Null value from column in result set -- returning zero-length string");
System.out.println("%#%-Exception in sqlstatement "
+ ex.getMessage());
ex.printStackTrace();
}
/* if we see null return, we return "" */
return "";
}
}
/***************************************************************************
* Connection close method to be called on-end-of-session in map.
**************************************************************************/
public void closeConnect() {
try {
if (DEBUG) {
System.out.println("%#%-Connection con being closed");
}
if (con != null)
con.close();
} catch (SQLException e) {
System.out.println("%#%-Exception in sqlstatement" + e.getMessage());
e.printStackTrace();
}
}
/***************************************************************************
* Execute Query with DB Connection.
**************************************************************************/
public static String getProperties(String propertyFile,
String propertyName) {
String result = null;
try {
if (DEBUG) {
System.out.println("%#%-1 - propertyFile = " + propertyFile);
System.out.println("%#%-2 - propertyName = " + propertyName);
}
result = Manager.getProperty(propertyFile, propertyName, true);
if (DEBUG) {
System.out.println("%#%-3 - result = " + result);
}
return result;
} catch (Exception e) {
System.out.println("%#%-Exception in getProperties" + e.getMessage());
e.printStackTrace();
return "ERROR";
}
}
/***************************************************************************
*
* CONNECTIONS: Two connection methods exist; one is a connection that uses
* SBI connection pools; the other uses a standalone connection for testing.
* It uses the Oracle jdbc driver for use from a Windows remote platform
* (using Eclipse, for example).
*
* The class variable connectionMethod controls which connection attempt
* executes. This is an integer that should be set to "0" (zero) for normal
* processing when installed in SBI. For testing from Eclipse, should be set
* to "1".
*
* The class variable DB_POOL is set in the constructor method. This should
* be the name of the GIS pool that contains the custom tables used by this
* collection of classes.
*
* The class variable currentCatalog defines the library containing the
* custom tables used. To access the production tables, this should be set
* to "edidev".
*
**************************************************************************/
/* **************************************************************** */
/* ************** Database Connection method ****************** */
/* ********** calls child methods indicated by flag ************* */
/* ****** connectionMethod to get appropriate connection ******** */
/* **************************************************************** */
static Connection getConnection(String dbPool) {
Connection con = null;
try {
con = JDBCService.getConnection(dbPool);
} catch (SQLException e) {
System.out.println("%#%-" + e.getMessage());
}
return con;
}
}
You can see there are some debug statements within there.
If you add a 3rd parameter (basically any string) on your execute command:
return_value = dbutil.executeSQL(sql_string,"Pool_Name",”X”);
Then the debug messages will be written to the noapp log. Just make sure you remove it later, or you’ll write to your log every time the map runs.
The entries in the log start with “%#%-“ so you can do a search to find them easier.
You’ll also notice some methods to get property file values. That will be another blog entry, to discuss how to use those.
If you are having problems getting it to work correctly, I would recommend using a LWJDBC adapter to test the pool and sql statement. Here is an example of BPML for a simple LWJDBC adapter:
<process name="LWJDBC">
<sequence>
<operation name="Lightweight JDBC Adapter">
<participant name="LightweightJDBCAdapterQuery"/>
<output message="LightweightJDBCAdapterTypeInputMessage">
<assign to="pool">Pool Name</assign>
<assign to="query_type">SELECT</assign>
<assign to="result_name">Header</assign>
<assign to="row_name">row</assign>
<assign to="sql">select query</assign>
<assign to="." from="*"></assign>
</output>
<input message="inmsg">
<assign to="." from="*"></assign>
</input>
</operation>
</sequence>
</process>
Replace the "Pool Name" and "select query" with the corresponding Pool Name and query to run
As I mentioned at the beginning, customer support does not support custom user exits, so use this at your own risk. If you need additional help, you can always talk to sales about getting a consultant or ask on some of the user forums or email groups.
As always, any and all comments are welcome.
Feel free to request any topics or ideas as well.
Thanks for reading!
Pat Frey – IBM Support
).