What is ODBC? #
ODBC stands for Open Database Connectivity. It is a specification for an application programming interface (API) for accessing any database management system (DBMS), such as Microsoft SQL Server, Oracle, MySQL etc.
Although Microsoft released the first product for ODBC, it was actually created and released by the SQL Access Group. It is, therefore, independent of any operating system or DBMS, and actually uses The Open Group standard SQL (structured query language) Call-Level Interface (CLI) as a standard for accessing the data. It also supports ISO/IEC.
ODBC enables applications to use SQL requests to query databases , no matter which interfaces the targeted databases adopt, as ODBC can convert the request into a call that the database can understand. This makes it extremely useful for organisations that manage and utilise multiple databases or applications.
As long as the data source has an ODBC driver, it is possible to transfer data. The majority of ERP systems and relational or nonrelational database suppliers, as well as other software products, support ODBC, making it an extremely efficient and cost-effective method for reading and writing data between databases.
The data source may be on the same system as the application, on a different system within the company’s network or in the cloud.
How does ODBC work? #
ODBC consists of a number of functions that enable applications to access data within a database.
To access the data an application needs to create connections to the ODBC data sources by creating ODBC API function calls, such as a SQL statement, to connect to a SQL Server or MySQL database.
Each DBMS requires a different ODBC driver. Therefore, an application that uses ODBC can connect to different DBMSs as long as the appropriate driver is installed. An ODBC driver is a software component that provides an interface between an ODBC application and a specific DBMS.
The dedicated ODBC tool or driver manager then needs to load the drivers for each application as the applications have to call the functions in these drivers to access data in a way that is independent of any DBMS or operating system.
Once the ODBC drivers have been selected by the ODBC tool or driver manager, the manager then uses a configuration file – Data Source Name (DSN) – to define the connection information for a specific database. A DSN contains information such as the name of the DBMS, the location of the database and the authentication credentials.
Once the DSN is configured, the ODBC driver is then able to manage the ODBC calls and submit SQL requests to the data source. The driver translates the API calls into commands that the targeted database can understand, authenticates the user and sets up the necessary communication channels.
After the connection is established, the application can execute SQL statements to read, modify or delete data from the database. The ODBC driver can then retrieve the data from the DBMS and return it to the application.
If an intermediary automation system is in place, the data can then be further exposed and consumed by a number of other tools for further processing, such as converting, formatting, filtering or running reports, before pushing the data into the desired application.
For more information on ODBC and how to use the Database Query (ODBC) tool, download the whitepaper below.
