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:

Spring Tool Suite

I have spent quite some time banging my head trying to get the first sample project worked out using STS (I was using 2.7.1.RELEASE).

While I was trying to create a Spring Template Project, I kept getting "Failed to read artifact descriptor for org.springframework:spring-context:jar:3.0.5.RELEASE". The solution was to delete the ".m2" in "C:\Documents and Settings\xyz\" to start with a clean slate for Maven repo. The reason is that a Spring Template Project is closely tied with Maven 2. Any un-resolved dependency will cause the project to fail.

Now think of it, what happened was that I started when was in my office and I needed to go through proxy server to get all dependencies downloaded. In my first attempt, I did not have proxy set up right. Somehow, partial dependencies were downloaded. Then after I got the proxy set up right, for some reason, the Spring Template Project thought all dependencies were already there and didn't bother to download the missing ones. No other error was given but the above nasty line. After tried a clean installation of the STS and created a Template Project successfully at my home PC, I did a comparison with my machine at work and resolved the mystery.

Groovy and Grails

In the past three month or so, I have been working very intensively and extensively on a project using Grails. The project looked small however full of exciting features of Grails. I have to say Grails is a great framework for web application development. We have been talking about picking framework for years and none of them is really met my standard of a "framework" until I started to know Grails. This is it! I was amazed by the simplicity and productivity of it. In the coming posts, I will start sharing some of the implementation in various areas...