Connect to ODBC Data Source (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to ODBC data source.

Supported Versions

The FireDAC ODBC bridge driver supports the ODBC Level 2, 3 drivers.

Client Software

Windows Client Software

FireDAC requires the x86 or x64 ODBC driver to be installed according to ODBC specification. Please see the following ODBC documentation:

Note: Some ODBC drivers (such as Oracle ODBC driver v 11) have a bug that leads to an Access Violation on data fetching. To workaround this bug, set FetchOptions.RowsetSize to 1.

Linux and macOS Client Software

FireDAC requires the installation of unixODBC and any required ODBC driver.

Note: Some ODBC drivers for Linux, such as that of Teradata, include the DataDirect Driver Manager, which you must configure for unicode encoding.

Driver Linkage

To link the driver:

Connection Definition Parameters

There are two basic ways to connect to the ODBC data source:

  • Configure DSN using the ODBC Administrator control panel, then specify the DSN name in the DataSource parameter;
  • In the FireDAC Connection Editor (see Defining Connection (FireDAC) for details), specify the ODBCDriver parameter value, and then click the Wizard button. Follow the wizard instructions.

Note: The DataSource and ODBCDriver parameters are mutually exclusive. Each ODBC driver has its own set of connection parameters. They can be specified as the ODBCAdvanced parameter value.


DriverID=ODBC

Parameter Description Example value
ODBCDriver The name of the ODBC driver to use for connecting. If specified, other connection parameters must be specified in the ODBCAdvanced parameter. SQL SERVER
ODBCAdvanced Allows you to specify the ODBC connection parameter name=value pairs. They must be separated by ';'.
ODBCVersion Specifies the ODBC driver manager version. Possible values: 3.8 or 3.0 (default value). 3.8
DataSource The name of the existing DSN to use for connecting. MySAPDB
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
NumericFormat Controls the internal NUMBER binding mode. 'Binary' is the most effective mode. However, some ODBC drivers do not support the binary mode correctly. In this case, ensure that this parameter is set to 'String' (it is the default value). String
MetaDefCatalog Specifies the default catalog for the application. The design time code omits the catalog name in object names if it is equal to MetaDefCatalog. Northwind
MetaDefSchema Specifies the default schema for the application. The design time code omits the schema name in object names if it is equal to MetaDefSchema. dbo

For an ODBC MSSQL database, set the ODBCAdvanced FDConnection parameter value to MARS_Connection=YES. Otherwise, when you call the Open method of FDTable, you will receive the following error:

[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another command.

Use Cases

  • Connect to PostgreSQL:
DriverID=ODBC
User_Name=postgres
Password=marlboro
ODBCDriver=PostgreSQL ANSI
ODBCAdvanced=SERVER=localhost;PORT=5432;DATABASE=addemo
  • Connect to Sybase Adaptive Server Enterprise:
DriverID=ODBC
ODBCDriver=Adaptive Server Enterprise
ODBCAdvanced=server=da;port=5000;quotedidentifier=1
Database=addemo
User_Name=sa
MetaDefCatalog=addemo
MetaDefSchema=dbo
  • Connect to Informix Dynamic Server:
DriverID=ODBC
ODBCDriver=IBM INFORMIX ODBC DRIVER
User_Name=informix
Password=informix2
Database=sysuser
ODBCAdvanced=HOST=DA;SRVR=ol_svr_custom;SERV=svc_custom;PRO=olsoctcp;CLOC=en_US.CP1252;DLOC=en_US.819

See Also