Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

I'd rather play golf


Thoughts on data management, autonomic computing, and self-managing database systems.

header image

Differences between jConnect and the iAnywhere JDBC driver – part un

March 16th, 2009 · 17 Comments

Now that the Brier is over–for those of you not in the know, the Brier is the Canadian Mens’ Curling Championship–we can get back to some regularly scheduled programming.

Lately I’ve been getting some questions about the differences between Sybase jConnect and the iAnywhere JDBC driver, so I thought I’d put some points down here. Eventually I’ll turn this content into a Sybase technical document, but here it is for now.

In a nutshell

A brief summary of the differences between the two JDBC implementations are as follows:

  • jConnect is a Type 4 JDBC driver, while the iAnywhere JDBC driver is Type 1, and relies on the existence of a properly-installed ODBC driver.
  • jConnect uses the Sybase Tabular Data Stream (TDS) wire protocol, while the iAnywhere JDBC driver uses the native and proprietary SQL Anywhere application-level protocol called CMDSEQ.
  • SQL Anywhere supports TDS only over the TCP/IP network protocol. In contrast, the SQL Anywhere-specific CMDSEQ protocol supports both TCP/IP as well as an efficient shared-memory protocol designed for same-computer communication.
  • It is assumed that applications connecting to SQL Anywhere via jConnect, and hence using TDS, are desirous of Sybase ASE behaviour; hence the SQL Anywhere implementation of jConnect support sets a number of ASE compatibility settings immediately after the application connects to the database (see below).
  • A variety of jConnect behaviours stem from its native support for Sybase ASE. This is particularly true with jConnect’s support for cursors and specific data types (more on this in a subsequent article).

JDBC driver types

Sybase jConnect 6.05 (jconn3.jar) is a Type 4 JDBC driver which is entirely Java-based. In contrast, the iAnywhere JDBC driver is a Type 1 driver, as it relies on its underlying (non-Java) ODBC driver to actually communicate with the SQL Anywhere server. Both the iAnywhere JDBC driver and jConnect 6.0.5 are JDBC 3.0 compliant. jConnect 5.5 (jconn2.jar)–still supported by Sybase Engineering–is JDBC 2.0 compliant (see below).

SQL Anywhere Version 11 supports only JDBC 3.0 (JDK 1.4 and up). SQL Anywhere Version 10 supported both JDBC 2.0 and 3.0; the jodbc.jar that shipped with Version 10 contained support for both, but which version was used depended on the driver name:

  • iAnywhere.ml.jdbcodbc.idriver provides JDBC 2.0 support; while
  • iAnywhere.ml.jdbcodbc.jdbc3.idriver provides JDBC 3.0 support.

If you plan to utilize a Java application on a Windows CE platform, the older jConnect 5.5 may be your best option. On Windows CE, the best Java VM to install is IBM’s J9 VM with the “Mobile Database Option”, which provides JDBC 2.0 support (from JDK Version 1.3). However, as only jConnect 5.5 is JDBC 2.0-compliant—jConnect 6.05 and SQL Anywhere 11 require JDBC 3.0—then you should install jConnect 5.5 (jconn2.jar) on the device.

Using jConnect with SQL Anywhere on any platform requires installation of jConnect’s JDBC metadata schema in the database. By default, SQL Anywhere databases are created with jConnect metadata support; you can explicitly add it using dbupgrad -j. jConnect is downloadable from sybase.com here. Make sure you specify “all months” for the display of possible versions/EBFs to download:

Downloading jConnect versions from sybase.com

Connection options

When connecting via JConnect, the SQL Anywhere server automatically resets the values of several option settings to permit the server to emulate Sybase ASE behaviour; this occurs in the sp_tsql_environment system procedure, which executes the following SET OPTION statements for the connection:

SET TEMPORARY OPTION allow_nulls_by_default='Off';
SET TEMPORARY OPTION ansi_blanks='On';
SET TEMPORARY OPTION ansinull='Off';
SET TEMPORARY OPTION chained='Off';
SET TEMPORARY OPTION close_on_endtrans='Off';
SET TEMPORARY OPTION date_format='YYYY-MM-DD';
SET TEMPORARY OPTION date_order='MDY';
SET TEMPORARY OPTION escape_character='Off';
SET TEMPORARY OPTION isolation_level='1';
SET TEMPORARY OPTION on_tsql_error='Continue';
SET TEMPORARY OPTION quoted_identifier='Off';
SET TEMPORARY OPTION time_format='HH:NN:SS.SSS';
SET TEMPORARY OPTION timestamp_format='YYYY-MM-DD HH:NN:SS.SSS';
SET TEMPORARY OPTION tsql_variables='On';

Note that the original or default values for the connection are not retained. Also note that the default isolation level for TDS connections is “1″ (READ COMMITTED). Older versions of both DBISQL and Sybase Central undo these temporary option settings after they have connected to the database server to retain SQL Anywhere semantics as much as possible, so one may not notice any difference. Newer versions of the DBISQL and Sybase Central admin tools (SQL Anywhere Version 10 and up) no longer support connecting via jConnect.

Next: Semantic and performance differences between jConnect and the iAnywhere JDBC driver.

Tags: SQL Anywhere · Sybase ASE

17 responses so far ↓

  • 1 ron hiner // Mar 17, 2009 at 10:50 pm

    Thanks Glenn…
    I took a couple years for me to figure out that IAnywhere JDBC driver was works far better than jConnect with Adobe’s Coldfusion. There is precious little documentation about the difference between the various connection options.

    This is a bit of a bible for me: http://www.sybase.com/detail?id=1037304
    Ron

  • 2 Glenn Paulley // Mar 17, 2009 at 11:32 pm

    Thanks, Ron.

    The link you refer to is an excellent source of information about the iAnywhere JDBC driver – in the forthcoming blog posts I have in mind, I want to go into a bit more depth about the underlying differences – particularly with respect to performance. Stay tuned!

  • 3 Breck Carter // Mar 18, 2009 at 1:59 am

    Maybe you have some thoughts on whether the “smalldatetime issue” thread on sybase.public.sqlanywhere has anything to do with jconn3 versus idriver ( there, I’ve started using the short name, I get tired typing “iAnywhere JDBC driver” :)

  • 4 Susannah // Apr 8, 2009 at 12:47 pm

    This is the most helpful thing about SQL Anywhere drivers that I’ve read all day. Thanks so much for taking the time to post!

  • 5 Susannah // Apr 8, 2009 at 1:01 pm

    One thing – if planning to create a connection pool, is it possible to use the iAnywhere JDBC driver?

  • 6 Glenn Paulley // Apr 12, 2009 at 8:26 am

    Susannah – I am unaware of anything to prevent you from using the iAnywhere JDBC driver in conjunction with connection pooling. Are you using a toolkit to implement pooling functionality? If so, which one?

  • 7 Stepan // Jun 30, 2009 at 9:03 am

    Does anybody know any usable implementation of the abstract connection pool functionality that can be used with any JDBC driver? I’m looking for something like that to use iAnywhere JDBC driver with connection pool. Is there some tool or I should wrap the iAnywhere JDBC driver with connection pool functionality myself?

  • 8 Glenn Paulley // Jun 30, 2009 at 12:36 pm

    Current releases of SQL Anywhere, including Version 11, do not offer a connection pooling implementation. A quick search on the web yielded several connection pooling implementations for JDBC, including:

    I have no experience with any of these; perhaps another reader can comment on these or other implementations.

  • 9 Stepan // Jul 1, 2009 at 2:12 am

    Thanks, Glenn!

    I’ll test these wrappers and then post here what I managed to do.

  • 10 Stepan // Jul 1, 2009 at 8:35 am

    Unfortunately, that’s not I’m looking for. I use WebSphere Application Server 6.1 which already has a connection pool. But the drivers that can be used with it must implement the JDBC 2.0 Optional Package API. A JDBC driver must implement the ConnectionPoolDataSource interface to act as a factory for PooledConnection objects. So I’m looking for a wrapper around the JDBC driver which only implements the JDBC 2.0 Optional Package API, but not the whole connection pool itself. Also, this driver must grant a low-level implementation to an abstract driver that implements JDBC 2.0 Core API, than it will be possible to use an SQL Anywhere in the WebSphere connection pool. For example, there is a Protomatter JDBC Connection Pool (http://protomatter.sourceforge.net/1.1.8/index.html). This implementation should be exactly that I need, but it does not implement many abstract methods of the JDBC 2.0 interfaces, so it can’t be used. I’m interesting is there anybody who managed to use some wrapper like this?

  • 11 Stepan // Jul 3, 2009 at 4:03 am

    I think the way to make the SQL Anywhere JDBC driver usable in the WebSphere connection pool will be interesting for somebody. I managed to do this myself. I made only 2 wrapper classes.

    The first class implements a PooledConnection interface. It’s getConnection method simply calls the ASADataSource’s getConnection method and returns a new connection. Close method closes it and sends a connectionClosed event to the pool (event listeners a set and removed in it’s addConnectionEventListener and removeConnectionEventListener methods).

    The second class implements the ConnectionPoolDataSource interface. It’s two getPooledConnection methods create instances of the first described here class with provided user credentials. Also, this class is responsible for processing the connection pool settings: it implemets setUser, setPassword and setURL methods. These methods are called by WAS pool while creating a new connection. The URL for the ASA JDBC driver is stored in the datasource’s advanced properties of the WAS pool. The credentials are stored in the J2C storage.

    The second class should be specified in the connection pool settings as the driver implementation class.

    That’s all. The other connection pool functionality is implemented in the WAS connection pool, so you don’t have to care about how and where store the connections, limiting their count or something else. The described way can be used to make a WAS “pool wrapper” for any JDBC driver that supports the JDBC 2.0 Core API.

  • 12 Differences between jConnect and the iAnywhere JDBC driver - part deux // Oct 6, 2009 at 5:45 pm

    [...] a previous post I briefly described some of the differences between the jConnect JDBC driver and the iAnywhere JDBC [...]

  • 13 subhash // Oct 13, 2009 at 9:45 pm

    Hi Glenn,

    I am a student from India. Could you offer the advantages of installing a database on a mobile device, as in the services provided with SQL Anywhere 11? Also, is it advisable to install a database on a mobile device with respect to security issues? Thanks a lot.

  • 14 Jude Kofi // Mar 8, 2010 at 4:43 pm

    I am looking to set up glassfish to use the new ASA11 drivers -sajdbc and the dll file.

    Has any one been sucessful in doing this? What is the data source class? Do I need to add the sajdbc jar as a native library to glassfish?
    Any help will be much appreciated.
    Thank you

  • 15 Jude Kofi // Mar 10, 2010 at 10:29 am

    I get An error has occurred
    java.lang.ClassNotFoundException cannot be cast to java.sql.SQLException when I ping it from glass fish

    This is part of the error stack I am getting:

    com.sun.enterprise.web.connector.grizzly.WorkerThreadImpl.run(WorkerThreadImpl.java:116) Caused by: javax.resource.ResourceException: java.lang.ClassNotFoundException cannot be cast to java.sql.SQLException at com.sun.enterprise.connectors.ConnectorConnectionPoolAdminServiceImpl.testConnectionPool(ConnectorConnectionPoolAdminServiceImpl.java:556) at com.sun.enterprise.connectors.ConnectorRuntime.testConnectionPool(ConnectorRuntime.java:520) at com.sun.enterprise.admin.mbeans.ResourcesMBean.pingConnectionPool(ResourcesMBean.java:2387) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.enterprise.admin.MBeanHelper.invokeOperationInBean(MBeanHelper.java:381) at com.sun.enterprise.admin.MBeanHelper.invokeOperationInBean(MBeanHelper.java:364) at com.sun.enterprise.admin.config.BaseConfigMBean.invoke(BaseConfigMBean.java:477) at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:836) at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:761) at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.enterprise.admin.util.proxy.ProxyClass.invoke(ProxyClass.java:90) at $Proxy1.invoke(Unknown Source) at com.sun.enterprise.admin.server.core.jmx.SunoneInterceptor.invoke(SunoneInterceptor.java:304) at com.sun.enterprise.interceptor.DynamicInterceptor.invoke(DynamicInterceptor.java:174) at com.sun.enterprise.tools.admingui.util.JMXUtil.invoke(JMXUtil.java:119) … 55 more Caused by: java.lang.ClassCastException: java.lang.ClassNotFoundException cannot be cast to java.sql.SQLException at sybase.jdbc.sqlanywhere.ASADataSource.getConnection(ASADataSource.java:210) at com.sun.gjc.spi.DSManagedConnectionFactory.createManagedConnection(DSManagedConnectionFactory.java:105) at com.sun.enterprise.connectors.ConnectorConnectionPoolAdminServiceImpl.getUnpooledConnection(ConnectorConnectionPoolAdminServiceImpl.java:1315) at com.sun.enterprise.connectors.ConnectorConnectionPoolAdminServiceImpl.testConnectionPool(ConnectorConnectionPoolAdminServiceImpl.java:551) … 74 more

  • 16 Glenn Paulley, Director, Engineering // Mar 10, 2010 at 1:12 pm

    I don’t see any reference to any SQL Anywhere classes in this stack trace; if I had to guess, I’d say that the URL you are specifying is referencing the Sun JDBC-ODBC bridge rather than the SQL Anywhere JDBC driver. Take a look at this blog post by Jason Hinsperger. If you continue to have issues, I suggest contacting SQL Anywhere technical support for direct troubleshooting help, or alternatively posting a message on one of the SQL Anywhere NNTP support forums (forums.sybase.com) or Breck Carter’s SQLA site.

  • 17 Raymond Kwong // Jun 3, 2010 at 9:44 pm

    Jude’s report on ASADatasource is likely related to a bug fix that we just made yesterday. I would encourage anyone running into problems using ASADatasource with sajdbc.jar to wait for the bug fix which will be available for v11.0.1 EBF build 2444 or later.