Switch to standard view 
  Sybase logo
 
 
 



Basic Connectivity for Sybase IQ White Paper-Technical: Data Warehousing, Archived Products, Financial Services Solutions - Sybase Inc

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

Revisions:

Version RevisionAuthorDate
2.0
Added a discussion of the different types of drivers, information about jConnect 7.0, and information about the SQL Anywhere JDBC drivers. J. Smith March 2012
1.0
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. J. Smith September 2009

Contents


Overview

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:

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.

Table 1.1 Sybase JDBC driver list

Sybase JDBC Driver Name
Driver Version
Class File Name
JDBC Specification Compliance
Type
Required Java (JRE or JDK) version
Supported Sybase Databases
jConnect
7.x
jconn4.jar
4.0

3 - in 3-tier environments

4 - in two tier environments

6 (1.6)
ASE 15.7, IQ 15.4, SA 11.x, SA 12.x
SQL Anywhere
12.x
sajdbc4.jar
4.0
2
6 (1.6)
SA 12.x, IQ 15.4
jConnect
6.05
jconn3.jar
3.0
3 - in 3-tier environments

4 - in two tier environments

4 (1.4)

ASE 15.x, IQ 12.7, 15.x, SA 11.x, SA 12.x

SQL Anywhere
11.x
sajdbc.jar
3.0
2
4(1.4)
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.

  1. Open a browser and navigate to the following URL:

http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit/jconnect

  1. Change the default filter time-frame from "last 6 months" to "in all months." The list of jConnect releases will display.

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

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:

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.

Back to Contents


Configure the JDBC driver and database URL

Most JDBC applications require the following information to connect to a 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.

Database URL

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:

jdbc:sybase:Tds:<host_machine>:<port>?ServiceName=<database_name>

jdbc:sybase:Tds:<host_machine>:<port>/<database_name>

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:

  1. Set the JAVA_HOME, JDBC_HOME and CLASSPATH variables as described in the Set the environment variables section earlier in this paper.
  2. Open 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 system prompt.
  3. 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:
1>
  1. At the prompt, enter the following global variable to query the target Sybase server version:
select @@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 ---------------------------------
  Columns:     @@version 
  [ 1] Sybase IQ/15.4.0.6567/111107/P/GA/Sun_Sparc/OS 5.10/64bit/2011-11-07 03:04:18
------------------------ Result set 1 --------------------------------- 
  Columns:
  [ 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 configured environment.

  1. To exit the IsqlApp application, enter "quit" without the quotes at the prompt.
1> quit

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


References

Following is the list of refernces for this paper:

To access these and other Sybase product manuals, do the following:

  1. Open a Web browser and navigate to www.sybase.com.
  2. Navigate to Support & Downloads | Product Manuals.
  3. 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

 
 
 


Back to Top
© Copyright 2010, Sybase Inc.