15. Embedded SQL
Although a knowledgeable SQL user can accomplish a great deal with an interactive command processor, much interaction with a database is through application programs that provide a predictable interface for nontechnologically sophisticated users. In this chapter you will read about the preparation of programs that contain SQL statements and the special things you must do to fit SQL within a host programming language.
The Embedded SQL Environment
SQL statements can be embedded in a wide variety of host languages. Some are general-purpose programming languages such as COBOL, C++, or Java. Others are special-purpose database programming languages such as the PowerScript language used by PowerBuilder or Oracle's SQL/Plus, which contains the SQL language elements discussed in Chapter 14 as well as Oracle-specific extensions.
The way in which you handle source code depends on the type of host language you are using: Special-purpose database languages such as PowerScript or extensions of the SQL language (for example, SQL/Plus) need no special processing. Their language translators recognize embedded SQL statements and know what to do with them. However, general-purpose language compilers are not written to recognize syntax that isn't part of the original language. When a COBOL1 or C++ compiler encounters a SQL statement, it generates an error.
1Many people think COBOL is a dead language. While few new programs are being written, there are literally billions of lines of code for business applications written in COBOL that are still in use. Maintaining these applications is becoming a major issue for many organizations because COBOL programmers are starting to retire in large numbers and young programmers haven't learned the language.
The solution to the problem has several aspects:
◊ Support for SQL statements is provided by a set of program library modules. The input parameters to the modules represent the portions of a SQL statement that are set by the programmer.
◊ SQL statements embedded in a host language program are translated by a precompiler into calls to routines in the SQL library.
◊ The host language compiler can access the calls to library routines and therefore can compile the output produced by the precompiler.
◊ During the linking phase of program preparation, the library routines used to support SQL are linked to the executable file along with any other library used by the program.
To make it easier for the precompiler to recognize SQL statements, each one is preceded by EXEC SQL. The way in which you terminate the statement varies from one language to another. The typical terminators are summarized in Table 15-1. For the examples in this book, we will use a semicolon as an embedded SQL statement terminator.
Java is an unusual language, in that it is pseudo-compiled. (Language tokens are converted to machine code at runtime by the Java virtual machine.) It also accesses databases in its own way: using a library of routines (an API) known as Java Database Connectivity, or JDBC. A JDBC driver provides the interface between the JDBC library and the specific DBMS being used.
JDBC does not require that Java programs be precompiled. Instead, SQL commands are created as strings that are passed as parameters to functions in the JDBC library. The process for interacting with a database using JDBC goes something like this:
1. Create a connection to the database.
2. Use the object returned in Step 1 to create an object for a SQL statement.
3. Store each SQL command that will be used in a string variable.
4. Use the object returned in Step 2 to execute one or more SQL statements.
5. Close the statement object.
6. Close the database connection object.
If you will be using Java to write database applications, then you will probably want to investigate JDBC. Many books have been written about using it with a variety of DBMSs.
Using Host Language Variables
General purpose programming languages require that you redeclare any host language variables used in embedded SQL statements. 2 The declarations are bracketed between two SQL statements, using the following format:
EXEC SQL BEGIN DECLARE SECTION;
declarations go here
EXEC SQL END DECLARE SECTION;
2Some major DBMSs make the programmer's life easier by providing tools that generate host variables for each column in a table automatically (for example, DB2's DCLGEN).
The specifics of the variable declarations depend on the host language being used. The syntax typically conforms to the host language's syntax for variable declarations.
As mentioned in Chapter 14, when you use a host language variable in a SQL statement, you precede it by a colon so that it is distinct from table, view, and column names. For example, the following statement updates one row in the customer table with a value stored in the variable da_new_phone, using a value stored in the variable da_which_customer to identify the row to be modified3:
EXEC SQL UPDATE customer
SET contact_phone = :da_new_phone
WHERE customer_numb = :da_which_customer;
3Keep in mind that this will work only if the value on which we are searching is a primary key and thus uniquely identifies the row.
This use of a colon applies both to general purpose programming languages and to database application languages (even those that don't require a precompiler).
Note: The requirement for the colon in front of host language variables means that theoretically columns and host language variables could have the same names. In practice, however, using the same names can be confusing.
The host language variables that contain data for use in SQL statements are known as dynamic parameters. The values that are sent to the DBMS, for example, as part of a WHERE predicate, are known as input parameters. The values that accept data being returned by the DBMS, such as the data returned by a SELECT, are known as output parameters.
DBMS Return Codes
When you are working with interactive SQL, error messages appear on your screen. For example, if an INSERT command violates a table constraint, the SQL command processor tells you immediately. You then read the message and make any necessary changes to your SQL to correct the problem. How-ever, when SQL is embedded in a program, the end user has no access to the SQL and therefore can't make any corrections. Technologically unsophisticated users also may become upset when they see the usually cryptic DBMS errors appearing on the screen. Programs in which SQL is embedded need to be able to intercept the error codes returned by the DBMS and to handle them before the errors reach the end user.
The SQL standard defines a status variable named SQL-STATE, a five-character string. The first two characters represent the class of the error. The rightmost three characters are the subclass, which provides further detail about the state of the database. For example, 00000 means that the SQL statement executed successfully. Other codes include a class of 22, which indicates a data exception. The subclasses of class 22 include 003 (numeric value out of range) and 007 (invalid datetime format). A complete listing of the SQLSTATE return codes can be found in Appendix B.
In most cases, an application should check the contents of SQLSTATE each time it executes a SQL statement. For example, after performing the update example you saw in the preceding section, a C++ program might do the following:
If (strcmp(SQLSTATE,’00000’) == 0)
EXEC SQL COMMIT;
else
{
// some error handling code goes here
}
Retrieving a Single Row
When the WHERE predicate in a SELECT statement contains a primary key expression, the result table will contain at most one row. For such a query, all you need to do is specify host language variables into which the SQL command processor can place the data it retrieves. You do this by adding an INTO clause to the SELECT.
For example, if someone at the rare book store needed the phone number of a specific customer, a program might include
EXEC SQL SELECT contact_phone
INTO :da_phone_numb
FROM customers
WHERE customer_numb = 12;
The INTO clause contains the keyword INTO followed by the names of the host language variables in which data will be placed. In the preceding example, data are being retrieved from only one column and the INTO clause therefore contains just a single variable name.
Note: Many programmers have naming conventions that make working with host variables a bit easier. In this book, the names of host language variables that hold data begin with da_; indicator variables, to which you will be introduced in the next section, begin with in_.
If you want to retrieve data from multiple columns, you must provide one host language variable for each column, as in the following:
EXEC SQL SELECT first_name, last_name, contact_phone
INTO :da_first, :da_last, :da_phone
FROM customer
WHERE customer_numb = 12;
The names of the host language variables are irrelevant. The SQL command processor places data into them by position. In other words, data from the first column following SELECT is placed in the first variable following INTO, data from the second column following SELECT is placed in the second variable following INTO, and so on. Keep in mind that all host language variables are preceded by colons to distinguish them from the names of database elements.
After executing a SELECT that contains a primary key expression in its WHERE predicate, an embedded SQL program should check to determine whether a row was retrieved. Assuming we are using C or C++, the code might be written
There are three things to note about the COMMIT statement in this code:
◊ The COMMIT must be issued after checking the SQLSTATE. Otherwise, the COMMIT will change the value in SQLSTATE.
◊ There is no need to roll back a retrieval transaction, so the code commits the transaction even if the retrieval fails.
◊ The COMMIT could be placed after the IF construct. However, depending on the length of the code that follows error checking, the transaction may stay open longer than necessary. Therefore, the repeated COMMIT statement is an efficient choice in this situation.
Indicator Variables
The SQLSTATE variable is not the only way in which a DBMS can communicate the results of a retrieval to an application program. Each host variable into which you place data can be associated with an indicator variable. When indicator variables are present, the DBMS stores a 0 to indicate that a data variable has valid data of a -1 to indicate that the row contained a null in the specified column and that the contents of the data variable are unchanged.
To use indicator variables, first declare host language variables of an integer data type to hold the indicators. Then, follow each data variable in the INTO clause with the keyword INDICATOR and the name of the indicator variable. For example, to use indicator variables with the customer data retrieval query:
You can then use host language syntax to check the contents of each indicator variable to determine whether you have valid data to process in each data variable.
Note: The INDICATOR keyword is optional. Therefore, the syntax INTO :first :ifirst, :last :ilast, and so on is acceptable.
Indicator variables can also be useful for telling you when character values have been truncated. For example, assume that the host language variable first has been declared to accept a 10-character string but that the database column first_name is 15 characters long. If the database column contains a full 15 characters, only the first 10 will be placed in the host language variable. The indicator variable will contain 15, indicating the size of the column (and the size to which the host language variable should have been set).
Retrieving Multiple Rows: Cursors
SELECT statements that may return more than one row present a bit of a problem when you embed them in a program. Host language variables can hold only one value at a time and the SQL command processor cannot work with host language arrays. The solution provides you with a pointer (a cursor) to a SQL result table that allows you to extract one row at a time for processing.
The procedure for creating and working with a cursor is as follows:
1. Declare the cursor by specifying the SQL SELECT to be executed. This does not perform the retrieval.
3. Fetch the next row in the result table and process the data in some way.
4. Repeat step 3 until all rows in the result table have been accessed and processed.
5. Close the cursor. This deletes the result table from main memory but does not destroy the declaration. You can therefore reopen an existing cursor, recreating the result table, and work with the data without redeclaring the SELECT.
If you do not explicitly close a cursor, it will be closed automatically when the transaction terminates. (This is the default.) If, however, you want the cursor to remain open after a COMMIT, then you add a WITH HOLD option to the declaration.
Even if a cursor is held from one transaction to another, its result table will still be deleted at the end of the database session in which it was created. To return that result table to the calling routine, add a WITH RETURN option to the declaration.
Note: There is no way to “undeclare” a cursor. A cursor's declaration disappears when the program module in which it was created terminates.
By default, a cursor fetches the “next” row in the result table. However, you may also use a scrollable cursor to fetch the “next,” “prior,” “first,” or “last” row. In addition, you can fetch by specifying a row number in the result table or by giving an offset from the current row. This in large measure eliminates the need to close and reopen the cursor to reposition the cursor above its current location.
Declaring a Cursor
Declaring a cursor is similar to creating a view in that you include a SQL statement that defines a virtual table. The DECLARE statement has the following general format in its simplest form:
DECLARE cursor_name CURSOR FOR
SELECT remainder_of_query
For example, assume that someone at the rare book store wanted to prepare labels for a mailing to all its customers. The program that prints mailing labels needs each customer's name and address from the database, which it can then format for labels. A cursor to hold the data might be declared as
EXEC SQL DECLARE address_data CURSOR FOR
SELECT first_name, last_name, street, city,
state_province, zip_postcode
FROM customer;
The name of a cursor must be unique within the program module in which it is created. A program can therefore manipulate an unlimited number of cursors at the same time.
Scrolling Cursors
One of the options available with a cursor is the ability to retrieve rows in other than the default “next” order. To enable a scrolling cursor, you must indicate that you want scrolling when you declare the cursor by adding the keyword SCROLL after the cursor name:
EXEC SQL DECLARE address_data SCROLL CURSOR FOR
SELECT first_name, last_name, street, city, state_province, zip_postcode
FROM customer;
You will find more about using scrolling cursors a bit later in this chapter when we talk about fetching rows.
Enabling Updates
The data in a cursor are by default read only. However, if the result table meets all updatability criteria, you can use the cursor for data modification. (You will find more about the updatability criteria in the Modification Using Cursors section later in this chapter.)
To enable modification for a customer, add the keywords FOR UPDATE at the end of the cursor's declaration:
EXEC SQL DECLARE address_data SCROLL CURSOR FOR
SELECT first_name, last_name, street, city, state_province, zip_postcode
FROM customer
FOR UPDATE;
To restrict updates to specific columns, add the names of columns following UPDATE:
EXEC SQL DECLARE address_data SCROLL CURSOR FOR SELECT first_name, last_name, street, city, state_province, zip_postcode
FROM customer
FOR UPDATE street, city, state_province, zip_postcode;
Sensitivity
Assume, for example, that a program for the rare book store contains a module that computes the average price of books and changes prices based on that average: If a book's price is more than 20 percent higher than the average, the price is discounted 10 percent; if the price is only 10 percent higher, it is discounted 5 percent.
A programmer codes the logic of the program in the following way:
2. Fetch each row and modify its price.
The question at this point is: What happens in the result table as data are modified? As prices are lowered, some rows will no longer meet the criteria for inclusion in the table. More important, the average retail price will drop. If this program is to execute correctly, however, the contents of the result table must remain fixed once the cursor has been opened.
The SQL standard therefore defines three types of cursors:
◊ Insensitive: The contents of the result table are fixed.
◊ Sensitive: The contents of the result table are updated each time the table is modified.
◊ Indeterminate (asensitive): The effects of updates made by the same transaction on the result table are left up to each individual DBMS.
The default is indeterminate, which means that you cannot be certain that the DBMS will not alter your result table before you are through with it.
The solution is to request specifically that the cursor be insensitive:
EXEC SQL DECLARE address_data SCROLL
INSENSITIVE CURSOR FOR
SELECT first_name, last_name, street, city, state_province, zip_postcode
FROM customer
FOR UPDATE street, city, state_province, zip_postcode;
Opening a Cursor
Fetching Rows
To retrieve the data from the next row in a result table, placing data into host language variables, you use the FETCH statement:
FETCH FROM cursor_name
INTO host_language_variables
For example, to obtain a row of data from the list of customer names and addresses, the rare book store's program could use
EXEC SQL FETCH FROM address_data
INTO :da_first, :da_last, :da_street, :da_city,
:da_state_province, :da_zip_postcode;
Notice that as always the host language variables are preceded by colons to distinguish them from table, view, or column names. In addition, the host language variables must match the database columns as to data type. The FETCH will fail if, for example, you attempt to place a string value into a numeric variable.
If you want to fetch something other than the next row, you can declare a scrolling cursor and specify the row by adding the direction in which you want the cursor to move after the keyword FETCH:
◊ To fetch the first row
EXEC SQL FETCH FIRST FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
◊ To fetch the prior row
EXEC SQL FETCH PRIOR FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
◊ To fetch a row specified by its position (row number) in the result table
EXEC SQL FETCH ABSOLUTE 12
FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
The preceding fetches the twelfth row in the result table.
◊ To fetch a row relative to and below the current position of the cursor
EXEC SQL FETCH RELATIVE 5
FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
The preceding fetches the row five rows below the current position of the cursor (current position + 5).
◊ To fetch a row relative to and above the current position of the cursor
EXEC SQL FETCH RELATIVE -5
FROM address_data
INTO :da_first, :da_last, :da_street,
:da_city, :da_state_province,
:da_zip_postcode;
The preceding fetches the row five rows above the current position of the cursor (current row - 5).
Note: If you use FETCH without an INTO clause, you will move the cursor without retrieving any data.
If there is no row containing data at the position of the cursor, the DBMS returns a “no data” error (SQLSTATE = ‘02000’). The general strategy for processing a table of data is therefore to create a loop that continues to fetch rows until a SQLSTATE of something other than ‘00000’ occurs. Then you can test to see whether you've simply finished processing or whether some other problem has arisen. In C/C++, the code would look something like Figure 15-1.
Note: One common error that beginning programmers make is to write loops that use a specific error code as a terminating value. This can result in an infinite loop if some other error condition arises. We therefore typically write loops to stop on any error condition and then check to determine exactly which condition occurred.
Note: You can use indicator variables in the INTO clause of a FETCH statement, just as you do when executing a SELECT that retrieves a single row.
 |
| Figure 15-1 Using a host language loop to process all rows in an embedded SQL result table |
Closing a Cursor
To close a cursor, removing its result table from main memory, use
CLOSE cursor_name
as in
EXEC SQL CLOSE address_data;
Embedded SQL Data Modification
Although many of today's database development environments make it easy to create forms for data entry and modification, all those forms do is collect data. There must be a program of some type underlying the form to actually interact with the database. For example, whenever a salesperson at the rare book store makes a sale, a program must create the row in sale and modify appropriate rows in volume.
Direct Modification
Data modification can be performed using the SQL UPDATE command to change one or more rows. In some cases, you can use a cursor to identify which rows should be updated in the underlying base tables.
To perform direct data modification using the SQL UPDATE command, you simply include the command in your program. For example, if the selling price of a purchased volume is stored in the host language variable da_selling_price, the sale ID in da_sale_id, and the volume's inventory ID in da_inventory_id, you could update volume with
EXEC SQL UPDATE volume
SET selling_price = :da_selling_price, sale_id = :da_sale_id
WHERE inventory_id = :da_inventory_id;
The preceding statement will update one row in the table because its WHERE predicate contains a primary key expression. To modify multiple rows, you use an UPDATE with a WHERE predicate that identifies multiple rows, such as the following, which increases the prices by two percent for volumes with leather bindings:
EXEC SQL UPDATE volume
SET asking_price = asking_price * 1.02
WHERE isbn IN (SELECT isbn
FROM book
WHERE binding = “Leather’);
Indicator Variables and Data Modification
Indicator variables, which hold information about the result of embedded SQL retrievals, can also be used when performing embedded SQL modification. Their purpose is to indicate that you want to store a null in a column. For example, assume that the rare book store has a program that stores new rows in the volume table. At the time a new row is inserted, there are no values for the selling price or the sale ID; these columns should be left null.
To do this, the program declares an indicator variable for each column in the table. If the data variable hold a value to be stored, the program sets the indicator variable to 0; if the column is to be left null, the program sets the indicator variable to -1.
Sample pseudocode for performing this embedded INSERT can be found in Figure 15-2.
 |
| Figure 15-2 Using indicator variables to send nulls to a table |
Integrity Validation with the MATCH Predicate
The MATCH predicate is designed to be used with embedded SQL modification to let you test referential integrity before actually inserting data into tables. When included in an application program, it can help identify potential data modification errors.
For example, assume that a program written for the rare book store has a function that inserts new books into the database. The program wants to ensure that a work for the book exists in the database before attempting to store the book. The application program might therefore include the following query:
EXEC SQL SELECT work_numb
FROM work JOIN author
WHERE (:entered_author, :entered_title)
MATCH (SELECT author_first_last, title
FROM work JOIN author);
The subquery selects all the rows in the join of the work and author tables and then matches the author and title columns against the values entered by the user, both of which are stored in host language variables. If the preceding query returns one or more rows, then the author and title pair entered by the customer exist in the author and work relations. However, if the result table has no rows, then inserting the book into book would produce a referential integrity violation and the insert should not be performed.
If a program written for the rare book store wanted to verify a primary key constraint, it could use a variation of the MATCH predicate that requires unique values in the result table. For example, to determine whether a work is already in the database, the program could use
EXEC SQL SELECT work_numb
FROM work JOIN author
WHERE UNIQUE (:entered_author, :entered_title)
MATCH (SELECT author_first_last, title
FROM work JOIN author);
By default, MATCH returns true if any value being tested is null or, when there are no nulls in the value being tested, a row exists in the result table that matches the values being tested. You can, however, change the behavior of MATCH when nulls are present:
◊ MATCH FULL is true if every value being tested is null or, when there are no nulls in the values being tested, a row exists in the result table that matches the values being tested.
◊ MATCH PARTIAL is true if every value being tested is null or a row exists in the result table that matches the values being tested.
Note that you can combine UNIQUE with MATCH FULL and MATCH PARTIAL.
Modification Using Cursors
Updates using cursors are a bit different from updating a view. When you update a view, the UPDATE command acts directly on the view by using the view's name. The update is then passed back to the underlying base table(s) by the DBMS. In contrast, using a cursor for updating means you update a base table directly, but identify the row that you want to modify by referring to the row to which the cursor currently is pointing.
To do the modification, you use FETCH without an INTO clause to move the cursor to the row you want to update. Then you can use an UPDATE command with a WHERE predicate that specifies the row pointed to by the cursor. For example, to change the address of the customer in row 15 of the address_data cursor's result table, a program for the rare book store could include
EXEC SQL FETCH ABSOLUTE 15 FROM address_data;
EXEC SQL UPDATE cutomer
SET street = ‘123 Main Street’,
city = ‘New Home’
state_province = ‘MA’,
zip_postcode = ‘02111’
WHERE CURRENT OF address data;
The clause CURRENT OF cursor_name instructs SQL to work with the row in customer currently being pointed to by the name cursor. If there is no valid corresponding row in the customer table, the update will fail.
You can apply the technique of modifying the row pointed to by a cursor to deletions as well as updates. To delete the current row, you use
Deletion Using Cursors
DELETE FROM table_name
WHERE CURRENT OF cursor_name
The deletion will fail if the current row indicated by the cursor isn't a row in the table named in the DELETE. For example,
EXEC SQL DELETE FROM customers WHERE CURRENT OF address_data;
will probably succeed, but
EXEC SQL DELETE FROM volume
WHERE CURRENT OF address_data;
will certainly fail because the volume table isn't part of the address_data cursor (as declared in the preceding section of this chapter).
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.