Spring Tool Suite, DataSource and Sybase

I have spent quite some time in the last two days banging my head against wall on a regular web application development that involved Spring, iBatis, JDBC connection pool and a Sybase DB.

The problem couldn't be simpler as I had done this before. However, with Spring Tool Suite. It became a major hassle.

First of all, with every configurations set up all right and jconn2 or jconn3 in the WEB-INF/lib, I defined a method doTest() to to call an Oracle stored procedure from iBatis. I got results as expected.

Right after I changed to point to the Sybase, I got the below error everything single time: I tried multiple builds of the jConn2 and jConn3, all with the same error message except the string "jdbc2" became "jdbc3"

...
at com.sybase.jdbc2.jdbc.ProtocolContext.(ProtocolContext.java:83)
at com.sybase.jdbc2.timedio.StreamContext.(StreamContext.java:29)
at com.sybase.jdbc2.tds.TdsProtocolContext.(TdsProtocolContext.java:87)
at com.sybase.jdbc2.tds.Tds.getProtocolContext(Tds.java:2977)
at com.sybase.jdbc2.jdbc.SybConnection.initProtocol(SybConnection.java:1939)
at com.sybase.jdbc2.jdbc.SybConnection.prepareCall(SybConnection.java:961)
at com.sybase.jdbc2.jdbc.MdaManager.loadMetaData(MdaManager.java:409)
at com.sybase.jdbc2.jdbc.MdaManager.(MdaManager.java:146)
at com.sybase.jdbc2.jdbc.MdaManager.(MdaManager.java:131)
at com.sybase.jdbc2.jdbc.SybConnection.checkMDA(SybConnection.java:2249)
at com.sybase.jdbc2.jdbc.SybConnection.checkDBMD(SybConnection.java:2265)
at com.sybase.jdbc2.jdbc.SybConnection.getMetaData(SybConnection.java:1237)
at com.springsource.insight.plugin.jdbc.JdbcPreparedStatementOperationCollectionAspect.createOperationForStatement(JdbcPreparedStatementOperationCollectionAspect.aj:125)
at com.springsource.insight.plugin.jdbc.JdbcPreparedStatementOperationCollectionAspect.ajc$afterReturning$com_springsource_insight_plugin_jdbc_JdbcPreparedStatementOperationCollectionAspect$1$81118432(JdbcPreparedStatementOperationCollectionAspect.aj:63)
at com.sybase.jdbc2.jdbc.SybConnection.prepareCall(SybConnection.java:963)
at com.sybase.jdbc2.jdbc.MdaManager.loadMetaData(MdaManager.java:409)
at com.sybase.jdbc2.jdbc.MdaManager.(MdaManager.java:146)
...

I started debugging by first changing the doTest() to a simpler version to just call a select statement as follows:

public void doTest(){
DataSource dataSource = getSqlMapClientTemplate().getDataSource();
try{
Connection conn=dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM dbo.rep_test2");
while (rs.next()) {
String col1 = rs.getString("col1");
String col2 = rs.getString("col2");
System.out.println("col1 = "+col1);
System.out.println("col2 = "+col2);
}
}catch(SQLException e){
e.printStackTrace();
}

It failed.

My initial thought was that something was not quite right in my connection string. I tested it with a hard coded sample in the main():

import org.apache.commons.dbcp.BasicDataSource;

public static void main(...){
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.sybase.jdbc2.jdbc.SybDriver");
dataSource.setUsername("UserName");
dataSource.setPassword("PassWd");
dataSource.setMaxActive(1);
dataSource.setMaxIdle(1);
dataSource.setUrl("jdbc:sybase:Tds:someHost:4100?ServiceName=theDB");
Connection conn = dataSource.getConnection();
System.out.println("conn String: " + conn.toString());
}

Everthing worked. My connection string is correct! Then what was the problem??

STS came with an enbeded tcServer (Spring's Tomcat). That is the only difference. A main() run did not need to go through tcServer. I then tried to zip up my web app and deploy it to a local Tomcat 6. Everything worked!

What the difference that made tcServer and Tomcat behave so differently? After some fruitless investigations, the finding was that the use of org.apache.tomcat.dbcp.dbcp.BasicDataSource class. While the local Tomcat allowed me using the org.apache.commons.dbcp.BasicDataSource and a jdbc driver resides in my web app's WEB-INF/lib, the STS's embedded tcServer used the Tomcat's wrapped version - org.apache.tomcat.dbcp.dbcp.BasicDataSource and needed me to put the jdbc driver into tcServer's lib.

Same held true when I used the jndi, my resource should use Tomcat's wrapper, as following: