Sybase Adaptive connection strings

ASE ADO.NET Data Provider

Standard

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

It's also possible to define the port value together with the data source value, separated either by colon or comma i.e. DataSource=servername,port; or DataSource=servername:port;

Use encrypted password

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;EncryptPassword=1;

If encryption is not supported an error will occur.

Use password encryption or plain text

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;EncryptPassword=2;

Password is sent over the wire after a login is negotiated. If encryption is possible it will be encrypted. Otherwise it will be sent as plain text.

Use SSL

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Encryption=ssl;TrustedFile='c:\sybase\ini\trusted.txt';

Connection Pooling

Connection pooling reuse connections from a pool. It saves the connection handle for reuse, instead of open new connections. Connection pooling is turned on by default.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Min Pool Size=5;Max Pool Size=50;

The Max Pool Size value restricts the maximum number of open connections. The calls to AseConnection.Open() fail with an AseException if the limit is reached.

Disable Connection Pooling

It's sometimes a good idea to disable connection pooling for trouble shooting.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Pooling=False;

Connection pooling with idle time out

Specified in seconds, how long a connection can be idle in the pool before the driver closes the connection.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;ConnectionIdleTimeout=600;

Default value is 0, meaning connections can stay idle for an indefinite amount of time. The driver will never close the connection.

Connection pooling with max lifetime

Specified in seconds, how long an underlying connection can exist before the driver closes the underlying connection instead of returning it to the connection pool upon connection object close. Idle pooled connections are closed and removed from the pool once they reach the defined Connection Lifetime.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Connection Lifetime=600;

Default value is 0, meaning connections can remain open for an indefinite amount of time. The driver will never close the connection.

Cluster Edition connection failover

If opening a connection to the primary server (specified by the Data Source value) is unsuccessful, the driver uses the servers listed in Alternate Servers.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;HASession=1;AlternateServers=anotherASEserver:5001,aThirdASEserver:5005;

After a connection is established, with any available instance, the client receives an updated list of failover targets from the server.

Connection failover alternative syntax

This is just another way of specifying failover in the connection string.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;HASession=1;Secondary Data Source=anotherASEserver;Secondary Server Port=5002;

Two connections in same transaction enlistment

Use this if you open two database connections to the same Adaptive Server server and enlist these connections in the same distributed transaction. If not specified (TightlyCoupledTransaction=1;) the distributed transaction may fail.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;TightlyCoupledTransaction=1;

Using the sql.ini file

If the server isn't aliased, you have to use the ip address and port number in the connection string, not ideal as these may change occassionally. Installing sybase on a machine, you will have a "sql.ini" file that maps an alias name to an ip address and a port. That file can be rolled out to all users, and the sql.ini updated when necessary. Use the following connection string to force the AseConnection object to use the interface file.

DSURL='file://c:\sybase\ini\sql.ini?SQL_MIDOFF_OPC1';Database=myDataBase;UID=myUsername;PWD=myPassword;APP=myAppName;

Provide client info to the ASE server

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;ClientHostName=computerName;ClientHostProc=localProcessName;ApplicationName=myAppName;

ClientHostName and ClientHostProc are used for the login record on the server. The ApplicationName is used by Adaptive Server to identify the client application.

Set buffer cache size

Default is 20.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;BufferCacheSize=50;

Increase for very large results to boost performance.

Count last update only

The records affected count returned includes all records happening from all updates or inserts (including triggers) in a stored procedure. Use this to return only the last update/insert count.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;CumulativeRecordCount=0;

Enable bulk load

Using ASEBulkCopy is disabled by default. Use this to allow for different bulk load operations.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;EnableBulkLoad=1;

Options: 1 = bulk-load using array insert, 2 = bulk-load using the bulk copy interface, 3 = bulk-load using the fast logged bulk copy interface.

Disable server packet size decission

Adaptive Server server versions 15.0 and later wants to choose the optimal packetsize. Use this to disable that feature.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;EnableServerPacketSize=0;

Max packet size

If you are using EnableServerPacketSize but still needs to set an upper limit you can specify that limit in multiples of 512 all the way to 65,536.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;RestrictMaximum PacketSize=4096;

Specify fetch results row count

Use this to control how many rows are received during fetch from server.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;FetchArraySize=50;

Default is 25 rows.

Set connection timeout

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;LoginTimeOut=5;

Specified in seconds. Default is 15 seconds. 0 means indefinite wait.

Connection pooling without ping

Default behaviour when obtaining and activating an idle connection from the pool is to ping the server to verify that the underlying connection is valid. Use this connection string to disable ping.

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Ping Server=false;

Use cursors

Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;UseCursor=1;

The driver does not use cursors by default. Use this option to activate cursors.

Sybase ASE OLE DB Provider

With Data Source .IDS file

Provider=Sybase ASE OLE DB Provider;Data source=myASE;

Note that you must create a Data Source .IDS file using the Sybase Data Administrator. These .IDS files resemble ODBC DSNs.

Adaptive Server Enterprise (ASE) 12.5

Provider=Sybase.ASEOLEDBProvider.2;Server Name=myASEserver;Server Port Address=5000;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

This one works only from Open Client 12.5 where the server port number feature works, allowing fully qualified connection strings to be used without defining any .IDS Data Source files.

Adaptive Server Enterprise (ASE) 15.0 alternative

This one is included because of reported problems with using the "Catalog" key which was solved by using the 12.5 syntax key "Initial Catalog" instead.

Provider=ASEOLEDB;Data Source=myASEserver:5000;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Sybase SQL Anywhere (former Watcom SQL) ODBC driver

Default local database engine

Driver={Sybase SQL Anywhere 5.0};DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=myUsername;Pwd=myPassword;Dsn="";

The empty DSN parameter could be critical if experiencing error 7778.

Remember to start the connection string with "odbc:" if you are using PHP PDO_ODBC

Database engine not running

Driver={Sybase SQL Anywhere 5.0};Uid=myUsername;Pwd=myPassword;EngineName=myServerAddress;DatabaseName=myDataBase;Start=c:\sqlany50\win32\dbclient.exe;DatabaseSwitches=-commandSwitch1 -commandSwitch2;
DatabaseFile=c:\pb\ex\psdemodb.db;AutoStop=NO;

If AutoStop is set to YES then the database is unloaded automatically as soon as there are no more open connections to it.

When the engine starts the database specified by DatabaseFile, the engine will use the supplied DatabaseSwitches as command line options to determine startup options for the database. The Sybase manual contains a List of DatabaseSwitch values.

Adaptive Server Enterprise ODBC driver

Adaptive Server Anywhere ODBC Driver

Standard

Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;PWD=myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=serverNameOrAddress);

Specific port

Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;PWD=myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=serverNameOrAddress:1234);

Multiple servers

Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;PWD=myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=Server1,Server2);