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!
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?
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)
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);
}
}
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
Did you resolve your null pointer issue. I am currently battling with the same issue.
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]
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