Call-level interfaces

 

The final technique for sending SQL statements to the DBMS is through a call-level interface (CLI). A call-level interface provides a library of DBMS functions that can be called by the application program. Thus, instead of trying to blend SQL with another programming language, a call-level interface is similar to the routine libraries most programmers are accustomed to using, such as the string, I/O in C, or math libraries in Fortran. Note that DBMSs that support embedded SQL already have a call-level interface, the calls to which are generated by the precompiler. However, these calls are undocumented and subject to change without notice.

 

Call-level interfaces are commonly used in client/server architectures, in which the application program (the client) resides on one computer and the DBMS (the server) resides on a different computer. The application calls CLI functions on the local system, and those calls are sent across the network to the DBMS for processing.

 

A call-level interface is similar to dynamic SQL, in that SQL statements are passed to the DBMS for processing at run time, but it differs from embedded SQL as a whole because there are no embedded SQL statements and no precompiler is required.

 

Using a call-level interface typically involves the following steps:

 

  1. The application calls a CLI function to connect to the DBMS.

  2. The application builds an SQL statement and places it in a buffer. It then calls one or more CLI functions to send the statement to the DBMS for preparation and execution.

  3. If the statement is a SELECT statement, the application calls a CLI function to return the results in application buffers. Typically, this function returns one row or one column of data at a time.

  4. The application calls a CLI function to disconnect from the DBMS.

 

The X/Open Company and a consortium of leading software and hardware database vendors (known as the SQL Access Group (SAG), which is now a part of the X/Open Company) jointly developed a standard specification for a callable SQL interface. This interface, referred to as the X/0pen Call Level Interface (or X/Open CLI), focused on using function calls to invoke dynamic SQL statements, instead of blending SQL with another programming language.

 

In 1995, X/Open modified the original CLI standards specification and the newer specification became an International Organization for Standardization (ISO) standard shortly afterwards.

 

The goal of the X/Open CLI interface was to increase the portability of database applications by allowing them to become independent of any one DBMS's programming interface. CLI is similar to dynamic SQL in that SQL statements are passed to the DBMS for processing at runtime, but it differs from embedded SQL as a whole in that there are no embedded SQL statements and no precompiler is required.