This section explains how embedded SQL applications retrieve data.
Retrieving Data Using Cursors
Data is retrieved from database tables with the
FETCHstatement, which fetches the values from an individual row in a result set into host variables.
The result set is defined by a
SELECTconstruction or a result set procedure
CALL, see Manipulating Data, used in a cursor declaration. A cursor may be thought of as a pointer which moves through the rows of the result set as successive
FETCHstatements are issued.
An exception is raised to indicate when the
FETCHhas reached the end of the result set.
Data retrieval involves several steps in the application program code, which are as follows:
- declaration of host variables to hold data
- declaration of a cursor with the appropriate
SELECTconditions or result set procedure
- opening the cursor
- performing the
- closing the cursor.
The steps in the previous section are built into the application program as shown in the general frameworks below (only SQL statements are shown in the frameworks).
SELECT:EXEC SQL BEGIN DECLARE SECTION; ... VAR1, VAR2, ... VARn ... EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cursor-name CURSOR FOR select-statement; EXEC SQL OPEN cursor-name; loop as required EXEC SQL FETCH cursor-name INTO :VAR1, :VAR2, ..., :VARn; end loop; EXEC SQL CLOSE cursor-name;
For a result set procedure
CALL:EXEC SQL BEGIN DECLARE SECTION; ... VAR1, VAR2, ... VARn ... EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cursor-name CURSOR FOR CALL routine-invocation; EXEC SQL OPEN cursor-name; loop as required EXEC SQL FETCH cursor-name INTO :VAR1, :VAR2, ..., :VARn; end loop; EXEC SQL CLOSE cursor-name;
Declaring Host Variables
All host variables used to hold data fetched from the database and used in selection conditions or as result set procedure parameters must be declared within an SQL
DECLARE SECTION, see Communicating with the Application Program.
Indicator variables for columns that may contain null values must also be declared.
The same indicator variable may be associated with different main variables at different times, but declaration of a dedicated indicator variable for each main variable is recommended for clarity.
Declaring the Cursor
A cursor operates as a row pointer associated with a result set.
A cursor is defined by the
DECLARE CURSORstatement and the set of rows addressed by the cursor is defined by the
SELECTstatement in the cursor declaration.
Cursors are local to the program in which they are declared. A cursor is given an identifying name when it is declared.
DECLARE CURSORis a declarative statement that does not result in any implicit connection to a database, see Idents and Privileges for details on connecting to a database.
Preprocessing the statement generates a series of parameters used by the SQL compiler but does not generate any executable code; the select-expression or result set procedure call in the cursor declaration is not executed until the cursor is opened.
Holdable cursors can be declared using the
WITH HOLDclause. An open cursor declared
WITH HOLDremain open after
Cursors should normally be declared
WITHOUT HOLD(default), because
WITH HOLDcursors require more internal resources then ordinary cursors. In addition, long lasting
WITH HOLDcursors can have negative performance effects just like long lasting transactions.
If the cursor declaration contains a
CALLto a result set procedure, it is
FETCHthat actually executes the procedure.
RETURNstatement is used from within the result set procedure to return a row of the result set.
FETCHcauses statements in the result set procedure to execute until a
RETURNstatement is executed, which will return the row data defined by it. Execution of the procedure is suspended at that point until the next
If, during execution, the end of the procedure is encountered instead of a
FETCHresult is end-of-set. See Result Set Procedures for a detailed description of result set procedures.
Note: It is advisable to always use an explicit list of items in the SELECT statement of the cursor declaration. The shorthand notations SELECT * and SELECT table.* are useful in interactive SQL, but can cause conflicts in the variable lists of FETCH statements if the table definition is changed.
The cursor declaration can use host variables in the
HAVINGclause of the
The result set addressed by the cursor is then determined by the values of these host variables at the time when the cursor is opened.
The same cursor declaration can thus address different result sets depending on when the cursor is opened, for example:EXEC SQL DECLARE C1 CURSOR..; -- cursor with host variables set variables EXEC SQL OPEN C1; -- open one result set ... EXEC SQL CLOSE C1; set variables EXEC SQL OPEN C1; -- open different result set
Scrollable cursors can be declared using the
SCROLLkeyword. When a cursor is declared as scrollable, records can be fetched using an orientation specification. This makes it possible to scroll through the result set with the cursor.
Cursors which are to be used only for retrieving data may be declared with a
FOR READ ONLYclause in the
SELECTstatement. This can improve performance slightly in comparison with cursors that permit update and delete operations.
Opening the Cursor
A declared cursor must be opened with the
OPENstatement before data can be retrieved from the database. The
OPENstatement evaluates the cursor declaration in terms of
- the privileges the current user holds on any tables and views accessed by the cursor
- the values of any host variables used in the
- for a cursor calling a result set procedure, whether the current user has the required
EXECUTEprivilege on the procedure and also the values of any
OPENstatement has been executed, the cursor is positioned before the first row in the result set.
Once a cursor has been opened, data may be retrieved from the result set with
FETCHstatements, see the Mimer SQL Reference Manual, FETCH, for the syntax description.
Host variables in the variable list correspond in order to the column names specified in the
SELECTclause of the cursor declaration. The number of variables in the
FETCHstatement may not be more than the number of columns selected. The number of variables may be less than the number of columns selected, but a `success with warning'-code is then returned in
A suitably declared record structure may be used in place of a variable list in host languages where this is supported, see Host Language Dependent Aspects.
FETCHstatement moves the cursor to the specified row in the result set before retrieving data. In strict relational algebra, the ordering of tuples in a relation (the formal equivalent of rows in a table) is undefined. The
SELECTstatement in the cursor declaration may include an
ORDER BYclause if the ordering of rows in the result set is important to the application.
Note: A cursor declared with an ORDER BY clause cannot be used for updating table contents.
ORDER BYclause is specified, the ordering of rows in the result set is unpredictable.
Note: The variables into which data is fetched are specified in the FETCH statement, not in the cursor declaration. In other words, data from different rows in the result set may be fetched into different variables.
When there are no more rows to fetch, the exception condition
NOT FOUNDwill be raised.
The following construction thus fetches rows successively until the result set is exhausted:EXEC SQL DECLARE C1 CURSOR FOR select-statement; EXEC SQL OPEN C1; EXEC SQL WHENEVER NOT FOUND GOTO done; LOOP EXEC SQL FETCH C1 INTO :var1,:var2,...,:varn; END LOOP done: EXEC SQL CLOSE C1;
The access rights for a user are checked when the cursor is opened and they remain unchanged for that cursor until the cursor is closed.
For example, if an application program declares and opens a cursor, then
SELECTaccess on the table is revoked from the user running the program, data can still be fetched from the result set as long as the cursor remains open. Any subsequent attempt to open the same cursor will, however, fail.
The Embedded SQL interface tries whenever possible to fetch rows in blocks to minimize server communications. The first fetch would normally issue a request to the server for a number of rows at once. In most situations, this will improve application performance.
In some situations, this is not the desired behavior. One such situation is queries searching through a huge number of rows without the help of indexes. For example if the database server is only able to return one row a second, and the entire query takes minutes, the user can still be happy as long as he sees the first rows on screen. If this is important to the application, set the fetch size manually. An appropriate fetch size is the number of rows displayed at once. See Mimer SQL Reference Manual, SET SESSION FETCH SIZE for more information.
Closing a Cursor
An opened cursor remains open until it is closed with one of the statements
CLOSEcloses the specified cursor.
DISCONNECTclose all open cursors for the connection.
COMMITcloses all open cursors for the connection, except cursors declared as
WITH HOLD. Once a cursor is closed, the result set is no longer accessible. However, the cursor declaration remains valid, and a new cursor may be opened with the same declaration.
Note: The result set addressed by the new cursor may not be the same if the contents of the database or the values of variables used in the declaration have changed.
Normally, resources used by the cursor remain allocated when the cursor is closed and will be used again if the cursor is re-opened. The optional form
CLOSE cursor-name RELEASEdeallocates cursor resources. Use of
RELEASEoption is recommended in application programs which open a large number of cursors, particularly where system resources are limited.
Note: The use of CLOSE with the RELEASE option may slow down performance if there is a following OPEN, since it requires that new resources are allocated at the next OPEN for that cursor. For this reason it should only be used when necessary.
Cursors are local to a connection and remain open but dormant when the connection is made dormant. The state of dormant cursors is fully restored (including result set addressed and position in the result set) when the connection is reactivated. Cursors are, however, closed and cursor resources are deallocated, when a connection is disconnected.
Note: Cursors opened in a program ident context are closed and resources deallocated when LEAVE is executed within the same connection, unless LEAVE RETAIN is specified.
Retrieving Single Rows
If the result of a
SELECTstatement is known to be a single row, the
SELECT INTOstatement may be used as an alternative to fetching data through a cursor.
This is a much simpler programming construction, since cursors are not used and the only requirement is that host variables used in the
SELECT INTOstatement are declared in the
However, there are two disadvantages associated with
- An error occurs if the result set addressed by the search condition contains more than one row. In other words,
SELECT INTOcan only be reliably used when there is no possibility of a multi-row result set (essentially when the search condition includes the columns that form a
PRIMARY KEYcolumn or returns just the result of a set function, e.g.
- Execution of the
SELECT INTOstatement involves a check that the result set contains one single row, which may incur unnecessary overhead. Even if it is known that the result row is unique, a single
FETCHoperation through a cursor may be a more efficient implementation.
Use of a
SELECT INTOstatement is justified when the result set may contain several rows, but it is a condition for continued execution of the application program that the result row is unique. With a cursor, this would require a construction that checked that one and only one
FETCHoperation could be performed (alternatively, use a separate
SELECT COUNTwith the same search condition as the cursor). In such a case, a
SELECT INTOstatement with a check on the return code, see Handling Errors and Exceptions, is probably the preferred solution.
CALLstatement can be used to return information to the one or more host variables associated with the output parameter(s) of the procedure.
SETstatement can be used with a function or method invocation to return information to one host variable.
Retrieving Data from Multiple Tables
Data can be retrieved from multiple tables in ESQL by addressing several tables in the
SELECTstatement of the cursor declaration, in the same way as in interactive SQL. The preprocessor generates a
SELECTstatement addressing multiple tables, which is optimized by the SQL compiler when the cursor is opened.
ExampleEXEC SQL DECLARE c_1 CURSOR FOR SELECT ... FROM a JOIN b ON a.x = b.y; EXEC SQL OPEN c_1; ...
An alternative way to link information between tables could be to define the search condition for one cursor in terms of a variable fetched through another cursor:EXEC SQL DECLARE c_1 CURSOR FOR SELECT x FROM a; EXEC SQL DECLARE c_2 CURSOR FOR SELECT ... FROM b WHERE y = :HOSTX; EXEC SQL OPEN c_1; EXEC SQL FETCH c_1 INTO :HOSTX; EXEC SQL CLOSE c_1; EXEC SQL OPEN c_2; EXEC SQL FETCH c_2; ...
When considering the two alternatives, the first one is preferred. The reason for this is:
- The SQL optimizer gets the full information about the query that it is supposed to return a result set for. In this way the optimizer can make more use of statistical information and it can thereby optimize the query to execute in a more efficient way.
- The application will require less resources in the form of open cursors.
- If the application is run in a client/server environment, the second alternative will cause more communication over the network, since it will send data over the net which is only used to determine which data from the second cursor that will be selected and is of no real interest to the application.
- The application will be more compact as well as easier to understand and maintain.
The 'Parts explosion' Problem
A special case of data retrieval from multiple tables is the use of stacked cursors to fetch data from logical copies of the same table, in a manner that provides a solution to the so called "Parts explosion" problem.
A cursor can be defined as
REOPENABLEand the same cursor may be opened several times in succession in the same application program, each previous instance of the cursor being saved on a stack and restored when the following instance is closed. A
FETCHstatement refers to the most recently opened instance of a cursor. Each instance of the cursor addresses an independent result set and the position of each cursor in its own result set is saved on the stack.
Note: Result sets addressed by different instances of a cursor may differ according to the conditions prevailing when the cursor instance was opened.
The state of the cursor stack needs to be controlled by the application. A counter can be used to indicate if there are more instances of the cursor remaining on the stack. See the example that follows.
Stacked cursors are typically used in application programs which traverse a tree structure stored in the database.
For example (this is a simplified variant of the "parts explosion" problem), traverse a tree structure and print out the leaf nodes:
procedure TRAVERSE; integer CSTACK, LASTC; EXEC SQL BEGIN DECLARE SECTION; integer PARENT, CHILD; string SQLSTATE(5); EXEC SQL END DECLARE SECTION; begin EXEC SQL DECLARE c_tree REOPENABLE CURSOR FOR SELECT parent, child FROM tree WHERE parent = :PARENT; CSTACK := 1; LASTC := 1; PARENT := 1; -- Start at root node EXEC SQL OPEN CTREE; loop EXEC SQL FETCH c_tree INTO :PARENT, :CHILD; if SQLSTATE = "02000" then -- No more children EXEC SQL CLOSE c_tree; -- Pop the parent CSTACK := CSTACK - 1; exit when CSTACK = 0; if CSTACK >= LASTC then print(PARENT); -- Write leaf node end if; LASTC := CSTACK; else -- Step to next level PARENT := CHILD; EXEC SQL OPEN c_tree; -- Stack the current parent and open new level CSTACK := CSTACK + 1; end if; end loop; end TRAVERSE;
LASTCkeep track of the number of stacked cursor levels and the latest level in the tree hierarchy respectively.
Entering Data into Tables
The following sections explain how to perform cursor-independent operations and update and delete using cursors.
The SQL statements
UPDATE, as well as user-defined functions or method invocations, embedded in application programs operate on a set of rows in a table or view in exactly the same way as in interactive SQL.
Host variables may be used in the statements to supply values or set search conditions, and host variables may be used as routine parameters.
Examples:EXEC SQL INSERT INTO mimer_store.items(item_id, product_id, format_id, release_date, price, stock, reorder_level, ean_code, producer_id) VALUES (CURRENT VALUE FOR mimer_store.item_id_seq, :product_id, :format_id, mimer_store.cast_to_date(:book_release_date), :book_price, :book_stock, :book_reorder_level, (:ean * 10) + mimer_store.ean_check_digit(:ean), producer_id);
From the standpoint of the application program, each statement is a single indivisible operation, regardless of how many columns and rows are affected.
Updating and Deleting Through Cursors
DELETE CURRENTstatements (see the Mimer SQL Reference Manual, SQL Statement Descriptions for the syntax description), allow update and delete operations to be controlled on a row-by-row basis from an application. These statements operate through cursors, which are declared and opened as described above for
These statements operate on the current row of the cursor referenced in the statement. If there is no current row, e.g. the cursor has been opened but not yet positioned with a
FETCHstatement, an error is raised.
UPDATE CURRENTchanges the contents of the current row according to the
SETclause in the statement, but does not change the position of the cursor. Two consecutive
UPDATE CURRENTstatements will therefore update the same row twice.
DELETE CURRENTdeletes the current row and does not move the cursor; after a
DELETE CURRENTstatement, the cursor is positioned between rows and there is no current row. The cursor must be moved to the next row with a
FETCHstatement before any other operation can be performed through the cursor.
DELETE CURRENTstatements, the table name as used in the statement must be exactly the same as the table name addressed in the cursor declaration. The cursor must also address an updatable result set.
DELETE CURRENTchanges for a particular cursor can be divided into several transactions if the cursor is a holdable cursor. A cursor declared
WITH HOLDremains open when transactions are committed, which makes it possible to use the same cursor for fetch and update of additional rows after
COMMIT. However, each row must still be fetched and updated (or deleted) in the same transaction.
SELECTstatements are by default read only. This means that they cannot be used with
DELETE CURRENTunless a
FOR UPDATEclause is added to the
SELECTstatement. If a
FOR UPDATE OFclause is used to specify which fetched columns may be updated, only the columns specified may appear in the corresponding
Cursors can not be updatable if the data retrieval statement in the cursor declaration contains any of the following features at the top level (i.e. not in a subselect) of the statement:
- reference to more than one table in the
- reference to a read-only view in the
- the keyword
- set-functions in the
- arithmetic or string concatenation expressions in the
- the result set of an explicit inner or outer
CALLto a result set procedure
When to Use UPDATE CURRENT, DELETE CURRENT
DELETE CURRENTstatements are useful for manipulating single rows in interactive applications where rows are displayed, and the user decides which rows to delete or update.
The example below illustrates the program framework for such an operation (the construction is similar for a
DELETE CURRENToperation):... EXEC SQL DECLARE c_1 CURSOR FOR SELECT ... FOR UPDATE; ... EXEC SQL OPEN c_1; EXEC SQL WHENEVER NOT FOUND GOTO done; loop EXEC SQL FETCH c_1 INTO :VAR1, :VAR2, ..., :VARn; display VAR1, VAR2, ..., VARn; prompt "Update this row?"; if ANSWER = "YES" then prompt "Give new values"; EXEC SQL UPDATE tab SET col1 = :NEWVAL1, col2 = :NEWVAL2, ... WHERE CURRENT OF c_1; display "Row updated"; end if; prompt "Display next row?"; exit when ANSWER = "NO"; end loop; done: EXEC SQL CLOSE c_1;
In situations where there is no requirement to interactively choose rows and where all the rows to be updated or deleted can be specified completely in terms of a
WHEREclause, it is more efficient to do so rather than use a cursor.
An operation completely specified as a
WHEREclause is executed as a single statement, rather than a series of statements (i.e. one for each
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40