Previous Page TOC Index Next Page



A SHORT HISTORY OF SQL


This chapter provides a brief history of SQL and describes programmatic interfaces to SQL. For more information about SQL, see "Where to Find Additional Information" in "Welcome."

SQL Background Information

SQL, or Structured Query Language, is a widely accepted industry standard for data definition, data manipulation, data management, access protection, and transaction control. SQL originated from the concept of relational databases. It uses tables, indexes, keys, rows, and columns to identify storage locations.

Many types of applications use SQL statements to access data. Examples include ad hoc query facilities, decision support applications, report generation utilities, and online transaction processing systems.

SQL is not a complete programming language in itself. For example, there are no provisions for flow control. SQL is normally used in conjunction with a traditional programming language.

ANSI 1989 Standard

SQL was first standardized by the American National Standards Institute (ANSI) in 1986. The first ANSI standard defined a language that was independent of any programming language.

The current standard is ANSI 1989, which defines three programmatic interfaces to SQL:

The most popular programmatic interface has been embedded SQL.

Embedded SQL

Embedded SQL allows programmers to place SQL statements into programs written in a standard programming language (for example, COBOL or Pascal), which is termed the host language. SQL statements are delimited with specific starting and ending statements defined by the host language. The resulting program contains source code from two languages — SQL and the host language.

When compiling a program with embedded SQL statements, a precompiler translates the SQL statements into equivalent host language source code. After precompiling, the host language compiler compiles the resulting source code.

In the ANSI 1989 standard, the embedded SQL only supports static SQL. Static SQL has the following characteristics:

Static SQL is efficient; SQL statements can be precompiled prior to execution and run multiple times without recompiling. The application is bound to a particular DBMS when it is compiled.

Static SQL cannot defer the definition of the SQL statement until run time. Therefore, static SQL is not the best option for client-server configurations or for ad hoc requests.

Current ANSI Specification

SQL-92 is the most recent ANSI specification, and is now an international standard. SQL-92 defines three levels of functionality: entry, intermediate, and full. SQL-92 contains many new features, including:

Dynamic SQL

Dynamic SQL, which is included in the most recent ANSI specification, allows an application to generate and execute SQL statements at run time.

Dynamic SQL statements can be prepared. When a statement is prepared, the database environment generates an access plan and a description of the result set. The statement can be executed multiple times with the previously generated access plan, which minimizes processing overhead.

Parameters can be included in dynamic SQL statements. Parameters function in much the same way as host variables in embedded SQL. Prior to execution, assign values to the place held by each parameter. Unlike static SQL, parameters do not require length or data type definition prior to program compilation.

Dynamic SQL is not as efficient as static SQL, but is very useful if an application requires:

Call Level Interface

A Call Level Interface (CLI) for SQL consists of a library of function calls that support SQL statements. The ODBC interface is a CLI.

A CLI is typically used for dynamic access. The CLI defined by the X/Open and SQL Access Group — and therefore the ODBC interface — is similar to the dynamic embedded version of SQL described in the X/Open and SQL Access Group SQL CAE specification (1992).

The ODBC interface is designed to be used directly by application programmers, not to be the target of a preprocessor for embedded SQL.

A CLI is very straightforward to programmers who are familiar with function libraries. The function call interface does not require host variables or other embedded SQL concepts.

A CLI does not require a precompiler. To submit an SQL request, place an SQL command into a text buffer and pass the buffer as a parameter in a function call. CLI functions provide declarative capabilities and request management. Obtain error information as for any function call — by return code or error function call, depending on the CLI.

A CLI allows for specification of result storage before or after the results are available. Results can be determined and appropriate action taken without being limited to a specific set of data structures that were defined prior to the request. Deferral of storage specification is called late binding of variables.

For a comparison between embedded SQL statements and the ODBC call level interface, see Appendix E, "Comparison Between Embedded SQL and ODBC."

Interoperability

Interoperability for call level interfaces can be addressed in the following ways:

The second approach allows drivers to shield clients from database functionality differences, database protocol differences, and network differences. ODBC follows the second approach. ODBC can take advantage of standard database protocols and network protocols, but does not require the use of a standard database protocol or network protocol.

Previous Page TOC Index Next Page

Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.