Embedded SQL

Embedded SQL

SQL provides a powerful declarative query language. Writing queries in SQL is usually much easier than coding the same queries in a general-purpose programming language. However, a programmer must have access to a database from a general- purpose programming language for at least two reasons:

1. Not all queries can be expressed in SQL, since SQL does not provide the full expressive power of a general-purpose language. That is, there exist queries that can be expressed in a language such as C, Java, or Cobol that cannot be expressed in SQL. To write such queries, we can embed SQL within a more powerful language.

SQL is designed so that queries written in it can be optimized automatically and executed efficiently — and providing the full power of a programming language makes automatic optimization exceedingly difficult.

2. Nondeclarative actions — such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface — cannot be done from within SQL. Applications usually have several components, and query- ing or updating data is only one component; other components are written in general-purpose programming languages. For an integrated application, the programs written in the programming language must be able to access the database.

The SQL standard defines embeddings of SQL in a variety of programming lan- guages, such as C, Cobol, Pascal, Java, PL/I, and Fortran. A language in which SQL queries are embedded is referred to as a host language, and the SQL structures per- mitted in the host language constitute embedded SQL.

Programs written in the host language can use the embedded SQL syntax to ac- cess and update data stored in a database. This embedded form of SQL extends the programmer’s ability to manipulate the database even further. In embedded SQL, all query processing is performed by the database system, which then makes the result of the query available to the program one tuple (record) at a time.

An embedded SQL program must be processed by a special preprocessor prior to compilation. The preprocessor replaces embedded SQL requests with host-language declarations and procedure calls that allow run-time execution of the database accesses. Then, the resulting program is compiled by the host-language compiler. To identify embedded SQL requests to the preprocessor, we use the EXEC SQL statement; it has the form

EXEC SQL <embedded SQL statement > END-EXEC

The exact syntax for embedded SQL requests depends on the language in which SQL is embedded. For instance, a semicolon is used instead of END-EXEC when SQL is embedded in C. The Java embedding of SQL (called SQLJ) uses the syntax

# SQL { <embedded SQL statement > };

We place the statement SQL INCLUDE in the program to identify the place where the preprocessor should insert the special variables used for communication between the program and the database system. Variables of the host language can be used within embedded SQL statements, but they must be preceded by a colon (:) to distin- guish them from SQL variables.

Embedded SQL statements are similar in form to the SQL statements that we de- scribed in this chapter. There are, however, several important differences, as we note here.

To write a relational query, we use the declare cursor statement. The result of the query is not yet computed. Rather, the program must use the open and fetch commands (discussed later in this section) to obtain the result tuples.

Consider the banking schema that we have used in this chapter. Assume that we have a host-language variable amount, and that we wish to find the names and cities of residence of customers who have more than amount dollars in any account. We can write this query as follows:

The variable c in the preceding expression is called a cursor for the query. We use this variable to identify the query in the open statement, which causes the query to be evaluated, and in the fetch statement, which causes the values of one tuple to be placed in host-language variables.

The open statement for our sample query is as follows:

EXEC SQL open c END-EXEC

This statement causes the database system to execute the query and to save the results within a temporary relation. The query has a host-language variable (:amount); the query uses the value of the variable at the time the open statement was executed.

If the SQL query results in an error, the database system stores an error diagnostic in the SQL communication-area (SQLCA) variables, whose declarations are inserted by the SQL INCLUDE statement.

An embedded SQL program executes a series of fetch statements to retrieve tuples of the result. The fetch statement requires one host-language variable for each attribute of the result relation. For our example query, we need one variable to hold the customer-name value and another to hold the customer-city value. Suppose that those variables are cn and cc, respectively. Then the statement:

EXEC SQL fetch c into :cn, :cc END-EXEC

produces a tuple of the result relation. The program can then manipulate the variables cn and cc by using the features of the host programming language.

A single fetch request returns only one tuple. To obtain all tuples of the result, the program must contain a loop to iterate over all tuples. Embedded SQL assists the programmer in managing this iteration. Although a relation is conceptually a set, the tuples of the result of a query are in some fixed physical order. When the program executes an open statement on a cursor, the cursor is set to point to the first tuple of the result. Each time it executes a fetch statement, the cursor is updated to point to the next tuple of the result. When no further tuples remain to be processed, the variable SQLSTATE in the SQLCA is set to ’02000’ (meaning “no data”). Thus, we can use a while loop (or equivalent loop) to process each tuple of the result.

We must use the close statement to tell the database system to delete the temporary relation that held the result of the query. For our example, this statement takes the form

EXEC SQL close c END-EXEC

SQLJ, the Java embedding of SQL, provides a variation of the above scheme, where Java iterators are used in place of cursors. SQLJ associates the results of a query with an iterator, and the next() method of the Java iterator interface can be used to step through the result tuples, just as the preceding examples use fetch on the cursor.

Embedded SQL expressions for database modification (update, insert, and delete) do not return a result. Thus, they are somewhat simpler to express. A database modification request takes the form

EXEC SQL < any valid update, insert, or delete> END-EXEC

Host-language variables, preceded by a colon, may appear in the SQL database- modification expression. If an error condition arises in the execution of the statement, a diagnostic is set in the SQLCA.

Database relations can also be updated through cursors. For example, if we want to add 100 to the balance attribute of every account where the branch name is “Perryridge”, we could declare a cursor as follows.

Embedded SQL allows a host-language program to access the database, but it pro- vides no assistance in presenting results to the user or in generating reports. Most commercial database products include tools to assist application programmers in creating user interfaces and formatted reports. We discuss such tools in Chapter 5 (Section 5.3).