SQL Bible [Electronic resources] نسخه متنی

Embedded (Static) SQL

The idea of embedded SQL is simple —
implant the SQL statements into the program written in a traditional procedural
language, like C, COBOL, FORTRAN, or Pascal. This approach enables you to
combine SQL's capability to work with relational database tables (and more)
with all the power of a procedural language (variables, parameters,
subprograms, iterations, conditional statements, etc.).

Embedded SQL and SQL99
standards

Embedded SQL is a classic example of how
de-facto IBM-based standards became SQL99 standards. As we mentioned before,
IBM developed and implemented embedded SQL principles in the early 1980s; since
then most vendors were simply following these standards with just slight
variations. The standards were recognized first by SQL92 and later by SQL99
specifications.

All the "big three" vendors featured in
this book support the ANSI/ISO standards to some extent. Oracle provides
precompilers for C/C++, COBOL, ADA, PL/I, and FORTRAN; DB2 supports embedded
SQL for C, COBOL, PL/I, FORTRAN, and Java; and MS SQL Server allows you to use
only C language.

The terminology used in this chapter
follows commonly accepted conventions. However, it may look confusing to some.
For example, we are talking about embedded and dynamic SQL as if they were two
separate topics, whereas dynamic SQL is rather an extension to the embedded
SQL; dynamic SQL simply adds some functionality and extends the embedded SQL
syntax. It is more appropriate to talk about two variations of the embedded SQL
— static and dynamic.

Another commonly misinterpreted issue
is the differences between the embedded SQL and the vendor-specific procedural
SQL extensions. For example, Oracle allows you to write stored procedures using
Java language, or, as an alternative, you could create a C program with
embedded SQL. The main difference between a stored procedure and a host program
is that the stored procedure is a compiled code stored inside the database,
whereas a host program with embedded SQL resides outside the database as a
binary file somewhere in the operating system.

Embedded SQL basic elements

Even though
the embedded SQL syntax differs slightly for different implementations (mostly
because of the proprietary SQL syntax variations), the concept and the elements
are the same. You have to declare host variables, establish database
connection, execute one or more SQL statements, get the results, and perform
some error handling when necessary.

Host variables

Host variables are the key to
communication between your host program and the server. They are used to pass
data to RDBMS and/or to receive the information back from it. In other words,
the host variables are necessary to link the SQL statements with the host
language (C, COBOL, etc.) statements. For example, your C program could pass
parameters accepted from the command line into an SQL statement using
previously declared host variables.

Host variables
declaration

The declaration of the host variables
is done in the
DECLARE section in the following
form:

EXEC SQL BEGIN DECLARE
SECTION <language-specific delimiter> <language-specific variable
declaration>,... EXEC SQL END DECLARE SECTION <language-specific
delimiter>

The following three examples show how
to declare a character-string host variable that can hold fifty characters and
another variable of type integer in C, COBOL, and FORTRAN, respectively:

Example 1

EXEC SQL BEGIN DECLARE
SECTION; char custname [51]; int custid; ... EXEC SQL END DECLARE
SECTION; 

Example 2

EXEC SQL BEGIN DECLARE
SECTION END-EXEC. 01 CUSTNAME PIC X(50). 01 CUSTID PIC S9(9) COMP. ... EXEC SQL
END DECLARE SECTION END-EXEC. 

Example 3

EXEC SQL BEGIN DECLARE
SECTION CHARACTER*50 CUSTNAME INTEGER*4 CUSTID ... EXEC SQL END DECLARE
SECTION

As you can notice, the delimiter in C
is a semicolon (;), COBOL uses the keyword
END-EXEC, and no delimiter is
necessary in FORTRAN. The host data types are usually declared according to the
host-language variable declaration rules using data types valid for this
specific language. SQL99 rules require host variable names to be no longer than
eighteen characters, but most RDBMS allow longer names.

Input versus output host
variables

As we mentioned before, the purpose
of using host variables is to provide communication between the host program
and the RDBMS. Generally, you can divide host variables into two main
categories: input and output host variables. The input host variables transfer
information from the host program to RDBMS, and the output host variables
receive data from the database. Host variables can be used anywhere an
expression can be used; however, in SQL statements, they must be prefixed with
a colon (:), to set them apart from database
schema names.

Host variables and SQL data
types correspondence

The correspondence between host
variables and SQL data types is a complex and unintuitive topic. The fact that
some host-language data types share their names with incompatible SQL data
types adds even more confusion.

Table 15-1 shows the
correspondence between SQL99 data types and some host-language data types (C,
COBOL, and FORTRAN). You will need to refer to vendor-specific documentation
for more information.

Dealing with NULLs and indicator
variables

The concept of null is foreign to
most programming languages. To deal with that issue, embedded SQL introduces
the concept of indicator variables. You can associate any
host variable with an optional indicator variable. Every time an SQL statement
uses the host variable, a result is stored in its associated indicator
variable, providing you the capability to monitor host variables and treat them
accordingly.

Indicator variables must be declared
in the host variable declaration sections, just like any other host variables.
The appropriate data type for an indicator variable is
short in C;
PIC
S9(4)
COMP in COBOL; and FORTRAN programs
could use
INTEGER*2.

Indicator variables must be placed
immediately after the corresponding host variable specified in the Embedded SQL
statement (without separating them with a comma); they can be used with both
input and output host variables. The first scenario is typically used to assign
NULL values in DML statements while
the second situation allows you to handle nulls received by
SELECT
INTO (discussed later in this
chapter) or as the result of a cursor fetch.

Tables 15-2 and
15-3 show how the value of an indicator variable
affects the related host variable.

In the following examples (written in
C and COBOL, respectively), we declare host variable
price (PRICE) along with indicator variable
priceind (PRICEIND), assigned the value of
-1 to the latter, and then used the
variables in the
UPDATE statement to modify the price
for product
990 to be
NULL.

EXEC SQL BEGIN DECLARE
SECTION; double price; short priceind = -1; ... EXEC SQL END DECLARE SECTION;
... EXEC SQL UPDATE PRODUCT SET PROD_PRICE_N = :price:priceind WHERE PROD_ID_N
= 990; ... 

EXEC SQL BEGIN DECLARE
SECTION END-EXEC. 01 PRICE V9(9) COMP-3. 01 PRICEIND PIC S9(4) COMP VALUE -1.
... EXEC SQL END DECLARE SECTION END-EXEC. ... EXEC SQL UPDATE PRODUCT SET
PROD_PRICE_N = :PRICE:PRICEIND WHERE PROD_ID_N = 990 END-EXEC.
...

Database connection

Before your program can start doing
anything else, it has to establish connection to the target database. The
embedded SQL keyword
CONNECT is used for that purpose; the
grammar again is vendor-specific with generic syntax specified below:

EXEC SQL CONNECT
<connect_string> <language-specific delimiter>

The
<connect_string> is different for
different RDBMS, and the delimiter is host-language-specific. For example, to
connect to Oracle using Pro*C (Oracle's precompiler for C/C++), you can use
this embedded SQL statement (assuming the host variables
username and
password of appropriate data types are
properly declared and some meaningful values are assigned to them):

EXEC SQL CONNECT :username
IDENTIFIED BY :password; 

The
<connect_string> in a C program
that connects to DB2 UDB would be slightly different, as shown in the following
example:

EXEC SQL
CONNECT TO :database USER :username USING :password;

The syntax to connect to MS SQL Server
is quite similar, except the username and the password must be concatenated
with an intervening dot (.):

strcat(strcat(username,
"."), password); EXEC SQL CONNECT TO :database USER :username;

The first line of code is simply using
the C function
strcat() to represent the username and
password in the form
username.password and store the result
in the host variable username; the second line performs the database
connection.

All the previous examples are using C
as the host language; the syntax for COBOL connecting to Oracle and DB2 UDB,
respectfully, follows:

Oracle
example

EXEC SQL CONNECT :USERNAME
IDENTIFIED BY :PASSWORD END-EXEC.

DB2 UDB example

EXEC SQL CONNECT TO
:DATABASE USER :USERNAME USING :PASSWORD END-EXEC.

These examples assume three host
variables:
DATABASE,
USERNAME, and
PASSWORD.

Data retrieval and SELECT INTO
statement

One of the major advantages of
embedded SQL over nonprocedural SQL is the ability to retrieve data from a
database server into host variables and use that data within the host program.
A special variation of the
SELECT statement,
SELECT
INTO, is used to accomplish this task.
The syntax is very similar to that of the regular SQL
SELECT statement; the main difference
is that in the
INTO clause of the
SELECT
INTO statement, you specify the host
variables to store the resulting set rather than sending it to the standard
output:

SELECT
{[<qualifier>.]<column_name> | <expression>},... INTO
<host_variable>,... FROM <from_clause> [WHERE <predicate>]
[GROUP BY [<qualifier>.]<column_name>,... [HAVING
<predicate>] ]

SELECT
INTO has some limitations, but in general it is capable of doing most
things the regular
SELECT can do, including grouping,
using aggregate functions, and so on. (The
SELECT statement was discussed in
Chapter
8.) The number of columns listed in the
SELECT clause must match the number of
host variables in the
INTO clause; they also must be of
compatible data types. Also, the query must return one and only one row,
otherwise either
TOO
MANY
ROWS or
NO
DATA
FOUND RDBMS error is generated.

Assuming host variable declarations
earlier in this chapter, the following
SELECT INTO statement selects
CUST_ID_N and
CUST_NAME_S columns from the
CUSTOMER table using the ACME sample
database for a customer with alias
MNGA71396:

C

EXEC SQL SELECT CUST_ID_N,
CUST_NAME_S INTO :custid, :custname FROM CUSTOMER WHERE CUST_ALIAS_S =
'MNGA71396'; 

COBOL

EXEC SQL SELECT CUST_ID_N,
CUST_NAME_S INTO :CUSTID, :CUSTNAME FROM CUSTOMER WHERE CUST_ALIAS_S =
'MNGA71396' END-EXEC.

The host variables used in these SQL
statements are output host variables; you could combine both input and output
host variables in a single
SELECT
INTO statement. For example, assuming
another host variable,
custalias (CUSTALIAS) was declared and the value
MNGA71396 has been assigned to it, the
above statements could be written in this form:

C

EXEC SQL SELECT CUST_ID_N,
CUST_NAME_S INTO :custid, :custname FROM CUSTOMER WHERE CUST_ALIAS_S =
:custalias; 

COBOL

EXEC SQL SELECT CUST_ID_N,
CUST_NAME_S INTO :CUSTID, :CUSTNAME FROM CUSTOMER WHERE CUST_ALIAS_S =
:CUSTALIAS END-EXEC.

In this case,
CUSTID and
CUSTNAME are still used as output host
variables, and
CUSTALIAS plays the role of an input
host variable since it transfers data from the host program to RDBMS.

Using cursors to store multirow
query results

SELECT
INTO can be useful in many situations,
but it has a very serious limitation — it can only return one row at a time. We
introduced the concept of the
CURSOR in
Chapter
14, so not to repeat ourselves let us simply remind you there are four
basic operations on a cursor:
DECLARE,
OPEN,
FETCH, and
CLOSE. You can use both input and
output host variables in
DECLARE and
FETCH. The following examples show how
to use a cursor to retrieve
CUST_ID_N and
CUST_NAME_S columns from the
CUSTOMER table, row by row, and fetch
the values into the host variables until the last row is processed:

C

... EXEC SQL DECLARE
custcur CURSOR FOR SELECT CUST_ID_N, CUST_NAME_S FROM CUSTOMER; ... EXEC SQL
OPEN custcur; ... do { EXEC SQL FETCH custcur INTO :custid, :custname; ... if
(SQLCODE != 0) break; } ... EXEC SQL CLOSE custcur; ... 

COBOL

... EXEC SQL DECLARE
CUSTCUR CURSOR FOR SELECT CUST_ID_N, CUST_NAME_S FROM CUSTOMER END-EXEC. ...
EXEC SQL OPEN CUSTCUR END-EXEC. ... FETCH-LOOP. EXEC SQL FETCH CUSTCUR INTO
:CUSTID, :CUSTNAME END-EXEC. ... GO TO LOOP. EXIT. ... EXEC SQL CLOSE CUSTCUR
END-EXEC. ...

Handling errors

Error handling is an important part of
any application program. For a program with embedded SQL, error handling means
detecting and recovering from SQL statement execution errors (in addition to
any other errors in the program). It is critical to check for error conditions
after every DML statement to make sure it processed all data it was supposed
to; when using a
SELECT
INTO statement, you usually check if a
single row query returned no data (NO
DATA condition) or more than one row
(TOO
MANY
ROWS condition).

SQL99 specifies two variables,
SQLCODE and
SQLSTATE, for error handling needs.
SQLCODE is pretty much
implementation-specific. A negative value indicates some serious problem; a
positive number points to a warning; and zero means successful completion.
SQLSTATE is the new SQL99 standard; it
consists of error class and error subclass, which are consistent across
implementations.
Table
15-4 shows several
SQLSTATE classes and subclasses and
their meanings.

The error handling is usually done in
the host program using programming language-specific conditional statements.
For example:

C

... EXEC SQL
<embedded_sql_statement>; if (SQLCODE < 0) {
<condition_to_handle_error>,... } ... ... EXEC
SQL <single_row_select>; if (SQLSTATE != "02000") {
<do_something>,... } ... 

COBOL

EXEC SQL
<embedded_sql_statement> END-EXEC. IF SQLCODE LESS THAN 0
<condition_to_handle_error>,... ... ... EXEC SQL
<single_row_select> END-EXEC. IF SQLSTATE NOT = "02000"
<do_something>,... ...

As an alternative to checking
SQLCODE and/or
SQLSTATE values after each embedded SQL
statement, you can use SQL Communications Area (SQLCA) to handle embedded SQL errors.
SQLCA is a structure containing
components that are filled in at runtime after the SQL statement is processed
by RDBMS. Before your program can start using
SQLCA, you have to include the
structure in your program using the following syntax (usually somewhere before
the host variables declaration section):

C

EXEC SQL INCLUDE SQLCA;

COBOL

EXEC SQL INCLUDE SQLCA
END-EXEC.

The main advantage of using
SQLCA is that in addition to explicit
checking of its components in a way similar to how you would do it with
SQLCODE and
SQLSTATE variables, you can perform
implicit checking using
WHENEVER statement.

Rather than checking errors after
every single embedded SQL statement, with the
WHENEVER statement you can specify
actions to be taken when RDBMS detects errors, warnings, or
NOT
FOUND conditions. You can tell the
program to go to the next (or specifically labeled) statement, call a
subprogram, or stop execution.

The general syntax for the
WHENEVER statement is:

EXEC SQL
WHENEVER {SQLERROR | SQLWARNING | NOT FOUND} {CONTINUE | GOTO <label> |
DO | STOP} <language-specific delimiter>

The error handling with
WHENEVER is less specific, but much
simpler than using
SCLCODE or
SQLSTATE. All you have to do is use the
appropriate calls somewhere before the first SQL call in your program:

C

... EXEC SQL WHENEVER
SQLERROR GOTO my_error; EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL WHENEVER
SQLWARNING CONTINUE; ... 

COBOL

... EXEC SQL WHENEVER
SQLERROR GOTO MYERROR END-EXEC. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC. ...

Additional useful embedded SQL
statements

Some additional useful embedded SQL
statements are listed in
Table
15-5.

A typical embedded SQL program
flow

The following example illustrates the
typical flow of a program containing embedded SQL:

EXEC SQL BEGIN DECLARE
<host_variable_declaration>,... EXEC SQL END DECLARE SECTION
<language-specific delimiter> EXEC SQL INCLUDE SQLCA
<language-specific delimiter> EXEC SQL WHENEVER <condition>
<action>

<language-specific delimiter>,... EXEC SQL CONNECT <vendor-specific
connect string> <language-specific delimiter> EXEC SQL SELECT INTO
<single_select> <vendor-specific delimiter>,... EXEC SQL
<embedded_dml_statement> <language-specific delimiter>,... EXEC SQL
COMMIT <vendor-specific delimiter > EXEC SQL CONNECT RESET
<vendor-specific delimiter > <error handling section referred from
WHENEVER directive>