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:
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.