EXEC SQL <embedded SQL statement > END-EXEC in Software

Generating Code 128C in Software 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 distinguish them from SQL variables Embedded SQL statements are similar in form to the SQL statements that we described 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

The McGraw Hill Companies, 2001

4

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 nd the names and cities of residence of customers who have more than amount dollars in any account We can write this query as follows:

declare c cursor for select customer-name, customer-city from depositor, customer, account where depositorcustomer-name = customercustomer-name and accountaccount-number = depositoraccount-number and accountbalance > :amount

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:

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:

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 xed physical order When the program executes an open statement on a cursor, the cursor is set to point to the rst 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