Page 87 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Declaring Variables and Exceptions

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.
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 arid fetch commands (discussed later in this
section) to obtain the result tuples.

Consider the banking schema. 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:
EXEC SQL

declare c cursor for
select customer-name, customer-city
.from deposit, customer
where deposit.customer-name = customer.customer-name and
deposit.balance > : amount
END-EXEC

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-EXCE
This statement causes the database system to execute the query and to save the results within a
temporary relation. 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.
A series of fetch statements is executed to make tuples of the result available to the program. 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 en and cc, respectively. A tuple of the
result relation is obtained by the statement:
EXEC SQL fetch c into : en, : cc END-EXEC

The program can then manipulate the variables en and cc using the feature of the host
programming language.
The close statement must be used 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

80 LOVELY PROFESSIONAL UNIVERSITY