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.).

Cross-References 

The major elements of a procedural language are introduced in Chapter 14.

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.

start sidebar
Embedded and Dynamic SQL Terminology

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.

end sidebar

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.

Tip 

To be able to use embedded SQL in your host program, you must provide it with special instructions on how to find the specific precompiler files for the RDBMS the host program will be using.

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>

Note 

The EXEC SQL keyword is used to start an embedded SQL block in a host-language program.

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

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

Example 2

start example
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. 
end example

Example 3

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

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.

Table 15-1: Data type Correspondence

SQL99

C

COBOL

FORTRAN

CHAR(n)

char[n+1]

PIC (n)

CHARACTER*n

DECIMAL/NUMERIC

Double

V9(s) COMP-3

REAL*8

DOUBLE

Double

COMP-2

REAL*8

INTEGER

Long

PIC S9 (9)

INTEGER*4

REAL

Float

COMP-1

REAL*4

SMALLINT

Short

PIC S9 (4) COMP

INTEGER*2

VARCHAR(n)

char*

NO EQUIVALENT

NO EQUIVALENT

Note 

Many SQL99 data types do not have exact equivalents in the host languages; for example, all date and time related data types must be converted into host-language-compatible character string data types.

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.

Table 15-2: Indicator Variable Values (Input)

Value

Meaning

-1

NULL will be assigned to the column; the value of the host variable is ignored.

>= 0

The value of the host variable will be assigned to the column.

Table 15-3: Indicator Variable Values (Output)

Value

Meaning

-1

The column value is NULL.

0

The column value is not NULL; the original column value is assigned to the associated host variable.

> 0

The column value is not NULL, but the original column value was truncated before it was assigned to the associated 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; 
Note 

This example is the typical situation when the input host variable is used to pass the information (the user name and the password) from your host program to the RDBMS.

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;

Note 

This example assumes that the additional host variable, database, is in place. In the Oracle example, we assumed the default database connection; otherwise the connect string would be slightly different.

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

start example
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD END-EXEC. 
end example

DB2 UDB example

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

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

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

COBOL

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

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

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

COBOL

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

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

start example
... 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; ... 
end example

COBOL

start example
... 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. ...
end example

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.

Table 15-4: Selected SQLSTATE Codes

Code

Code Condition

Subcode

Subcode condition

00

Successful completion

00000

Successful completion

01

Warning

01000
01001
01007

Warning
Cursor operation conflict
Privilege not granted

02

No data

02000

No data

08

Connection exception

08000
08002
08006

Connection exception
Connection name is use
Connection failure

0A

Feature not supported

0A000
0A001

Feature not supported
Multiple server transactions

23

Integrity constraint violation

23000

Integrity constraint violation

27

Triggered data change violation

27000

Triggered data change violation

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

C

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

COBOL

start example
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>,... ...
end example

Note 

You must declare SQLCODE and/or SQLSTATE before you can use it in your program; refer to vendor-specific documentation for details.

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

start example
EXEC SQL INCLUDE SQLCA; 
end example

COBOL

start example
EXEC SQL INCLUDE SQLCA END-EXEC. 
end example

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

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

COBOL

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

Additional useful embedded SQL statements

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

Table 15-5: Useful Embedded SQL Statements

Generic statement syntax

Explanation

EXEC SQL COMMIT [WORK] [RELEASE]
<language-specific delimiter>

End the current transaction making all database changes permanent. The optional keyword RELEASE also frees resources and disconnects from the database.

EXEC SQL ROLLBACK [WORK] [RELEASE]
<language-specific delimiter>

End the current transaction, discarding all database changes. The optional keyword RELEASE also frees resources and disconnects from the database.

EXEC SQL CONNECT RESET
<language-specific delimiter>
EXEC SQL INSERT INTO
<table_name>[<columns_clause>]
VALUES <values_clause>

Disconnect for the database.

<language-specific delimiter>
EXEC SQL UPDATE <table_name>
SET <set_clause> [<where_clause>]

Add rows to a table.

<language-specific delimiter>
EXEC SQL DELETE FROM <table_name>

Change existing values in a table.

[<where_clause>]
<language-specific delimiter>

Remove rows from a table.

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>




SQL Bible
Microsoft SQL Server 2008 Bible
ISBN: 0470257040
EAN: 2147483647
Year: 2005
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net