Connect to ODBC Data Source (FireDAC)
Go Up to Database Connectivity (FireDAC)
This topic describes how to connect to ODBC data source.
Contents
Supported Versions
The FireDAC ODBC bridge driver supports the ODBC Level 2, 3 drivers.
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
.
OS X Client Software
FireDAC requires UnixODBC to be installed. As the required ODBC drivers.
Driver Linkage
To link the driver:
- drop a TFDPhysODBCDriverLink component from the "FireDAC Links" component palette page
- or include the FireDAC.Phys.ODBC unit in the uses clause.
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:
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