EMBEDDED SQL, PROCEDURES, AND CURSORS

Write a set of SQL statements in a module and then executing that module (Module language or Procedures), and

Writing SQL statements from an application programming interface such as PowerBuilder (Embedded SQL).

Although we have been using Direct Invocation of SQL to study SQL as a programming language, SQL is mostly used in the context of some other host language. Module language or Procedure is SQL code that resides separately from the host language. The host languages then use native mechanisms to directly invoke those procedures. In contrast, embedded SQL is SQL statement written directly in an application interface and executed along with other code in the application.

String ls_nam

Long ll_cost

SELECT Item_Name,Item_cost INTO :ls_name, :ll_cost

FROM ITEM WHERE Item_id = '050';

IF SQLCA.SQLCode = -1 then

MessageBox("HOSPITAL APPLICATION", SQLCA.SQLErrText,Information!)

HALT

END IF

IF ll_cost > 10 THEN

MessageBox("HOSPITAL APPLICATION","This Item costs more than ten dollars")

USING Matching Data Types. The variable declaration of string and long are powerBuilder data types. In this example, PowerBuilder data type String matches with the SQL data type Char. The SELECT statement uses column item_name, which is of Char data type in SQL and holds the value in the PowerBuilder variable ls_name which is a string. The only expectation from SQL is that if the host language uses a data type, it should simply match with the characteristics of a SQL data type.

CHECKING FOR return codes. When executing a SQL statements interactively, you may see an error message at times, even when your SQL statement in syntactically correct. This may happen in situations such as a subquery returning more than one row, or the requested record locked by some other user. You see the response visually and take appropriate actions. In Embedded SQL, since it is your application program that is sending the query, the programmer must take responsibility to check for the status of your query after executing a SQL statement. An attribute SQLCODE of the SQLCA transaction object contain the message returned by the database. This must be checked after every embedded SQL statement.

In embedded SQL, we use the host variables to store the values return from the SQL query. These host variables are application variables preceded by a colon (:) . We must note that if a SQL query returns a result set (as opposed to a single value), then we obviously cannot store that in a single variable. In situations where we have to process multiple rows of table we must resort to CURSORS.

A cursor is something like a pointer that traverses a collection of rows and acts as intermediary to resolve our host language's inability to handle an arbitrary collection of rows. There are four Statements

DECLARE a cursor. DECLARE findavg CURSOR FOR SELECT salary FROM Employee is an example of a declaration of cursor. Findavg is the name of the cursor that is later used in the OPEN and CLOSE statements as well. Any value that must be supplied by the host program can be specified in the DECLARE statement. As an example, if we are interested in finding the average of only those employees whose salary is above a certain value specifed by the host program, we could have written DECLARE findavg CURSOR FOR SELECT salary FROM Employee WHERE salary > :ll_mysal

OPEN the cursor. There is nothing complex about opening the cursor. OPEN findavg will do it! Note that there are no host variables associated with the OPEN statement. All values to the cursor are specified only in the DECLARE statement, but it is actually obtained by the OPEN statement.

FETCH statement allows the data to be into applications' area one row at a time. It simply fetches the row after the row on which the cursor is positioned. Many DBMS also support formats of FETCH other than the customary (and default) FETCH NEXT such as FETCH FIRST, FETCH PRIOR, or FETCH LAST. After fetching, you can also do a positioned delete or Update. DELETE FROM TableName WHERE CURRENT OF CursorName; will delete the current row where the cursor is positioned.

CLOSE is used to close the cursor. Only when you CLOSE the cursor the system resources are freed up. It also protects you from inadvertently using the cursor in later data operations.