When a DBMS supports a function call interface, an application program communicates with the DBMS exclusively through a set of calls that are collectively known as an application programming interface, or API. The basic operation of a typical DBMS API is illustrated in Figure 19-1.
- The program begins its database access with one or more API calls that connect the program to the DBMS and often to a specific database.
- To send a SQL statement to the DBMS, the program builds the statement as a text string in a buffer and then makes an API call to pass the buffer contents to the DBMS.
- The program makes API calls to check the status of its DBMS request and to handle errors.
- If the request is a query, the program uses API calls to retrieve the query results into the program’s buffers. Typically, the calls return data a row at a time or a column at a time.
- The program ends its database access with an API call that disconnects it from the DBMS.
A SQL API is often used when the application program and the database are on two different systems in a client/server architecture, as shown in Figure 19-2. In this configuration, the code for the API functions is located on the client system, where the application program executes. The DBMS software is located on the server system, where the database resides. Calls from the application program to the API take place locally within the client system, and the API code translates the calls into messages that it sends to and receives from the DBMS over a network. A SQL API offers particular advantages for a client/server architecture because it can minimize the amount of network traffic between the API and the DBMS.
The early APIs offered by various DBMS products differed substantially from one another. Like many parts of the SQL language, proprietary SQL APIs proliferated long before there was an attempt to standardize them. In addition, SQL APIs tend to expose the underlying capabilities of the DBMS more than the embedded SQL approach, leading to even more differences. Nonetheless, all of the SQL APIs available in commercial SQL products are based on the same fundamental concepts illustrated in Figures 19-1 and 19-2. These concepts also apply to the ODBC API and more recent ANSI/ISO standards based on it.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.