Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

Rows and Columns


SQL Anywhere perspective on technology

header image

Connecting To SQL Anywhere Using JDBC

October 15th, 2009 · 13 Comments

I have heard from customers that connecting to SQL Anywhere over JDBC can be difficult at times. In my investigations of this, I have found that this is almost always due to confusion over the classname to use to register the JDBC driver, and the URL’s to use to actually connect to the database. In stepping back, I can see how people might easily get confused based on the history of the JDBC driver. Here is my attempt to clarify things by following the history of the driver, starting with SQL Anywhere version 9.

Before I go into detail on the history of the SQL Anywhere JDBC driver, here is a table which explains classpath settings, jar files required, driver name URLs and sample connection URLs.

SQL Anywhere Version JDBC jar file to include in classpath Driver classname Connection URL
9.0.2 %ASA90%\java\jodbc.jar ianywhere.ml.jdbcodbc.IDriver jdbc:odbc:Driver=Adaptive Server Anywhere 9.0;UID=DBA;PWD=sql;eng=demo
10.0.0 %SQLANY10%\java\jodbc.jar ianywhere.ml.jdbcodbc.jdbc3.IDriver jdbc:odbc:Driver=SQL Anywhere 10 Demo;UID=DBA;PWD=sql;eng=demo
10.0.1 %SQLANY10%\java\jodbc.jar ianywhere.ml.jdbcodbc.jdbc3.IDriver jdbc:ianywhere:Driver=SQL Anywhere 10;DSN= SQL Anywhere 10 Sample
11.0.0 %SQLANY11%\java\jodbc.jar ianywhere.ml.jdbcodbc.jdbc3.IDriver jdbc:ianywhere:Driver=SQL Anywhere 10;DSN= SQL Anywhere 11 Sample
11.0.1 %SQLANY11%\java\sajdbc.jar sybase.jdbc.sqlanywhere.IDriver jdbc:sqlanywhere:uid=DBA;pwd=sql;eng=demo
12.0.0 %SQLANY12%\java\sajdbc4.jar no longer required for JDBC 4.0 jdbc:sqlanywhere:uid=DBA;pwd=sql;eng=demo
  1. Adaptive Server Anywhere version 9.0 (aka SQL Anywhere 9.0)
    In version 9, SQL Anywhere supported JDBC 2.0 using an iAnywhere generic JDBC-ODBC bridge driver (similar to but different from the Sun JDBC/ODBC driver). The jar file is jodbc.jar, and resides in the %ASA90%\java directory.
    To use the iAnywhere JDBC driver, you need to include the jar in your classpath. Then, you need to register it in your java app using the following code:

    DriverManager.registerDriver( 
         (Driver)Class.forName( "ianywhere.ml.jdbcodbc.IDriver" ).newInstance() );

    Since the iAnywhere JDBC/ODBC driver is a bridge driver, to connect to your SQL Anywhere database, you need to specify a “DRIVER=” parameter along with the rest of your connect string. For example:

    Connection con = DriverManager.getConnection(
         "jdbc:odbc:Driver=Adaptive Server Anywhere 9.0;UID=DBA;PWD=sql;eng=demo" );

    or, you could use an ODBC data source like this:

    Connection con = DriverManager.getConnection(
         "jdbc:odbc:DSN= Adaptive Server Anywhere 9.0 Sample" );
  2. SQL Anywhere 10.0.0
    In version 10, we added support for JDBC 3.0. To use the version 10 iAnywhere JDBC/ODBC bridge driver, you need to again include %SQLANY10%\java\jodbc.jar in your classpath. However, the class name for driver registration is slightly different:

    DriverManager.registerDriver( 
        (Driver)Class.forName( "ianywhere.ml.jdbcodbc.jdbc3.IDriver" ).newInstance() );

    Once registered, the connection URL was the same as in verison 9, above.

  3. SQL Anywhere 10.0.1
    After version 10 was released, we noticed that in some customer issues involving JDBC, the iAnywhere driver was not always being loaded when it was supposed to be, particularly when the Sun JDBC/ODBC bridge driver was present. It turns out that our use of “jdbc:odbc” in the connection URL was not sufficient to guarantee that the iAnywhere driver would be used during a connection. If the Sun bridge were present, it could be picked up and used instead, which lead to all sorts of unexpected behaviour.
    To resolve this problem, the 10.0.1 maintenance release introduced a new URL header for the iAnywhere driver, “jdbc:ianywhere”. From this point forward, the URL to register the driver was the same as with v10, but the correct URL to use when connecting to the database was as follows:

    Connection con = DriverManager.getConnection(
         "jdbc:ianywhere:Driver=SQL Anywhere 10;DSN= SQL Anywhere 10 Sample" );

    The “jdbc:ianywhere” portion of the connection string was actually back-ported to a 9.0.1 ebf, so if you are running one of the later 9.0.1 or 9.0.2 ebfs, the above connection URL will work for you as well.

  4. SQL Anywhere version 11.0.0
    In SQL Anywhere version 11, there was no change in classname for the driver or URL for the connection string, but we did update to a newer version of the JDK.
    This meant we had to drop the JDBC 2.0 driver, because JDK 1.4 and newer no longer supported it. To make things easier for our customers, we kept the JDBC 2.0 class names in the version 10 JDBC 3.0 jar. They simply pointed to the JDBC 3.0 equivalents.
  5. SQL Anywhere 11.0.1
    In SQL Anywhere version 11.0.1, a new SQL Anywhere JDBC driver was introduced. No longer a generic iAnywhere JDBC driver, it is a JDBC driver specific to SQL Anywhere. This was done to make it easier (ie. less confusing) for people to use JDBC with SQL Anywhere. With the new driver, there is no need to install ODBC on the system. This wasn’t a problem for Windows, but our Linux and Unix customers often had problems with this. As an added bonus, the performance of the driver was improved slightly because we no longer have to go through the ODBC driver manager.
    This change involved adding 2 new files to the SQL Anywhere installation: sajdbc.jar and dbjdbc11.dll.
    To use the new driver, you need to include %SQLANY11%\java\sajdbc.jar in your classpath. Then, the driver registration is as follows:

    DriverManager.registerDriver( (Driver)
         Class.forName( "sybase.jdbc.sqlanywhere.IDriver" ).newInstance() );

    Then, to connect, you use the following URL:

    Connection con = DriverManager.getConnection(
        "jdbc:sqlanywhere:uid=DBA;pwd=sql;eng=demo" );
  6. SQL Anywhere Innsbruck
    Not yet released, the plan is that the SQL Anywhere Innsbruck will deprecate the use of the iAnywhere JDBC/ODBC bridge driver in favor of the new SQLAnywhere driver. In addition, SQL Anywhere Innsbruck will support JDBC 4.0 (which requires JDK 1.6 or newer).
    To continue to use the JDBC 3.0, users do not have to make any changes from previous versions. However, to use the JDBC 4.0 support, the new driver name is “sybase.jdbc4.sqlanywhere.IDriver”, and requires that %SQLANY12%\java\sajdbc4.jar be in your classpath. However, there is no need to call DriverManager.registerDriver(…) to register the driver before using it anymore. Sun has implemented automatic driver registration so that applications just need to make sure that sajdbc4.jar is in the classpath (and dbjdbc12.dll is in the path), and use the “jdbc:sqlanywhere” URL header to connect. So, to connect with Innsbruck and JDBC 4.0, all you need is something like the following line of code:

    Connection con = DriverManager.getConnection(
        "jdbc:sqlanywhere:uid=DBA;pwd=sql;eng=demo" );

That concludes the history lesson. Confused yet?

Be Sociable, Share!

Tags: SQL Anywhere

13 responses so far ↓

  • 1 Differences between jConnect and the iAnywhere JDBC driver - part trois // Oct 15, 2009 at 2:05 pm

    [...] my colleague Jason Hinsperger posted a summary of the JDBC URL’s, DLLs, and classnames (along with code samples) for connecting to a SQL [...]

  • 2 subhash // Oct 24, 2009 at 7:20 pm

    hi,
    Can any one give me how can we connect to an sql anywhere 11 using type 4 driver, and what are presrequisite jar files so that to connect to an SQL ANYWHERE 11 data base. thanks in advance.

  • 3 Biff // Oct 26, 2009 at 8:37 am

    See this section of the SQL Anywhere documentation for information on connecting using a type 4 driver (JConnect):
    http://dcx.sybase.com/index.php#1101en/dbprogramming_en11/jconnect-using-jdbxextra.html

  • 4 Rahul // Apr 14, 2011 at 10:00 pm

    Hi,

    My database sever is on remote host (i.e. 10.*.*.123) and my application is running on different machine (say 10.*.*.50)
    so, please Can anyone tell me , how we can specify the remote host IP in connection URL for sybase ianywhere 12.0.0?

  • 5 Jason Hinsperger, Product Manager // Apr 19, 2011 at 6:17 am

    You can specify the location of the server in your connect string. For example: “uid=dba;pwd=sql;eng=myserver;links=tcpip(host=10.*.*.50)”
    BTW, there is a good community forum for SQL Anywhere questions at http://sqlanywhere-forum.sybase.com

  • 6 Ken Harkness // Jun 21, 2011 at 6:18 am

    I am trying to use the new sql anywhere driver to actually start the personal server from my application (I used to be able to do this with the old ODBC driver), but I can’t seem to get this to work properly. I tried using “jdbc:sqlanywhere:uid=DBA;pwd=sql;START=dbeng12;dbf=mydb.db” but I’m not getting the engine to start. (dbeng12 is is the program path). Do you think that I have to explicitly start a process running the dbeng12 command from java?

  • 7 Ken Harkness // Jun 21, 2011 at 6:59 am

    One more question: in the 12.0.1 instructions for deploying a JDBC4.0 client it says that the ODBC driver has to be deployed as well. So does that mean that I still have to install/configure and ODBC entry on my client workstations to auto start the personal server or are these driver files just necessary to use the “START” directive. (I’m trying to avoid having to configure the registry with the ODBC driver).

  • 8 Jason Hinsperger, Product Manager // Jun 21, 2011 at 7:36 am

    Hi Ken. What you are doing should work. I just tried the following connection string in a sample connect program:
    “jdbc:sqlanywhere:uid=DBA;pwd=sql;dbf=c:\\temp\\demo.db;start=dbeng12.exe”

    You might want to try adding the path to make sure your client program is finding the database file, as current directory for java programs is not always obvious.

    As for deployment, you don’t have to set up ODBC, since you don’t actually have to deploy/register the actual ODBC driver (dbodbc12.dll), you only have to deploy the other supporting files listed in the ODBC deployment section:
    http://dcx.sybase.com/index.html#1201/en/dbprogramming/jdbc-client-deploy.html*d5e51159

  • 9 Ken Harkness // Jun 22, 2011 at 7:04 am

    Oh. I now see what i am doing wrong. I am separating the options with colons instead of semicolons in the connection string I built.. ughh. Thanks for the help and quick response!

  • 10 Jason Hinsperger, Product Manager // Jun 22, 2011 at 7:06 am

    I didn’t notice that difference – nice catch.

  • 11 Ken Harkness // Jun 27, 2011 at 7:23 am

    Jason,
    I have one more question: does the SQL Anywhere JDBC driver support the syntax DriverManager.getConnection(url, properties)? I have not seen any examples of this in the documents I’ve looked at.

  • 12 Prashanth // Jul 19, 2011 at 8:45 am

    We have an issue where we are trying to develop an enhancement for a application that uses sql anywhere 10. The problem is that we cannot get the drivers to point to the development database – they will only point to production despite the parameters we are passing. Can anyone help!

  • 13 Jason Hinsperger, Product Manager // Aug 2, 2011 at 12:30 pm

    You should try the SQL Anywhere forums –
    http://sqlanywhere-forum.sybase.com/