SQL Anywhere offers two database connectivity choices for Java applications: jConnect and the iAnywhere JDBC driver. This document highlights the benefits of the iAnywhere JDBC driver and the reasons that application developers should consider using it.

A bit of background, please

Sun introduced JDBC back in JDK 1.1. JDBC is an API specification for communication between a Java client application and a database. A JDBC driver is the layer of the API that interprets the high-level generic function calls to database-specific communications. The JDBC specification identifies four types of JDBC drivers, from the Type 1 "JDBC-ODBC bridge" to the Type 4 "pure Java" driver.

Sybase was one of the first vendors to offer a Type 4 driver for their database platforms. The product, jConnect, uses the Tabular Data Stream (TDS) protocol for communication with an Open Server-capable server, such as Sybase Adaptive Server Enterprise or SQL Anywhere.

Java/JDBC myths

As a part of JDBC 1.0, Sun delivered a proprietary Type 1 driver, the Sun JDBC-ODBC bridge, that is implemented in the sun.jdbc.odbc.JdbcOdbcDriver class. The Java development community took to calling this driver the JDBC-ODBC driver. That particular driver had performance problems with many ODBC drivers, giving all Type 1 and bridge drivers a bad reputation ever since.

Being a pure Java driver, many Java developers looked to using Type 4 drivers in their solution to stay with the Java "write once, run anywhere" mantra. But as many experienced Java developers will testify, the run anywhere mantra is worth sacrificing if it yields better performance (or better developer productivity). More often than not, Java solutions get tied to one platform (the application server) or another (the data store).

JDBC in SQL Anywhere Studio

In version 7 of SQL Anywhere Studio, the two main GUI tools (Sybase Central and Interactive SQL) used jConnect 4.2 by default to connect to SQL Anywhere databases. Prior to version 7, the tools were C/C++ based and used ODBC directly. SQL Anywhere Studio 7 also installed jConnect 5.2 for use in client applications.

One component of SQL Anywhere Studio, the MobiLink synchronization server, uses ODBC to communicate with various consolidated databases (SQL Anywhere, Adaptive Server Enterprise, Oracle, SQL Server, and IBM DB2). To simplify the communication layer of MobiLink in version 8, iAnywhere developed the iAnywhere JDBC driver, ianywhere.ml.jdbcodbc.IDriver. The Studio's main GUI tools continued to use jConnect (version 5.5) for connectivity through the first release of the iAnywhere JDBC driver. SQL Anywhere Studio 8 also installed jConnect 4.5 for use in client applications.

In SQL Anywhere Studio 9, the GUI tools use the iAnywhere JDBC driver by default. The option to use jConnect still exists as jConnect 5.5 is installed for use in client applications.

In SQL Anywhere Studio 10, the iAnywhere JDBC driver was upgraded to be JDBC 3.0 compliant (“ianywhere.ml.jdbcodbc.jdbc3.IDriver”), but includes the JDBC 2.0 compliant driver for backwards compatibility. SQL Anywhere 10 also includes jConnect 6.0.5 is also JDBC 3.0 compliant, while jConnect 5.5 is JDBC 2.0 compliant.  Also in version 10, the GUI tools no longer allow the use of JConnect to connect to SQL Anywhere.

What type of driver is it?

Looking at the description of the four types of JDBC drivers, it could be argued that the iAnywhere JDBC driver is either a Type 1 or a Type 2 driver. The main difference between these types appears to be whether ODBC is actually a native interface to the relational database or not. Since ODBC is a native interface for SQL Anywhere, the iAnywhere JDBC driver is a Type 2 driver.  1  2

Given the choice...

In the SQL Anywhere Studio 10 documentation, the SA Programming Guide has a page entitled "Choosing a JDBC driver". This page identifies both jConnect and the iAnywhere JDBC driver, and weighs the pros and cons of these two drivers.  See the SQL Anywhere product manuals  SQL Anywhere® Server - Programming  > SQL Anywhere JDBC API  > Introduction to JDBC

The jConnect driver is appropriate in environments where a 100% pure Java driver or TDS communication is an absolute requirement.

For any other situation, the iAnywhere JDBC driver is the better choice (note that this is the vast majority of cases).

The TDS protocol used by jConnect is supported by SQL Anywhere natively, but is somewhat generic and does not support any features unique to SQL Anywhere, since it was originally written for Adaptive Server Enterprise (ASE). The other native communication protocol used by SQL Anywhere was written specifically for use with SQL Anywhere and is substantially more efficient than TDS.  The SQL Anywhere ODBC driver uses this protocol, which also has some additional features specific to SQL Anywhere.

• TDS is limited to TCP/IP, even for local connections, while the iAnywhere JDBC driver can use TCP/IP, SPX, HTTP(s) and shared memory.
• Strong encryption support, including RSA, RSA-FIPS, and ECC encryption technologies.
• Prefetch caching of result sets. TDS does not prefetch.
• Complete server-side cursor support. jConnect does not support server-side cursors; it implements a cursor on the client-side by retrieving the entire result set across the network, even if the client will only use a small number of rows from that set.
• Complete AppInfo support. jConnect truncates AppInfo details.
• The ability to automatically start and stop local database servers. TCP/IP connections cannot autostart or autostop servers.
• Richer batch SQL statement support (for example, wide inserts and wide fetches). jConnect only supports wide fetches.
• Integrated logins on Windows platforms.

Empirical evidence shows that under applications with a significant amount of database traffic the iAnywhere JDBC driver performs much better than jConnect. Many customers have reported a 2 to 3 times performance improvement while some internal iAnywhere engineering tests have demonstrated up to a 7 times improvement.

When using jConnect to connect to SQL Anywhere, the connection is initiated in "Adaptive Server Enterprise compatibility mode" which many SQL Anywhere developers find undesirable. For example, Adaptive Server Enterprise does not have the concept of empty strings, so when selecting an empty string using jConnect, the application actually gets a string with one blank in it. Until recently, this made it impossible for an application to distinguish between an empty string, a string containing a blank, and a NULL string. Newer versions of jConnect are able to use the status byte on requests to differentiate between NULL, empty and non-empty strings. Of course, some additional work needs to be done on the part of the app to get jConnect to use this status byte.
Another issue is with the precision of DATETIME values. SQL Anywhere supports microseconds precision, while Adaptive Server Enterprise supports 1/300 second precision, hence, jConnect only handles 1/300 second precision.

A standard JDBC driver

In SQL Anywhere 10.0, the iAnywhere JDBC 2.0 driver is implemented in the ianywhere.ml.jdbcodbc.IDriver class, where as the JDBC 3.0 version is implemented in the ianywhere.ml.jdbcodbc.jdbc3.IDriver class.  This class is located in the java/jodbc.jar file of a SQL Anywhere Studio installation.

To use this driver, follow the normal JDBC coding pattern

1. Create a JDBC URL that describes a connection to the data source.
2. Load the database driver class using Class.forName().
3. Have the JDBC driver manager connect to the data source using the URL.

The URL format is:

jdbc:ianywhere:driver=[ODBC_DRIVER];[CONNECTION_STRING]

[ODBC_DRIVER] is either the name of or the full file system path to the ODBC driver (the driver name can only be used on platforms where the ODBC driver manager supports naming drivers, such as the Microsoft ODBC driver manager).
On the supported Linux/Unix platforms, as of version 10, SQL Anywhere ships with its own ODBC driver manager so you can use driver names in connection strings, the same as you can under the Microsoft ODBC Driver Manager.

[CONNECTION_STRING] is any valid SQL Anywhere connection string with fields separated by semicolons. For more information, see "connection strings" in the index of the SQL Anywhere Studio 9 online documentation.

Show us the code

The following class shows the standard JDBC three step process of connecting to a database. This program uses the iAnywhere JDBC driver that in turn uses the SQL Anywhere ODBC driver.

//--------- Start of TestIasJdbc.java --------------------

import java.sql.*;
import java.io.File;

public class TestIasJdbc {

  public static String USAGE = "\n\n" +
      "usage: java TestIasJdbc <DB_FILE> <ODBC_DRIVER>\n" +
      " where <DB_FILE> is the full path to a SQL \n" + 
      " Anywhere database 
      " and <ODBC_DRIVER> is the name\n" +
      " or path to the driver\n"

  public static void main(String args[]) {
      String driver, url, dbFile, odbcDriver;
      Connection conn;

      dbFile = checkArgs( args );
      odbcDriver = args[1];

      // Step 1: Create a JDBC URL
      //
      url = "jdbc:ianywhere:driver=" + odbcDriver +
            ";UID=DBA;PWD=SQL;DBF=" + dbFile;

           // Step 2: Load the database driver class
      //
            //For SQL Anywhere 10 (JDBC 3.0):
      driver="ianywhere.ml.jdbcodbc.jdbc3.IDriver";

      //For SQL Anywhere 10 (JDBC 2.0):
      driver="ianywhere.ml.jdbcodbc.IDriver";
      try {
          Class.forName( driver );
      } catch (Exception ex) {
          ex.printStackTrace();
          System.exit(3);
      }

      // Step 3: Have the JDBC driver manager connect to
      // the data source
      try {
          conn = DriverManager.getConnection( url );
          if ( conn == null ) {
              System.out.println("Connection failed!");
              System.exit(4);
          }
      } catch (Exception ex) {
          ex.printStackTrace();
          System.out.println("\n\nURL is [" + url + "]\n");
          System.exit(5);
      }

      System.out.println("Successfully connected!");
      }

      public static String checkArgs( String args[] ) {
          if ( args.length != 2 ) {
              System.out.println(USAGE);
              System.exit(1);
          }
          if ( ! new File(args[0]).isFile() ) {
              System.out.println("\nDB_FILE not a file: " + args[0]);
              System.out.println(USAGE);
              System.exit(2);
          }
          return args[0];
      }
}

//--------- End of TestIasJdbc.java ----------------------

Save the above code to a file called TestIasJdbc.java.

The commands to compile and run the code in a 32-bit Windows shell are:

javac -classpath "%SQLANY10%\java\jodbc.jar" TestIasJdbc.java
java -classpath ".;%SQLANY10%\java\jodbc.jar" TestIasJdbc
    %SQLANY10%\demo.db "SQL Anywhere 10"

The commands to compile and run the code in a UNIX/Linux/Mac OS X shell are:

javac -classpath "$SQLANY10/java/jodbc.jar" TestIasJdbc.java
java -classpath ".;$SQLANY10/java/jodbc.jar" TestIasJdbc
    "$SQLANY10/demo.db" "$SQLANY10/lib/libdbodbc9_r.so"

Some notes about this example:

• The code above does not use an ODBC Data Source Name [DSN]. It is a DSN-less connection. The iAnywhere JDBC driver supports both DSN and DSN-less connection strings.
• Since this uses the SQL Anywhere ODBC driver and the DBF is specified, if the client does not find a running server it will automatically start (autostart) a server for the provided database file.
• The SQL Anywhere connection parameter log=sa_connect.txt can be added to the URL to gather information when experiencing connectivity problems. After running the program, the file sa_connect.txt will contain connection-debugging information from the client library. The path to this log file can be a relative to where the client application is running or, better yet, a full file system path.

But it's not pure

In the SQL Anywhere newsgroups (http://www.sybase.com/detail_list?id=10891), there are often questions about JDBC connectivity. Many Java developers have chosen to use jConnect and are now running into behavioral or performance issues. There are a number of reasons that developers choose jConnect:

• Previous experience with jConnect
• Predominance of documentation within Sybase products and on the Web
• It is listed first in the "JDBC Overview" documentation of SQL Anywhere Studio
• Its name starts with the letter J
• It is a Type 4 "Pure Java" driver

Of these arguments, it is the latter that appears to have technical merit. There are certainly advantages to remaining with a pure Java code base. However, the performance and flexibility improvements that an application gains by using the iAnywhere JDBC driver certainly justify its consideration.

Pure Java offers the benefit of being able to run the application in any JVM on any platform. The iAnywhere JDBC driver requires an external ODBC driver, most of which are native to a particular platform, so it cannot run "anywhere".

For SQL Anywhere, the ODBC driver (dbodbc10) is a native library written in C/C++. The driver is available on an extremely wide range of platforms, including various versions Windows (NT/XP/CE/Vista), Solaris, HP-UX, Linux, AIX, and Mac OS X (http://www.sybase.com/detail?id=1032632). The required files for installation of the ODBC client are well documented. So, effectively, the iAnywhere JDBC driver will run on "all platforms" where the overwhelming majority of Java applications are run. Is sacrificing performance or functionality really worth the "pure Java" argument?

In conclusion

When looking to add SQL Anywhere connectivity to a Java application, the iAnywhere JDBC driver should be the first driver considered. Other than the few situations where either pure Java or TDS communications are a requirement, the iAnywhere JDBC driver will likely improve application throughput and raise developer productivity.

---

1  Types of JDBC technology drivers, Sun Developer Network, http://java.sun.com/products/jdbc/driverdesc.html.
2  Java Database Connectivity, Wikipedia, http://en.wikipedia.org/wiki/JDBC.