Sybase JDBC Craziness

Say you're working on an enterprise class system. Developers work on Windows and Linux. Servers run Linux. Not so unusual.

Now enter Sybase SQL Anywhere. Aka Sybase ASA or iAnywhere.

First off there are 2 different JDBC drivers. JConnect (jconn3) and the iAnywhere JDBC driver (jodbc). It turns out that only the iAnwhere driver actually works with the high availability option (although not documented).

Also it turns out that the iAnywhere driver is really an ODBC bridge and you have to specify another driver in the JDBC URL.

While a little confusing at first due to the lack of documentation eventually you can dig up an example.

jdbc:ianywhere:driver=SQL Anywhere 10;dbn=mydatabase;eng=myserver;

Everything works and you move on with life.

Except that eventually you want to deploy your new code to the server. BAM nothing works. All sorts of errors about no suitable driver found.

After thrashing around for a few days you discover that the JDBC URL must be different on Linux! (this is the only page on the Internet that specifies this).

jdbc:ianywhere:driver=libdbodbc10.so;dbn=mydatabase;eng=myserver;

Of course your application now works on Linux, but not on Windows.

Now if I were writing my own code that needed to talk to the database there wouldn't be much problem as I can use one of several techniques for figuring out which driver I should be using.

However, this URL used to configure some enterprise reporting tool which uses that same URL whether doing local report development or running from the server.

So now I have 3 options.

  1. Install the reporting server on every developers workstation.
  2. Stand up a Windows version of the reporting server.
  3. Create ODBC DSNs on all affected systems.

While option #1 is enticing (I like developers to have a local copy of all dependencies if at all feasible). Feasibility plays into the picture here because of license costs.

Option #2 is certainly doable, but I am not a big fan of adding the overhead of administering another server and keeping it in sync with all the others.

Options #3 is simple and works well. However, DSNs represent another thing that needs to be set up on every developer and qa system. This also breaks my rule of being able to check out the source tree and go, even on a new computer (for reasons of continuous integration and easy new team member set up).

Ultimately we will go with #3 because it is low cost in dollars, and low cost in time (we'll write an Ant target to do the DSN setup).

Now wasn't that easy? It only took 3 days to work through in real time.

4 Comments

  • >JConnect does actually work with the SQL Anywhere high availability option.

    While I believe you, the support group doesn't know how to make it happen. Maybe you could provide a URL that explains how to do this.

  • Its basically brute force.
    url1 = primary server url location
    url2 = mirror server url location
    if connect using url1 fails, then use url2

    Not pretty, but it works.

    Incidentally, some of my colleagues here are surprised that you had to use different URLs on Windows and Linux. As of version 10 you should be able to use the same URL. I'll see if I can reproduce your problem so we can track down what is going on.

  • Noa,

    Forgive me for stating the obvious, but the JVM can't find the libdbodbc9.so. However, I will also say we had some challenges finding a location for libdbodbc9.so that the system would find. A little experimentation is required.

  • Noa,

    Also we ended up using the ODBC version of the connection sting:

    jdbc:ianywhere:driver=SQL Anywhere 10;DSN=SQL Anywhere 10 Demo

    Where "SQL Anywhere 10 Demo" is a ODBC DSN.

Comments have been disabled for this content.