Different aspects of databases and DB APIs (DataBase Application Programming Interface) may be called different things depending on the situation. This is especially true if multiple DB APIs are used. EG:
- Data is needed by an application (a Data Consumer).
- The code utilizes ADO (as a Data Consumer).
- The ADO talks with OLE DB (a Data Provider in this case).
- The OLE DB talks with ODBC (a Data Provider in this case).
- The ODBC utilizes an ODBC DSN (a Data Source in this case).
- The ODBC DSN enables connection with SQL Server (a Data Source).
All of the above DB APIs eventually talk to the DBMS using SQL (See also my section on SQL.). Some of the DBMSs also allow the DB API to be skipped altogether by allowing SQL code to be embedded directly into the code (usually in C or COBOL). Note that the DB API is also used to examine and utilize data returned to the DB API via SQL.
Different DBMSs (DataBase Management Systems) often have their own proprietary APIs by which a developer can talk in code to the DBMS. EG:
- The native DB API for Microsoft Access is JET (Joint Engine Technology).
- The native DB API for Microsoft SQL Server (prior to version 7) is DB Library.
It is possible to develop directly in the DB API. However the DB API is frequently a database CLI (Call Level Interface). It is more common, and easier, to develop using a database object API instead of a CLI.
Database Connection APIs
In addition, the major DBMS also support at least one standard API. Here are three standard DB APIs:
- ODBC (Open DataBase Connectivity). The biggest and most common DB API. It was developed by Microsoft but they turned it over to the SAG (SQL Access Group) consortium. If the Web had not come around, ODBC and SQL-92 would have been numero uno. An application would need the appropriate ODBC driver for the particular DBMS type. In Windows, the ODBC drivers are managed with an applet in the Control Panel called (and this may vary with your system configuration) "ODBC Data Sources" which does a number of things including displaying which ODBC drivers are installed on the machine and allows you to see and setup DSNs (Data Source Names). DSNs consolidate a lot of the info needed to access a particular ODBC driver for a particular data source (i.e. to access a particular database). A DSN batches this info and the DSN can be called upon by name. There are three flavors of DSNs:
- User DSN. These are data sources usable by the current user on the current machine.
- System DSN. These are data sources usable to all users on the current machine, even NT services such as IIS or other apps.
- File DSN. These are data sources that enable users to connect to data providers.
- JDBC (Java Database Connectivity). Developed by Sun and sed by Java programs. Just as with ODBC, this DB API also needs the appropriate JDBC driver for the particular DB type. JDBC has the power to access ODBC drivers too.
- OLE DB (Object Linking and Embedding DataBase). The cornerstone of MDAC. The OLE DB allows access to OLE DB service components (that can provide process queries, manage cursors, and perform business services), as well as OLE DB providers/drivers for the following types of data sources:
- ODBC compliant databases
- Relational databases
- Non-relational data
- Text files
- HTML pages
- Sources over networks and the Web.
Database Object APIs
What I refer to as "Database Object APIs" are the objects manipulated by developers to access data in databases.
Microsoft has a slew of database object APIs. (I'm sure Sun and other companies have all sorts of but I've worked primarily with Microsoft products.) Here they are in historical order:
DAO (Data Access Objects). Especially for JET. Microsoft actually has an object model called ODBCDirect which is just like DAO except that it can access ODBC.
- DBEngine (Either Microsoft Jet or ODBCDirect)
RDO Object Model
RDO (Remote Data Objects). Especially for ODBC.
ADO Object Model
ADO (ActiveX Data Objects). Used to access OLE DB Data Providers. Note that since OLE DB can access JET and ODBC, this is the primary Microsoft database object API.