Connect to Sybase SQL Anywhere (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to Sybase SQL Anywhere.

Supported Versions

The FireDAC native driver supports Sybase SQL Anywhere version 5 and later.

Client Software

Windows Client Software

FireDAC requires one of the Sybase SQL Anywhere x86 or x64 ODBC drivers to be installed on the workstation:

  • "Adaptive Server Anywhere" ODBC driver to connect to v 5-7.
  • "SQL Anywhere N" ODBC driver, where N is the DBMS major version number (e.g. 8, 9, 10).

The preferred driver is "SQL Anywhere N". The ODBC driver is part of Sybase SQL Anywhere SDK. You can download it from here.

If the SQL Anywhere ODBC driver has not been installed properly, an exception is raised when you try to connect:

[FireDAC][Phys][ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

If your application is using SQL Anywhere service components, such as TFDASABackup, then it is required to install the following DLL's on the workstations:

  • DBTOOL<N>.DLL
  • DBLIB<N>.DLL

N is a version of your SQL Anywhere.

OS X Client Software

FireDAC requires:

  • the UnixODBC (libodbc.dylib) x86 ODBC driver manager library.
  • the SQL Anywhere (libdbodbcNN.dylib) x86 ODBC driver.

SQL Anywhere client software for OS X can be downloaded from here. After installing:

  • add the following to /usr/local/etc/odbcinst.ini:
[SQL Anywhere 16]
Description=SQL Anywhere v 16
Driver=/Applications/SQLAnywhere12/System/lib32/libdbodbc16_r.dylib
Setup=/Applications/SQLAnywhere12/System/lib32/libdbodbc16_r.dylib
UsageCount=1
  • remove libodbcinst.dylib and libodbcinst.dylib.1 links from /Applications/SQLAnywhere12/System/lib32 folder, as they conflict with UnixODBC libodbcinst.dylib. This action is recommended by the Sybase development team.
  • run /Applications/SQLAnywhere12/System/bin32/sa_config.sh

Linux Client Software

FireDAC requires the unixODBC 64-bit ODBC driver manager library and the SQL Anywhere 64-bit ODBC driver. To install SQL Anywhere ODBC driver on Linux, run these commands:

wget http://d5d4ifzqzkhwt.cloudfront.net/sqla16client/sqla16_client_linux_x86x64.tar.gz
tar -xavf sqla16_client_linux_x86x64.tar.gz
cd client1600
sudo ./setup -nogui -I_accept_the_license_agreement -silent
sudo rm /opt/sqlanywhere16/lib64/libodbcinst.so
sudo rm /opt/sqlanywhere16/lib64/libodbcinst.so.1
/opt/sqlanywhere16/bin64/sa_config.sh
echo [SQL Anywhere 16] > asa.driver.template
echo Description=SQL Anywhere ODBC Driver v 16 >> asa.driver.template
echo Driver=/opt/sqlanywhere16/lib64/libdbodbc16_r.so >> asa.driver.template
echo Setup=/opt/sqlanywhere16/lib64/libdbodbc16_r.so >> asa.driver.template
sudo odbcinst -i -d -f asa.driver.template

Driver Linkage

To link the driver:

Connection Definition Parameters

To connect to a SQL Anywhere DBMS, most applications require that you specify DriverID, Server, Database, OSAuthent, User_Name, and Password (see Defining Connection (FireDAC) for details).

DriverID=ASA

Parameter Description Example value
Server Specifies the name of a running database server to which you want to connect. ASASrv
Database Identifies a loaded database to which a connection needs to be made when connecting to a database that is already running.
OSAuthent

Controls authentication mode:

  • Yes -- an integrated login is attempted;
  • No -- the DBMS login is used. This is the default value.
No
User_Name Specifies the user ID used to log in to the database if OSAuthent=No. dba
Password Specifies the user password used to log in to the database if OSAuthent=No.

Note that the passwords with both '{' and '}' are not supported.

sql
DatabaseFile Indicates which database file you want to load and connect to when starting a database that is not already running. To connect to an already-running database, use the Database parameter.
Compress

Controls network traffic compression:

  • Yes -- to turn compression on for a connection.
  • No -- to turn compression off. This is the default value.
Yes
Encrypt Encrypts packets sent between the client application and the server using transport-layer security or simple encryption. The value syntax is { NONE | SIMPLE | TLS( TLS_TYPE=cipher; [ FIPS={ Y | N }; ] TRUSTED_CERTIFICATES=public-certificate ) }
  • tls(tls_type=rsa;fips=n;trusted_certificates=rsaserver.crt)
  • simple
LoginTimeout Controls the amount of time, in seconds, before an application times out while attempting to establish a connection (0 specifies an infinite wait). 30
ApplicationName Assists administrators in identifying the origin of particular client connections from a database server. The string can be retrieved using the statement: SELECT CONNECTION_PROPERTY('AppInfo') AllBooks
ODBCAdvanced Allows you to specify any other additional ODBC connection parameter value. The default value is "CommLinks=ShMem,TCP". Please note that, if CommLinks is specified, the server will not auto start. AutoStart=Yes;CharSet=Windows-1251
MetaDefCatalog Default database name. The Design time code excludes the catalog name from the object name if it is equal to MetaDefCatalog. addemo
MetaDefSchema Default schema name. The Design time code excludes the schema name from the object name if it is equal to MetaDefSchema. dba

Use Cases

Auto start the local server and open the database file "C:\sybase\addemo_asa10.db":

DriverID=ASA
ODBCAdvanced=AutoStart=Yes
DatabaseFile=C:\sybase\addemo_asa10.db
User_Name=dba
Password=sql
MetaDefSchema=dba
  • Connect to a default local server
DriverID=ASA
User_Name=dba
Password=sql
MetaDefSchema=dba
  • Connect to the database ADDemo, running on server instance ASASrv:
DriverID=ASA
Server=ASASrv
Database=ADDemo
User_Name=dba
Password=sql
MetaDefSchema=dba
  • Connect to the database ADDemo, running on the server instance ASASrv in another network:
DriverID=ASA
ODBCAdvanced=CommLinks=tcpip(host=227.12.66.1)
Server=ASASrv
Database=ADDemo
User_Name=dba
Password=sql
MetaDefSchema=dba

See Also