Connecting to Sybase Databases

On Windows and UNIX, ColdFusion lets you access Sybase databases using ODBC and native drivers. ColdFusion 5 supports Sybase 11 and later.

If you install ColdFusion on a Windows server, you can configure a Sybase database as a ColdFusion data source using ODBC, OLE DB, or a native driver. For information about using OLE DB with ColdFusion data sources, see "About OLE DB".

ODBC: MERANT Sybase ASE Driver options

The following table describes ColdFusion options for the MERANT Sybase ASE ODBC driver. You set these options when you configure a ColdFusion data source.

Option

Description

Data Source Name
A name for your ODBC data source.

Description
Descriptive information about the data source.

Database Name
The name of the database to which you want to connect.

Server Name
The name of the server containing the Sybase tables that you want to access. If not supplied, the initial default is the server name in the DSQUERY environment variable. On UNIX, the name of a server from your $SYBASE/interfaces file.

Server Port
The port number that the Sybase server monitors for requests. The default value is 5000.

Network Library (Windows only)
The name of the network library. This specifies which network protocol to use (Winsock or NamedPipes). The default is Winsock. This option has no effect on UNIX; on UNIX, TCP/IP is used.

Performance
Row Limit (Fetch Array Size on Windows) The number of rows the driver retrieves from the server for a fetch. Selecting this option can increase performance by reducing network traffic.
Create stored procedures (UNIX only) Determines whether stored procedures are created on the server for every call to SQLPrepare.
When enabled, stored procedures are created for every call to SQLPrepare. This setting can result in bad performance when processing static statements.
When disabled, the driver does not create stored procedures.
Disable database cursors for Select statements Determines whether database cursors are used for Select statements. In some cases performance degradation can occur when performing large numbers of sequential Select statements because of the amount of overhead associated with creating database cursors.

Native: Sybase 11 Driver options

To connect to Sybase System 11 databases on Windows NT and UNIX, you must first install the Sybase client software, Sybase Open Client version 11.1.0 with Update 11.1.1 applied.

To use the native driver:

Install the Sybase Open Client version 11.1.0 (with Update 11.1.1 applied) client software.
Verify the connection to the database using a tool like Sybase SQL Advantage.
Create the data source in the ColdFusion Administrator, Native Drivers page.

You set these options when you configure a ColdFusion data source.


Option	Description
Data Source Name	A name for your ODBC data source.
Description	Descriptive information about the data source.
Server	Enter the name of the server hosting the Sybase System 11 database.
Default Database	Enter the name of the default database to use on the specified server. Enable RAISERROR Select to obtain user-defined errors from stored procedures and triggers.

Tips for connecting to Sybase System 11 (UNIX)

Keep the following tips in mind when you create Sybase ColdFusion data sources:

You can set up the Sybase data source using the ColdFusion Administrator Data sources page.
You need Sybase Open Client version 11.1.0 with Update 11.1.1 applied on your server. This software does not ship with ColdFusion.
Check that the SYBASE environment variable is set up in the /opt/coldfusion/start script. Also check that the LD_LIBRARY_PATH has the $SYBASE/lib directory in the beginning of its path; for an example, see "The /opt/coldfusion/bin/start script".
Set up an entry in the interfaces file for the particular database that you want to connect to. The interfaces file is in the $SYBASE directory; for example, /opt/sybase or /work/sybase or wherever you installed the Sybase client software. You can use a Sybase utility called sybinit on UNIX to update this file.

Note

If the Sybase database is on the same server as ColdFusion, make sure the $SYBASE environment variable that you set up in the ColdFusion start script is pointing to the Sybase client directory and not the Sybase server directory. Both of these directories contain an interfaces file.

The /opt/coldfusion/bin/start script

#!/bin/sh

# start - setup environment and run Cold Fusion servers

# This script should be run as root.

# Run as root, we are able to start the system registry deamon

# and then change to the Cold Fusion userid to start the servers

# Set during install



CFHOME=/opt/coldfusion

CFUSER=nobody



# Sybase Open Client directory



SYBASE=/work/sybclient11.1;export SYBASE



#II_SYSTEM=/home

# Set library search path

# NOTE: Add your database client library directory to the FRONT 

# of this list

# Example: 

# LD_LIBRARY_PATH=$SYBASE/lib:/usr/dt/lib:/lib:/usr/openwin/lib:

# $CFHOME/lib



LD_LIBRARY_PATH=$SYBASE/lib:/usr/dt/lib:/lib:/usr/openwin/lib:$CFHOME/lib



# This is the list of variables that Cold Fusion will see

# Add any special Database environment varaibles here



VAR_LIST=""LD_LIBRARY_PATH CFHOME SYBASE ORACLE_HOME INFORMIXDIR INFORMIXSERVER II_SYSTEM""

After you complete all the steps in this section, you must stop and restart ColdFusion services to reload the odbc.ini file.

Advanced ColdFusion Administration
Advanced Data Source Management