In this post, originally written by Glenn Paulley and posted to sybase.com in March of 2009, Glenn talks about the differences between JConnect and the SQL Anywhere JDBC driver. While I have left in the references to specific supported versions of JConnect, JDBC and SQL Anywhere for historical reference, I do want to note that versions 12 and 16 of the SQL Anywhere JDBC driver now support JDBC 4.0. The latest version of JConnect is 7.07 and also supports JDBC 4.0.
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 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 4.0 compliant.
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.idriverprovides 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 VMwith 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. jConnectis downloadable from sybase.com. Make sure you specify "all months" for the display of possible versions/EBFs to download:
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.
