Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Websphere 6.1 and JDBC-ODBC bridge - how to set it up?

40 views
Skip to first unread message

paul....@oracle.com

unread,
Apr 11, 2007, 1:49:13 PM4/11/07
to
Would appreciate any input on the following issue...

I am trying to set up a JDBC data source in Websphere 6.1 and want to be able to use an ODBC DSN. So the steps I took are:

1. Create new JDBC Provider, let's call it "ODBC_Provider", set implmentation class name to sun.jdbc.odbc.JdbcOdbcDriver, and set the driver's classpath to IBM's java's core.jar, i.e.:
C:\Program Files\IBM\WebSphere\AppServer\java\jre\lib\core.jar

I set it to the ibm java's core.jar since it had the JdbcOdbcDriver class.

2. I then created a data source in association with the "ODBC_Provider", let's call it "MY_ODBC_DataSource". I created a custom property for the data source, called 'url', with the value of 'jdbc:odbc:my_odbc_dsn'. I also created a jaas-j2c authentication entry which has username/password to the actual ODBC DSN.

3. When i tried to test the data source, i get casting error:

The test connection operation failed for data source MY_ODBC_DataSource on server server1 at node myMachineNode03 with the following exception: com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException: DSRA8101E: DataSource class cannot be used as one-phase: ClassCastException: sun.jdbc.odbc.JdbcOdbcDriver incompatible with javax.sql.ConnectionPoolDataSource.

******
4. A bit of searching around showed perhaps the property enableTwoPhase set to true may help. But When I add this custom property to the data source and tested again, I get a similar error:

The test connection operation failed for data source MY_ODBC_DataSource on server server1 at node myMachineNode03 with the following exception: com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException: DSRA8102E: DataSource class cannot be used as two-phase: ClassCastException: sun.jdbc.odbc.JdbcOdbcDriver incompatible with javax.sql.XADataSource.

******

Questions:
1. Does websphere allow for ODBC data sources, period?
2. If yes to #1, then what am I doing wrong in the setup? It seems that although the ibm-included jdk in websphere has the sun.jdbc.odbc.JdbcOdbcDriver class, i'm missing a step in setting up the JDBC provider and/or data source properly. Has anyone successfully set up jdbcodbc on websphere 6.1?

Thanks!

dj...@oah.wa.gov

unread,
Apr 13, 2007, 11:55:21 AM4/13/07
to
I can't believe you're working this same issue...

Were you able to find any solution? or confirmation of whether or not you can even use the jdbc-odbc driver as a datasource in WebSphere App Server 6?

paul....@oracle.com

unread,
Apr 13, 2007, 1:44:46 PM4/13/07
to
Unfortunately, I still haven't found anything yet. It almost seems that this was once supported in WAS 3 and 4, but somewhere along the line it dropped out of attention and now I can't find any references to it at all for WAS6 :(

dj...@oah.wa.gov

unread,
Apr 13, 2007, 2:19:11 PM4/13/07
to
I am beginning to suspect the same.

I tried all the things you tried, with the same results. A slightly different error output, notably missing the fqn of which interface was not being found for that enable2Phase business.

I am trying to connect to a legacy SQL Server 6.5 db, and unfortunately I can find no configuration that works. Here is what I have tried:

1) Source Forge's jTDS
2) JNetDirect's JSQLConnect
3) Sun's JDBC-ODBC bridge

I have gotten all 3 of these working in stand alone applications, but am unable to configure them as JNDI WebSphere JDBC Providers :(

I keep getting that error about enabling phases and what not.

I did make some progress with the Sun JDBC-ODBC idea, instead of using the driver name for the implementation class, I was able to get it to go a little further when using the sun.jdbc.odbc.ee.ConnectionPoolDataSource instead of sun.jdbc.odbc.JdbcOdbcDriver. However, I did earn myself another error, this time an infamous NullPointerException; you can see from the stack trace that it's cool because the actual driver did load a little ways before nulling out on a hashtable key lookup:

[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at java.util.Hashtable.containsKey(Hashtable.java(Compiled Code))
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at sun.jdbc.odbc.ee.ConnectionPoolFactory.obtainConnectionPool(ConnectionPoolFactory.java:65)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at sun.jdbc.odbc.ee.ConnectionPoolDataSource.getPool(ConnectionPoolDataSource.java:453)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at sun.jdbc.odbc.ee.ConnectionPoolDataSource.getPooledConnection(ConnectionPoolDataSource.java:192)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at com.ibm.ws.rsadapter.DSConfigurationHelper$1.run(DSConfigurationHelper.java:948)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at com.ibm.ws.security.util.AccessController.doPrivileged(AccessController.java(Compiled Code))
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at com.ibm.ws.rsadapter.DSConfigurationHelper.getPooledConnection(DSConfigurationHelper.java:943)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at com.ibm.ws.rsadapter.DSConfigurationHelper.testConnectionForGUI(DSConfigurationHelper.java:2073)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at sun.reflect.GeneratedMethodAccessor145.invoke(Unknown Source)
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
[4/13/07 11:16:26:172 PDT] 000000ae SystemErr R at com.ibm.ws.management.DataSourceConfigHelperMBean.testConnectionToDataSource2(DataSourceConfigHelperMBean.java:528)


aniket...@infosys.com

unread,
May 31, 2007, 12:20:07 PM5/31/07
to
Hi guys, was able to do it programatically in Websphere 6.1

1] binding on server startup:

//Bind the pyramid datasource jndi once during server startup using the sun.jdbc.odbc.ee.ConnectionPoolDataSource object
BackEndInitializer backend = BackEndInitializer.getInstance();

sun.jdbc.odbc.ee.ConnectionPoolDataSource cpds =
new sun.jdbc.odbc.ee.ConnectionPoolDataSource(backend.getPyramidJNDI());
// Provide user credentials and database name
cpds.setUser(backend.getPyramidDbUserID());
cpds.setPassword(backend.getPyramiddbPassword());

cpds.setDatabaseName(backend.getPyramidDBName());
cpds.setMinPoolSize(backend.getPyramidMinPoolsize());
cpds.setInitialPoolSize(backend.getPyramidInitialPoolsize());
cpds.setMaxPoolSize(backend.getPyramidMaxPoolsize());
cpds.setMaxIdleTime(backend.getPyramidMaxIdleTime());
cpds.setTimeoutFromPool(backend.getPyramidTimeoutFromPool());
// Maintenance interval of the pool. A maintenance thread will remove
// unwanted connections and cleanup the pool at the interval specified.
// This cannot be zero.
cpds.setMaintenanceInterval(backend.getPyramidMaintenanceInterval());
InitialContext ic = new InitialContext();

ic.rebind(backend.getPyramidJNDI(), cpds);

2] lookup for datasource and obtaining connection from datasource:

BackEndInitializer backendInitializer=BackEndInitializer.getInstance();
if(null == dbCon)
{
DataSourceFactory dsFactory = DataSourceFactory.getSingleton();
DataSource ds = dsFactory.lookupHome(backendInitializer.getPyramidJNDI());
if(null != ds){
dbCon = ds.getConnection();
}
if((null == ds) ||(null == dbCon)){
throw new DBConException(CommonConstantsIF.GETTING_DB_CONNECTION_FAILED);
}
}

dj...@oah.wa.gov

unread,
Jun 4, 2007, 11:45:53 AM6/4/07
to
Anix,

Thank you very much for the write up! :)

However, I'm confused about where to get started, I can't find any information about a "pyramid datasource" or a BackEndInitializer, can you clarify this?

//Bind the pyramid datasource jndi once during server startup using the sun.jdbc.odbc.ee.ConnectionPoolDataSource object
BackEndInitializer backend = BackEndInitializer.getInstance();

Also, I'm trying to use either jTDS or JSQLConnect, do you believe I can work your solution with either or none of these? or do I need to get the pyramid driver? Will the pyramid datasource connect with a legacy M$ SQL Server 6.5?

Thank you!!

-TreeFree

aniket...@infosys.com

unread,
Jun 5, 2007, 5:15:07 PM6/5/07
to
BackendInitializer is just a utility class that reads from a props fle and keeps all vars. names. Make sure tht ur datasource is present in the ODBC dialog window

anto...@au1.ibm.com

unread,
Jun 20, 2007, 6:49:18 PM6/20/07
to
Hi TreeFree

Did you resolve your null pointer issue. I am currently battling with the same issue.

Tony

dj...@oah.wa.gov

unread,
Jun 20, 2007, 7:25:41 PM6/20/07
to
Tony,

Here is some code I managed to get working:

[code]
try {
//
// Setup JDBC-ODBC DataSource
//
String jndiHome = "jdbc/DataSource Name"; // where to bind the ODBC connection
String user = "user";
String pass = "pass";
String odbcDS = "data source"; // original/target ODBC data source name

// Bind the datasource jndi once during server startup using the sun.jdbc.odbc.ee.ConnectionPoolDataSource object
sun.jdbc.odbc.ee.ConnectionPoolDataSource cpds =
new sun.jdbc.odbc.ee.ConnectionPoolDataSource(jndiHome);



// Provide user credentials and database name

cpds.setUser(user);
cpds.setPassword(pass);
cpds.setDatabaseName(odbcDS);

// Configure parameters
cpds.setMinPoolSize("10");
cpds.setInitialPoolSize("15");
cpds.setMaxPoolSize("20");
cpds.setMaxIdleTime("300");
cpds.setTimeoutFromPool("600");



// Maintenance interval of the pool. A maintenance thread will remove
// unwanted connections and cleanup the pool at the interval specified.
// This cannot be zero.

cpds.setMaintenanceInterval("1000");

InitialContext ic = new InitialContext();

ic.rebind(jndiHome, cpds);

//
// Sample Usage
//
DataSource ds = (DataSource)ic.lookup(jndiHome);

if(null != ds)
{
System.out.println("odbc ds is not null!");

Connection dbCon = ds.getConnection();

if(null == dbCon)
System.out.println("odbc dbConn is null");
else
{
System.out.println("odbc dbConn is not null!");

String sql = "select count(*) from TABLE";
System.out.println("sql: " + sql);
Statement stmt = dbConn.createStatement();
ResultSet rs = stmt.executeQuery(theSql);

// If there is a result, retrieve first value
String result = null;
if (rs.next()) {
result = rs.getString(1);
}
System.out.println("odbc result: " + result);

// Close resources
rs.close();
rs = null;
stmt.close();
stmt = null;
}

}
else
System.out.println("ds is null");
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
[/code]

anto...@au1.ibm.com

unread,
Jun 20, 2007, 8:50:21 PM6/20/07
to
Hi TreeFree

Thanks for the info. Much appreciated.

Where you able to get the datasource Test Connection in the WAS console to work? I am getting the null pointer exception as you described earlier when using the sun.jdbc.odbc.ee.ConnectionPoolDataSource implementation class.

[6/21/07 8:40:28:690 EST] 11b92ef DataSourceCon E DSRA8040I: Failed to connect to the DataSource. Encountered : java.lang.NullPointerException
at java.util.Hashtable.containsKey(Hashtable.java:311)
at sun.jdbc.odbc.ee.ConnectionPoolFactory.obtainConnectionPool(ConnectionPoolFactory.java:34)
at sun.jdbc.odbc.ee.ConnectionPoolDataSource.getPool(ConnectionPoolDataSource.java:422)
at sun.jdbc.odbc.ee.ConnectionPoolDataSource.getPooledConnection(ConnectionPoolDataSource.java:161)
at com.ibm.ws.rsadapter.DSConfigurationHelper$1.run(DSConfigurationHelper.java:934)
at com.ibm.ws.security.util.AccessController.doPrivileged(AccessController.java:111)
at com.ibm.ws.rsadapter.DSConfigurationHelper.getPooledConnection(DSConfigurationHelper.java:929)
at com.ibm.ws.rsadapter.DSConfigurationHelper.testConnectionForGUI(DSConfigurationHelper.java:2053)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:448)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:937)

Any help is greatly appreciated.

Regards

Tony

dj...@oah.wa.gov

unread,
Jun 21, 2007, 12:28:59 PM6/21/07
to
Nope, I put that code in an init block of a servlet that was configured to startup when the web application does. That way, the odbc data source has been bound to the jndi home at the time I need to use it later in the application; but it is not a datasource resource managed by the WAS console - i.e. it is not present in any of those configuration files, but merely present in the jndi place it needs to be which is where the console puts all the data sources it knows about.
0 new messages