Using Shared Variables

Using Shared Variables

A shared variable can be used in SQL statements in places where we expect or allow a constant. Remember that shared variables are preceded by a colon when so used. Here is an example in which we use the variables of "System Aspects of SQL" Example 1, as components of a tuple to be inserted into relation Studio.

Example 1 : In Figure 1 is a sketch of a C function getStudio that prompts the user for the name and address of a studio, reads the responses, and inserts the appropriate tuple into Studio. Lines (1) through (4) are the declarations we learned about in "System Aspects of SQL" Example 1. We omit the C code that prints requests and scans entered text to fill the two arrays studioName and studioAddr.

Then, in lines (5) and (6) is an embedded SQL statement that is a conventional INSERT statement. This statement is preceded by the keywords EXEC SQL to indicate that it is indeed an embedded SQL statement rather than ungrammatical C code. The preprocessor suggested in "System Aspects of SQL" Figure 1. will look for EXEC SQL to detect statements that must be preprocessed.

The values inserted by lines (5) and (6) are not explicit constants, as they were in previous examples such as in "Database Modifications" Example 1. Rather, the values appearing in line (6) are shared variables whose current values become components of the inserted tuple.

There are many kinds of SQL statements besides an INSERT statement that can be embedded into a host language, using shared variables as an interface. Each embedded SQL statement is preceded by EXEC SQL in the host-language program and may refer to shared variables in place of constants. Any SQL statement that does not return a result (i.e., is not a query) can be embedded. Examples of embeddable SQL statements include delete- and update-statements and those statements that create, modify, or drop schema elements such as tables and views.

Using shared variables to insert a new studio

On the other hand, select-from-where queries are not embeddable directly into a host language, because of the "impedance mismatch." Queries produce sets of tuples as a result, while none of the major host languages supports a set data type directly. In this way, embedded SQL must use one of two mechanisms for connecting the result of queries with a host-language program.

1. A query that produces a single tuple can have that tuple stored in shared variables, one variable for each component of the tuple. To do so, we use a modified form of select-from-where statement called a single-row select.

2. Queries producing more than one tuple can be executed if we declare a cursor for the query. The cursor ranges over all tuples in the answer relation, and each tuple in turn can be fetched into shared variables and processed by the host-language program.

We shall consider each of these mechanisms in turn.

Single-Row Select Statements

The form of a single-row select is the same as an ordinary select-from-where statement, except that following the SELECT clause is the keyword INTO and a list of shared variables. These shared variables are preceded by colons, as is the case for all shared variables within an SQL statement. If the result of the query is a single tuple, this tuple's components become the values of these variables. If the result is either no tuple or more than one tuple, then no assignment to the shared variables are made, and an appropriate error code is written in the variable SQLSTATE.

Example 2 : We shall write a C function to read the name of a studio and print the net worth of the studio's  president. A sketch of this function is shown in Figure 2. It begins with a declare section, lines (1) through (5), for the variables we shall need. Next, C statements that we do not show explicitly obtain a studio name from the standard input.

Lines (6) through (9) are the single-row select statement. It is quite similar to queries we have already seen. The two differences are that the value of variable studioName is used in place of a constant string in the condition of line (9), and there is an INTO clause at line (7) that tells us where to put the result of the query. In this case, we expect a single tuple, and tuples have only one component, that for attribute netWorth. The value of this one component of one tuple is stored in the shared variable presNetWorth.

A single-row select embedded in a C function