SQL as presented thus far, has been seen in the direct mode of use interacting with a DBMS via a database tool. The use of SQL in application processes is known as embedded SQL. A further use of SQL is through the use of compound statements stored within a DBMS, executed as a single action. Among other additional features are triggers that can be used to update associated tables.

Embedded SQL

Application programs written in programming languages, can be used to access a database through SQL statements embedded in the programming. Certain features of SQL are intended solely for use in embedded SQL.

There are three stages:

• Including SQL data manipulation statements within the host language
• Embedded SQL statements requires a precompilation process to be converted into a host language procedure call. The statements could not otherwise be compiled by the host language compiler.
• During execution, the converted procedure calls interact between the program and the DBMS. This may result in the storing of data provided by the program or the retrieval of data by the program from the database.

A DBMS is language independent, the SQL statements remain the same whichever host language is used. Data transferred is limited to simple values representing a single row and not a number of rows. This does not prevent the SQL statements from processing multiple rows. It simply means that multiple rows of data cannot be passed between program and DBMS.

Distinguishing SQL statements

Before SQL statements can be converted into procedure calls acceptable to the host language, they have to recognised by the precompiler as SQL. This is done by:

• Prefixing the SQL statements with EXEC SQL.
• Ending the SQL statements with END-EXEC or the simple use of a semi-colon at the end of the statement.

Variables

Variables that are for use by both the host language and the SQL are known as host variables. These variables have to be declared:

EXEC SQL
BEGIN DECLARE SECTION;
StudentName, StaffName: string(12);
Quota, Grade: integer;
.
.
EXEC SQL
END DECLARE SECTION;

Host variables and table columns do not need to share the same name, although it may help in the understanding of the program. Host variable names when used within the SQL are prefixed with a colon as in :Name.

SQLSTATE is a special host variable. It is defined by the application program and its value is set following the execution of each SQL statement. It has two parts concatenated together:

• A two character class value identifying a general classification condition.
• A three character class value identifying a specific condition in a class.

SQLSTATE values are defined by the particular implementation of SQL. Examples are shown below:

• 00 000 - successful completion
• 01 000 - warning
• 01 003 - null value eliminated in set function
• 02 000 - no data
• 22 000 - data exception
• 22 012 - division by zero
• 23 000 - integrity constraint violation

Embedded statements and cursors

Embedded statements that process many rows require a cursor. Statements that do not require cursors are similar to direct mode of use statements.

Non cursor statements

Host variables can be used in place of constants/literals. This provides for three roles for host variables:

1. Provide values for input using UPDATE/INSERT statements.
2. Provide values for predicates in WHERE/HAVING clauses.
3. To be assigned values retrieved from a database.

Role 1

EXEC SQL
INSERT INTO student (student_id, student_name, registered)
VALUES (:StudentId, :StudentName, :YearRegistered);

Role 2

EXEC SQL
DELETE FROM student 
WHERE registered < :YearRegistered;

Role 3

EXEC SQL
SELECT name, registered, region
INTO :StudentName, :YearRegistered, :Region
FROM student
WHERE Student_Id = SelectId; 

The above statement is limited to retrieving only one row at a time. Although it could be executed many times in a loop statement.

Cursor statements

A DBMS can only transfer values to an application program one row at a time. An application program can use an SQL statement to retrieve many rows using an SQL technique concept called cursor. This is used to refer to (point to) each row of a table to be transferred in turn. A cursor declaration is neccessary:

EXEC SQL
DECLARE <cursor name> CURSOR FOR
        <query statement>;

A cursor declaration is a definition that does not retrieve data. The data is simply made available. An OPEN statement is required to make the cursor available for processing by an application program:

EXEC SQL
OPEN <cursor name>;

When the applicayion program has opened the cursor, the resulting table from the query statement can be processed, one row at a time, using a FETCH statement:

EXEC SQL
FETCH <cursor name>
   INTO <host variable> <host variable> <host variable>;

The list of host variables should match the data type and number of the columns given in the query statement. Repeated execution of the FETCH statement assigns a value for each of the columns in one row to the host variables. SQLSTATE can be used to determine when no more rows should be fetched.

An application program has finished with the cursor table on executing a CLOSE statement:

EXEC SQL
CLOSE <cursor name>;

Cursors may be opened and closed many times by an application and multiple cursors may be open at the same time.

Updating using cursors

Statements known as positioned updates may be used to modify or delete the row at the current cursor position using a form of the UPDATE or DELETE statements.

A WHERE clause specifies the location of the row to be updated:

WHERE CURRENT OF <cursor name>;

A positioned UPDATE has no effect on the cursor position but a positioned DFELETE places the cursor to FETCH the next row.

Comments, suggestions, ideas to
Stuart Banner