Wednesday, January 03, 2007

This weekend I worked on integrating the ANTs Database with StreamCruncher (SC). Well, where shall I begin? Unless I'm mistaken, their JDBC Driver is awful. To start with the Driver does not even get registered with the DriverManager. Hard to believe, but that's how it is. ANTs is accessed using the JDBC-ODBC Bridge and their ANTs Driver never appears in the StackTraces. Because their (at least ANTs 3.6 GA) Driver does not get registered, the Driver Manager falls back to the Sun implementation.

And as it is, Sun's default JDBC-ODBC Driver is littered with bugs. For the first 1 and a half days, I kept banging my head, trying to get SC to work with the Database. The Sun implementation has this weird bug where you can't access any VARCHAR columns in the ResultSet in any order other than the sequence in which they are specified in the "SELECT A, B.." clause. And, you can't access them more than once. It keeps barfing with a "No data found" error. After looking at the StackTraces, I realized that ANTs was nowhere in the picture (er, StackTrace rather). It also had other such peculiarities, which I think was partly because of ANTs underneath. Batch Insert was not working correctly, when the Table had BIGINT columns.

After all the "head banging", I had enough lumps on my head to make me stop and find a better solution. So, after some clever coding to forcibly register and instantiate the ANTs Driver, I thought I was getting somewhere. But alas, ANTs compliance with JDBC is quite broken. Their PreparedStatement.getParameterMetaData(..) returns a fixed String - "java.sql.ParameterMetaData", instead of the fully qualified Java Class name of the Parameter/Column. But then there were bigger problems. SC makes extensive use of BIGINT columns through the setLong(..) method. In ANTs, however BIGINT and INT are not of the standard sizes as compared to other Databases. If a Long value is set to a BIGINT column using the PreparedStatement's setObject(..) or setLong(..) methods, like what SC does - you either get a "Null constraint violated error" or the Query won't return any results or if it is an Insert statement, the value in the Table will be entirely different from the one you set. And, the largest negative value you can s fely use on BIGINT columns is "-2147483647" (Integer.MIN_VALUE + 1). Anything larger will produce strange results or none at all! Their BIGINT actually should store Float, as per their error message when you use the PreparedStatement.setObject(new Long("some large number")).

It was quite frustrating to hit a roadblock at every stage. I couln't re-write the whole Kernel just to make it work with ANTs. So, I chose the easier and messier way out. When the Kernel is configured to use ANTs, all the SQL Connections and PreparedStatements are intercepted and replaced with Proxies, transparently. So, whenever the Kernel or any Client code using the Connections obtained from the Kernel's Connection Pool uses the setObject(..) or setLong() on a BIGINT column, the Proxy silently replaces it with the Integer form of the number. The Kernel uses a reduced number range for ANTs - this is the only ANTs specific code I had to make. If the Integer part of the Long value does not match the original Long, because of "overflow", then the Kernel logs a Warning and continues with the Long value without replacing it with the Integer form, by assuming that it was intentional. Most DBs have java.lang.Long as their Java equivalent for the SQL BIGINT data type.

Phew! It was very painful indeed. I guess using the Java Proxy for ANTs PreparedStatements and Connections might slow down the Kernel. Too bad, but it can't be helped unless those guys at ANTs do something about their Driver.

Next on the agenda is the SOLID Boost Engine. Some initial analysis revealed that their ResultSet.getTimestamp() on a TIMESTAMP column throws an "Invalid date" error. But getDate(), getTime(), getString() on the same column returns a result. Arrghh.. I think I see a few more days of coding with the Proxy classes.

3 comments:

Anonymous said...

Ashwin:

Check out All Two Human by Darrell DEH Hamilton. Great Science Fiction. And thanks for comments about ANTs db. Why not call them up an see if they can show you why you weren't getting your task done. Maybe they have a solution or advice. Their phone number is 1-650-931-0500. Email is tech-support@ants.com. Post an update on the blog once you've done this an let us know if maybe you have better luck on integrating the ANTs database with SteamCruncher. Very interested in knowing what you find out from ANTs. Thanks!

--Marcquis

David said...

Ashwin,

Thank you for checking out ANTs and trying to integrate it with StreamCruncher. We're sorry to hear that you ran into difficulties. We'd be happy to work with you to resolve them. Our JDBC Engineer and our Technical Support team are available to work with you to isolate, identify and resolve any issues that are affecting your use of ANTs. Thank you, Marcquis for suggesting this as well.

As it turns out, we currently have a project underway to significantly enhance our JDBC driver. Over the last several years, most of our customers and prospects have used ODBC and therefore most of our attention has been focused on this, rather than the JDBC driver. Our current Type 2 JDBC driver does function correctly for some applications. We have tested it in-house and have customers use it in the field.

However, we are aware that we don't support some of the JDBC API calls at this time. Applications that attempt to use calls or functionality that we don't support at present will unfortunately run into problems. Our current plan is to significantly enhance and extend our JDBC driver and provide an initial updated version in early Q2, with ongoing additional enhancements to be added over time. Obviously, if the missing functionality is critical to a customer application we will investigate the possibility of adding it to the driver ASAP.

Prioritizing which features to implement will largely be customer-driven. Our primary consideration with be immediate customer requirements and resulting overall driver quality and usefulness. In addition to working with you on StreamCruncher specifically, we would appreciate your input into this process as a Java developer yourself.

In the meantime, if you plan to use our existing JDBC driver, we would very much like to work with you to resolve any problems that might might come up. One of our Technical Support Engineers has already downloaded StreamCruncher and has been working on reproducing the problems that you ran into. You should be hearing from him shortly.

Thank you and regards,

David

David Segleau
VP Support Services
ANTs Software inc.

Ashwin Jayaprakash said...

Thanks David. I'd be glad to help identify issues with the Driver.