Basic Connectivity for Sybase IQ White Paper-Technical: Data
Warehousing, Archived Products, Financial Services Solutions - Sybase
Sybase White Paper: Basic JDBC Connectivity for Sybase Databases
Supplement for Sybase Partner Certification Reports
Abstract: This paper describes basic Java Database Connectivity (JDBC) connectivity for Sybase Adaptive Server Enterprise (ASE), IQ and SQL Anywhere (SA) databases. It is intended for use as a supplement to Sybase partner certification reports for certifications that include JDBC connectivity.
Audience: This paper is intended for users and system administrators responsible for configuring JDBC connectivity to Sybase databases.
Author: Jeannette Smith, Sybase Application Engineering Team
|Added a discussion of the different types of drivers, information about jConnect 7.0, and information about the SQL Anywhere JDBC drivers.
|Information taken from the Sybase White Paper: Basic Connectivity for Sybase IQ and initially published as a separate white paper for JDBC connectivity. Information added about jConn support in target Sybase server.
This paper describes how to install and configure basic JDBC connectivity for Sybase ASE, IQ and SQL Anywhere (SA) databases. To support the JDBC connectivity protocol, Sybase provides the jConnect for JDBC drivers and SQL Anywhere JDBC drivers. Using JDBC connectivity involves installing the Sun Java
Development Kit (JDK) or Java Run-time Environment (JRE), installing the Sybase JDBC driver, setting environment variables, and configuring a
JDBC connection. The configuration is similar on all
platforms, with exceptions noted for Windows or UNIX and Linux platforms where appropriate.
JDBC API compliance
JDBC is a call-level application programming interface (API) based on Java. It is for SQL-based database connectivity. Drivers that adhere to a version of the JDBC specifications are said to be "compliant" with that version. The Sybase JDBC drivers discussed in this paper are compliant with either the 3.0 or 4.0 JDBC specification. Information about Sybase driver compliance is provided below in the "Sybase JDBC drivers" section. The JDBC specification documentation, including capabilities, is available on the java.net website (http://www.java.net).
JDBC driver types
JDBC drivers are categorized into four types based on whether they are pure-Java drivers and how they handle database calls. Following is a brief description of each type:
- Type 1 - is known as a JDBC-ODBC bridge. It is not a pure-Java driver. Type 1 drivers use an ODBC driver to connect to a database. They convert JDBC method calls into ODBC function calls.
- Type 2 - is known as the "native-API" or "partial Java" driver. It is not a pure-Java driver either. Type 2 drivers use the client-side database libraries to convert database calls.
- Type 3 - is known as the "pure Java driver for Database Middleware" and "network-protocol" driver. It is a pure-Java driver. Type 3 drivers use the middle-tier, meaning the application server, to convert JDBC calls between the client application and the database.
- Type 4 - is known as the "direct to database," "native-protocol" or "thin" driver. It is also a pure-Java driver. Type 4 drivers convert calls directly into the database protocol.
Sybase JDBC drivers
This section describes the Sybase-provided JDBC drivers. A high-level description is provided first, followed by a table listing the drivers, their specification compliance, type, required Java version, and supported databases.
- jConnect JDBC drivers are Type 4, pure-Java drivers in two-tier environments. They use the Tabular Data Stream (TDS) 5.0 protocol, which is also used in Sybase ASE and Open Client/Server. Some of the jConnect driver behavior is configured to be compatible with ASE, rather than SA or IQ. Note that jConnect drivers are Type 3 drivers if used in a three-tier environment.
- SQL Anywhere (SA) JDBC drivers are Type 2 drivers, not pure-Java. The SA JDBC drivers are recommended for use with SA and IQ even though they are not pure-Java because they were designed to work with these databases and provide better performance than the pure-Java jConnect in most cases. For more information about which drivers to use, see the blog "Connecting To SQL Anywhere Using JDBC," http://iablog.sybase.com/hinsperg/2009/10/connecting-to-sql-anywhere-using-jdbc/ and the "Choosing a JDBC driver" section in the Sybase SA 12.0.1 Programming Guide. Note that the versions of the SA JDBC drivers (4.0 and 3.0) are indicative of the JDBC specficiation with which they comply, not the driver type or version.
- iAnywhere JDBC drivers (jodbc.jar and jodbc4.jar files) have been deprecated. They were replaced by the SA JDBC drivers, which should be used instead.
Table 1.1 Sybase JDBC driver list
Sybase JDBC Driver Name
Class File Name
JDBC Specification Compliance
Required Java (JRE or JDK) version
Supported Sybase Databases
3 - in 3-tier environments
4 - in two tier environments
ASE 15.7, IQ 15.4, SA 11.x, SA 12.x
SA 12.x, IQ 15.4
- in 3-tier environments
4 - in two tier environments
ASE 15.x, IQ 12.7, 15.x, SA 11.x, SA 12.x
|SA 11.x, IQ 15.x
Back to Contents
Download Sybase JDBC drivers
jConnect JDBC drivers
If you have a valid Sybase login, you may be able to download the
jConnect JDBC driver and client software described in this white paper from the Sybase
website. If you do not have a valid login, contact Sybase Technical
Support to determine if you are authorized for one. Otherwise, you should obtain the software from your normal distribution source.
- Open a browser and navigate to the following URL:
- Change the default filter time-frame from "last 6 months" to "in all months." The list of jConnect releases will display.
- Select and download the version you want.
Note that the Sybase jConnect JDBC drivers are also available with the Sybase IQ and ASE database server and client installations, as well as the Sybase Software Developer Kit (SDK).
SA JDBC driver
The SA JDBC driver is available with the SA server, the IQ server and IQ client products. The server products are available from the Sybase Product Download Center (http://sybase.subscribenet.com). The IQ client products are available from the Sybase EBF website (http://downloads.sybase.com). You will need a Sybase login and password to access and use these sites. Contact Sybase Technical Support for more information.
Back to Contents
Install the Sybase JDBC driver
Install the Sybase JDBC driver on the machine where the JDBC client application with which you want to use it is, or will be, installed. The installation process will vary depending on the product from which you will install the driver. Refer to the installation program instructions for information.
Download and install Sun JDK/JRE
In addition to installing the Sybase JDBC driver on the machine with the JDBC client application, you need to install Java, which means the Sun Java Development Kit (JDK) or Java Run-time Environment (JRE). The Sybase product from which you installed the JDBC driver may include the required version of Java, in which case you can use it. If the required version of Java is not installed, you will need to download and install JDK or JRE from the Java website (http://java.com). Following is a list of the Sybase JDBC drivers and their supported Java versions:
- jConnect 7.07 - supported with JDK/JRE 1.6 or later
- jConnect 6.05 driver - supported with JDK/JRE 1.4
- SA 4.0 JDBC driver - supported with JDK/JRE 1.6 or later
- SA 3.0 JDBC driver - only supported with JDK/JRE 1.4
Note that these
instructions describe using a JDK.
Verify the target Sybase server is configured for JDBC driver support
To support Sybase jConnect drivers, meta data stored procedures must be created in the Sybase database server you plan to access with the JDBC application. The Sybase IQ installation programs create these stored procedures by default. The ASE and SA installations do not. See the section "Installing stored procedures" in the jConnect for JDBC Installation Guides, for information about scripts to run to create these stored procedures.
The database metadata for the SQL Anywhere JDBC driver is always available.
Set the environment variables
While most Sybase product installation programs set environment variables on Windows platforms or provide scripts to do so on UNIX and Linux, these programs do not usually set the variables you need for JDBC connectivity. The required JDBC driver variables follow in this section. Before setting variables, it is a good idea to check to see what variables are already set, or what variables the provided scripts set, in your envronment. To check what variables are set, do the following as appropriate for your platform:
- On Windows, the Sybase installation programs typically set
the environment variables as "System" variables. To view the
environment variables set on a Windows machine, navigate to the System
properties. The way you access the System Properties and environment
variables varies with the version of Windows. For example, on XP, you
can choose Start | Settings | Control Panel | System and then open the
Advanced tab on the System Properties dialog box and click the
Environment Variables button. This opens the Environment Variables
- On UNIX,
the Sybase installation programs typically create shell scripts
(usually in .sh for korn, bourne and bash shells, or .csh for c shell), which
users can run to set the environment variables.
Take a look at the following scripts:
- Sybase IQ, ASE, and Sybase Open Client all provide the SYBASE.(c)sh
scripts in the root installation directory ($SYBASE).
- ASE 15.x provides the ASE-15_0.(c)sh scripts in their installation directories ($SYBASE/ASE-15_0).
- IQ 15.4 provides the IQ-15_4.c(sh) scripts in their installation driectories ($SYBASE/IQ-15_4)
- IQ 15.x provides the IQ-15_0.(c)sh scripts in $SYBASE/IQ-15_0.
After checking the existing variable settings, you need to manually set or modify the following variables for the Sybase JDBC driver. For your convenience in the future, you may want to create a batch or shell script that sets these variables appropriately in your environment.
- JAVA_HOME - set to the JDK or JRE installation directory.
For example on UNIX or Linux, /Sun/JDK16/jdk1.6.0_05, or on Windows,
C:\Sun\SDK16\jdk1.6.0._05. You set this variable for the Sybase jConnect and SA JDBC drivers.
- CLASSPATH - how you set the classpath varies for the different Sybase JDBC drivers:
- For jConnect, set two entries: one with the fully-qualified class jar file (jconn3.jar for jConnect 6.x or jconn4.jar for 7.x), which is in the jConnect classes directory and usually first, and one for the
jConnect classes directory itself. For example on UNIX or Linux,
or on Windows,
- For the SA JDBC drivers, set the classpath to include the driver jar file (sajdbc.jar for the 3.0 driver and sajdbc4.jar for the 4.0 file). For example, %SQLANY12\java\sajdbc.jar;classpath on Windows.
- JDBC_HOME - for jConnect only, set to the Sybase
jConnect-6_0 or jConnect-7_0 installation directory. The location of the jConnect installation directory varies with the product from which it was installed. It may be under the root Sybase installation directory or it may be in the Sybase /shared directory. For example, on UNIX and Linux, it may be
/sybase/shared/jConnect-6_0, or on Windows, C:\Program
Back to Contents
Configure the JDBC driver and database URL
Most JDBC applications require the following information to connect to a database:
- Driver specification
- Database URL
that specifies the host and port of the target database server
- Valid login for the target database
Following are the requirements for the Sybase JDBC drivers.
JDBC driver specification
For JDBC 3.0 specification compliance, the full Sybase JDBC driver specifications should be entered as shown below. The driver specification does not vary with your environment information, like the database URL does. The 4.0 JDBC specification implemented the "automatic driver registration" capability. This capability means that the driver will automatically be loaded when it is specified in the CLASSPATH environment variable. It does not require a full driver specification. You should only need the URL header, for example, "jdbc:sqlanywhere" to connect. Note that you can still use these drivers with 3.0-compliant applications using the full driver specification, as long as they use Java 6. The 4.0 compliant drivers are shown followed by an asterisk in the list below.
- JDBC 7.x driver: com.sybase.jdbc4.jdbc.SybDriver*
- JDBC 6.05 driver: com.sybase.jdbc3.jdbc.SybDriver
- SA 4.0 driver: sybase.jdbc4.sqlanywhere.IDriver*
- SA 3. 0 driver: sybase.jdbc.sqlanywhere.IDriver
The Database URL includes
the environment-specific information, specifying the host and port of the target database.
Sybase jConnect JDBC driver database URL
Following is the format of the jConnect Database URL:
Database URL: jdbc:sybase:Tds:<host_machine>:<port>
There is also a parameter for the database name, which varies for Sybase IQ and ASE as follows:
- For Sybase IQ and SA - most Sybase IQ and SA servers run with just one database. Thus, this parameter may be unnecessary. If you do want to specify a database in the URL, append the ServiceName parameter with a question mark. !Important: "?ServiceName=<database_name>" as follows:
- For ASE - some applications may not require the ASE database name, using instead the default database of the user you specify for login. If you do want to specify a database in the URL, append the database name with a forward slash as follows:
Sybase SA JDBC driver database URL
Following is the format of the SA JDBC driver URL. Note that it can use a variety of SA or IQ connection parameters, including an ODBC data source name (DSN). For lists of connection parameters, see the "Connection Parameters" section in the Sybase IQ Administration Guide: Volume 1 or the "Connection Parameters" section in the SQL Anywhere server Database Administration guide. The user id, password and SA engine connection parameters are shown below.
SA 3.0 and 4.0 drivers: jdbc:sqlanywhere:uid=<dba>;pwd=sql;eng=demo or, the SA JDBC driver can use an ODBC data source name (DSN) or other SA connection parameters. Refer to the SA Programming Guide for more information.
Verify JDBC connectivity
Following are instructions for verifying JDBC driver configuration for both the Sybase jConnect SA JDBC drivers:
Sybase jConnect connectivity
Verify jConnect JDBC connectivity using IsqlApp, which is a Sybase interactive SQL
Java application installed with jConnect
in its classes directory. To use IsqlApp, do the following:
- Set the JAVA_HOME, JDBC_HOME and CLASSPATH variables as described in the Set the environment variables section earlier in this paper.
a command prompt and change to the jConnect-6_0\classes directory of the jConnect installation. On
Windows, open a DOS command prompt. On UNIX and Linux, use an operating
- Launch the IsqlApp application using a command
similar to the following:
java IsqlApp -U <user> -P <password> -S jdbc:sybase:Tds:<host_name>:<port>
where <user> and <password> are valid in the target server and the jConnect JDBC database URL is provided for the -S parameter. The IsqlApp should open with the following command prompt.
Note that if it does not, troubleshoot the environment based on the
error. The cause of problem is often related to the variable settings
or the directory from which you attempted to launch the application.
Enter a query:
- At the prompt, enter the following global variable to query the target Sybase server version:
The first sample result is for Sybase IQ; the second is for ASE. The result should be similar to the following but appropriate for the platform and version to which you connected:
------------------------ Result set 1 ---------------------------------
[ 1] Sybase IQ/18.104.22.16867/111107/P/GA/Sun_Sparc/OS 5.10/64bit/2011-11-07 03:04:18
------------------------ Result set 1 ---------------------------------
[ 1] Adaptive Server Enterprise/15.7.0/EBF 19496 SMP /P/X64/Windows Server/ase157/2820/64-bit/OPT/Thu Sep 14 21:32:03 2011
This demonstrates a successful JDBC connection and properly
- To exit the IsqlApp application, enter "quit"
without the quotes at the prompt.
Sybase SA JDBC Driver
The SA Programming Guide provides a sample application you can use to verify JDBC connectivity with the SA JDBC driver. The section "Running the connection example" provides instructions for using this application, which you can use to to validate SA JDBC driver configuration.
Back to Contents
Following is the list of refernces for this paper:
- Sybase jConnect Installation Guides for jConnect for JDBC 6.0 and 7.07, source for information about jConnect JDBC drivers
- "Connecting to SQL Anywhere Using JDBC" blog, http://iablog.sybase.com/hinsperg/2009/10/connecting-to-sql-anywhere-using-jdbc/, source for detailed information about i Anywhere and SA JDBC drivers
- SDK 15.5 features for jConnect and Adaptive Server driver and providers, specifically the "JDBC 4.0 support" section.
- SA 12.0.1 Programming Guide, source for information about the SA JDBC drivers
- Sybase IQ 15.4 System Administration Guide: Volume 2, "Data Access using JDBC"
- Wikipedia topic "JDBC Drivers" was the source for information about the JDBC driver categories or types
- "Java SE Technologies - Database," specifically the "JDBC" section, http://www.oracle.com/technetwork/java/javase/jdbc/index.html
To access these and other Sybase product manuals, do the following:
- Open a Web browser and navigate to www.sybase.com.
- Navigate to Support & Downloads | Product Manuals.
- When the Product Manuals page opens, locate the Select a product window, choose jConnect for JDBC, the language you prefer, and click Go.
A list of the document sets by version for jConnect will open. Choose the set you want and then the document you want
from the Collection list when it opens.
Back to Contents