A Closer Look Into the ODBC World

What is ODBC

ODBC stands for Open Database Connectivity. It is an industry standard that defines a single application programming interface (the ODBC API) to access a variety of “data sources”. Data sources can range from database servers such as Microsoft SQL Server, Oracle, PostgreSQL or MySQL and even simpler data file formats such as Excel, or even .TXT or .CSV files. The benefit of having one interface that can access many data sources is that it allows business application programs to be database independent. Using the resources of ODBC and its data source specific drivers, the DataFlex ODBC driver connects applications to various database backends transparently without code changes.

“ODBC accomplishes DBMS independence by using an ODBC driver as a [standard] translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS*” using a database specific ODBC driver. (*Wikipedia)

By using ODBC, applications gain flexibility to access different types of data from the same source code.

Basic ODBC Concepts

Database-Specific ODBC Drivers

Database specific ODBC drivers translate standard ODBC API calls from ODBC-enabled applications like DataFlex to the specifics needed to connect to and access different data sources.

When the data source is a database server, the ODBC database driver may be referred to as the ODBC database client driver or simply the client driver. Most database servers provide a specific version of their client drivers with a related version of the server as well as in a separate installation program to set up the local client environments. Even though older client drivers may be able to access newer database servers, to take full advantage of all features that a server provides, the matching version of client driver must be installed.

Data Sources and ODBC Data Source Names

“A data source is simply the source of the data. It can be a file, a particular database on a DBMS, or even a live data feed. The data might be located on the same computer as the program, or on another computer somewhere on a network.*”
For use with ODBC, data sources are defined in a Data Source Name (DSN) created and stored using Microsoft’s ODBC Administrator program. A DSN is “a specific set of data, the information required to access that data, and the location of the data source.*” (* source: Microsoft)

Applications using ODBC for database access will normally use a DSN to access and process data stored in the backend.

Microsoft’s ODBC Administrator

The Microsoft® ODBC Data Source Administrator manages ODBC database drivers and data sources. The administrator program is on the Windows Control Panel under Administrative Tools. Beginning in Windows 8, the Control Panel icon is named ODBC Data Sources, and on 64-bit operating systems there are 32-bit and 64-bit versions.

When configuring DataFlex applications, DSNs must be defined using the 32-bit version of the ODBC Administrator. When running ODBC Administrator (odbcad32.exe), make sure you run the one located at %windir%\SysWOW64\ (32 bits) and not the one at %windir%\system32\ (64 bits).

DataFlex and ODBC

Data Access Worldwide provides an ODBC driver that enables DataFlex applications to interface with the ODBC API of the Driver Manager and, through it, any supported data source(s). DataFlex’s ODBC driver is an optionally licensed connectivity component available for WebApp Server or Windows Client deployment licenses. Examples of ODBC supported database servers that DataFlex can access via ODBC are: Oracle, MySQL and PostgreSQL.

The following files make up the required and optional components for DataFlex applications to use ODBC data sources:

The .INT configuration files shown above are used with DataFlex to define and control the ODBC database environment. ODBC_DRV_Database.int is generic and used for all ODBC connections. Optional .INT files can be database specific (<DatabaseBackendName>.int), to optimize a specific database backend.

The ODBC_DRV.int is DataFlex’s global ODBC configuration file; all settings included in that file will be applied when the DataFlex ODBC Driver (odbc_drv.dll) is loaded to define how the driver should operate.

DataFlex ODBC Driver

The diagram below illustrates the DataFlex ODBC Driver’s position in an ODBC environment:

Conclusion

ODBC is a powerful, versatile industry standard method to access application data sources. By using DataFlex ODBC Driver with other ODBC resources and data source connection options, applications can use the standard for reliable, high performance database management options like Oracle, MySQL or PostgreSQL or other data sources such as Excel spreadsheets, .CSV files and Microsoft Outlook data to easily integrate one or multiple data sources into business solutions.

Support for DataFlex ODBC Driver and all connectivity options is available in the Data Access Worldwide “Database Connectivity” sub-forum and according to our general support policies.

Resources:

• https://docs.microsoft.com/en-us/sql/odbc/microsoft-open-database-connectivity-odbc
• https://docs.microsoft.com/en-us/sql/odbc/reference/odbc-programmer-s-reference
• https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html
• https://msdn.microsoft.com/en-us/library/w558zwfc.aspx
• https://docs.microsoft.com/en-us/sql/odbc/admin/odbc-data-source-administrator
• https://docs.microsoft.com/en-us/sql/odbc/reference/what-is-odbc
• https://docs.microsoft.com/en-us/sql/odbc/reference/odbc-and-the-standard-cli
• https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/standards-compliant-applications-and-drivers
• http://www.opendatabasealliance.com/history.htm
• https://technet.microsoft.com/en-us/library/security/ms04-003.aspx