 |
» |
|
|
|
Two types of cursors are available in ALLBASE/SQL: A select cursor is one declared for a SELECT statement within either an application or a procedure. It is a pointer used to indicate the current row in a set of rows retrieved by a SELECT statement. A select cursor opened in an application program cannot be accessed within the procedure. However, a procedure can open and access its own select cursors. A procedure cursor is one declared for an
EXECUTE PROCEDURE statement within an application. It is a pointer used
to indicate the current result set and row in a set of rows retrieved by a set of SELECT statements in a procedure. A procedure cursor must be opened and accessed outside of the
specified procedure, in an application program. A given application can open more than one procedure cursor.
When you declare a procedure cursor and use procedure cursor processing statements in your application, the read functionality of a select cursor declared in an application is provided for any query with no INTO clause located in the procedure. Results of queries within such a procedure are available within your application, not within the procedure. A procedure cursor allows callers to process multiple row result sets from a procedure one row at a time either statically or dynamically.
Access to multiple row result sets from a procedure is read-only. Table 5-1 “Using Cursors with Procedures within an Application” compares the functionality available for a procedure cursor and a select cursor in a procedure. Table 5-1 Using Cursors with Procedures within an Application | Cursor Type | Available Functionality |
|---|
| Procedure Cursor | BULK processing Passing multiple (or single) row query results based on procedure queries to the invoking application SQLCA error checking | | | UPDATE or DELETE WHERE CURRENT | | Select Cursor in a Procedure | Single or multiple row query results | | | Built-in variable error checking |
Refer to Table 4-4 “Setting SQLDA Fields for Output and for Input in C” and Table 4-5 “Setting SQLDA Fields for Output and for Input in Pascal” in the
"Using Parameter Substitution in Dynamic Statements" chapter in this manual for coding information related to dynamic cursors. Procedures with Multiple Row Result Sets of Different Formats | |
The following example shows a procedure definition followed by an excerpt from an application program that uses a procedure cursor:
EXEC SQL CREATE PROCEDURE InventoryReport (option INTEGER, qty INTEGER OUTPUT)
AS BEGIN
IF option = 1 THEN
SELECT PartNumber FROM PurchDB.Inventory;
ELSEIF option = 2 THEN
SELECT DISTINCT BinNumber FROM PurchDB.Inventory;
ELSE
SELECT PartNumber, BinNumber, QtyOnHand FROM PurchDB.Inventory;
ENDIF;
SELECT SUM (QtyOnHand) INTO :qty FROM PurchDB.Inventory;
RETURN ::sqlcode;
END;
|
The following example shows the execution of procedure InventoryReport, retrieving multiple row result sets:
First, declare and open a cursor for the procedure returning multiple row result sets.
EXEC SQL DECLARE InvRepCursor CURSOR FOR EXECUTE PROCEDURE
:ReturnStatus = InventoryReport (:opt, :qty OUTPUT);
You must initialize the input value for :opt before the OPEN cursor call.
EXEC SQL OPEN InvRepCursor;
while (sqlca.sqlcode >= 0) && (sqlca.sqlcode != 200)
{
Advance to the next query result set from the procedure. Any remaining rows in the current query result set are discarded. Procedure execution continues with the next statement. Control returns to the caller when the next multiple row result set statement is executed or the procedure terminates. The number of columns and format information for the next query result set is returned in the specified SQLDA. This information may be used to process the query result set. When the last result set has been processed, ALLBASE/SQL sets SQLCA.SQLCODE to 200.
EXEC SQL ADVANCE InvRepCursor USING sqldaresult;
if (sqlca.sqlcode == 0 )
{
while (sqlca.sqlcode == 0)
{
Fetch one or more rows from the current query result set. Use the same SQLDA as that specified in the ADVANCE statement. When the last row in the last result set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100. When the last row in the current result set has been fetched, ALLBASE/SQL automatically advances to the next result set.
EXEC SQL FETCH InvRepCursor USING sqldaresult;
if (sqlca.sqlcode = 0)
{
Use the number of columns and column format information to process the query result. A detailed description is found in the "Using Dynamic Operations" chapters of the ALLBASE/SQL C Application Programming Guide.
}
}
}
}
The CLOSE statement will cancel processing of any remaining query result sets. Procedure execution continues with the next statement. No data is returned, nor does control return to the application for any subsequent multiple row result set queries executed by the procedure. The return status, :ReturnStatus, and output parameter, :qty, values are available to the caller after the CLOSE call.
EXEC SQL CLOSE InvRepCursor;
|
You need special techniques to handle dynamic EXECUTE PROCEDURE statements. In a program that accepts both EXECUTE PROCEDURE statements, and other SQL commands, you should first PREPARE the command, then use the DESCRIBE command with the OUTPUT option. The following C pseudocode outlines the above scenario with emphasis on ALLBASE/SQL programming for dynamically executing procedures with multiple row result sets.
Set up a dynamic command with dynamic parameters.
:DynamicCmd = "EXECUTE PROCEDURE ? = InventoryReport (?, ? OUTPUT);";
Assume you don't know the format for the statement. Prepare the statement.
EXEC SQL PREPARE cmd FROM :DynamicCmd;
For a dynamic EXECUTE PROCEDURE statement, the DESCRIBE command with the OUTPUT option sets the sqld field of the SQLDA to 0 and sets the sqlmproc field to a non-zero value for a procedure having multiple row result sets. The sqloparm field is set to the number of output parameters (including return status) in the EXECUTE PROCEDURE statement. The sqlfmtarr of the sqldaout is set to the data formats for the return status and output parameters of the procedure, if any. If you know there are no dynamic parameters in the prepared statement, use the EXECUTE statement to execute the dynamically preprocessed statement.
EXEC SQL DESCRIBE OUTPUT cmd INTO sqldaout;
For a dynamic EXECUTE PROCEDURE statement, the DESCRIBE command with the INPUT option sets the sqld field of the SQLDA to the number of input dynamic parameters in the prepared statement.
EXEC SQL DESCRIBE INPUT cmd USING sqldain;
If there are input dynamic parameters, the appropriate data buffer or host variables must be loaded with the values for the input dynamic parameters. Since the sqlmproc field is set to a non-zero value, the procedure has multiple row result sets. You define a procedure CURSOR to move through the query results sets row by row.
Declare a cursor for the procedure returning multiple row result sets.
EXEC SQL DECLARE InvRepCursor CURSOR FOR cmd;
Place the appropriate values into the SQLDA sqldain. Use the USING DESCRIPTOR clause of the OPEN statement to identify where the input dynamic parameter information is located.
EXEC SQL OPEN InvRepCursor USING sqldain;
while (sqlca.sqlcode >= 0) && (sqlca.sqlcode != 200)
{
Use the USING DESCRIPTOR clause of the ADVANCE statement to identify where to place the query result format information. Advance to the next query result set from the procedure. Any remaining rows in the previous query result set are discarded. Procedure execution continues with the next statement. Control returns to the caller when the next multiple row result set statement is executed. The number of columns and format information for the current query result set are returned in the specified SQLDA. This information may be used to process the query result set.
When the last result set has been processed, ALLBASE/SQL sets SQLCA.SQLCODE to 200.
EXEC SQL ADVANCE InvRepCursor USING sqldaresult;
if (sqlca.sqlcode != 0)
{
while (sqlca.sqlcode == 0)
{
Fetch as many rows from the current query result set as specified in SQLDA.sqlnrow. Specify the same SQLDA as specified in the ADVANCE statement. When the last row in the current result set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100.
EXEC SQL FETCH InvRepCursor USING sqldaresult;
if (sqlca.sqlcode == 0)
{
Use number of columns and column format information to process the query result. A detailed description is found in the "Using Dynamic Operations" chapters of the ALLBASE/SQL C Application Programming Guide.
}
}
}
}
The CLOSE statement will cancel processing of any remaining query result sets. Procedure execution continues with the next statement. No data is returned, nor does control return to the application for any subsequent multiple row result set queries executed by the procedure. Use the SQLDA specified in the DESCRIBE OUTPUT statement to retrieve return status and output parameter values.
CLOSE InvRepCursor USING sqldaout;
|
Procedures with no Multiple Row Result Sets | |
If a procedure is known to contain no multiple row result sets,
or the caller does not wish to retrieve such results, a simple EXECUTE PROCEDURE statement can be issued.
EXEC SQL EXECUTE PROCEDURE :ReturnStatus = InventoryReport (:opt, :qty OUTPUT);
|
The EXECUTE PROCEDURE statement will return a warning if the procedure contains any multiple row result sets.
In this example, the prepared statement is an EXECUTE PROCEDURE statement with both INPUT and OUTPUT dynamic parameters. After using DESCRIBE INPUT and OUTPUT for cmd, it can be executed using input and output SQL descriptor areas, or input and output host variables.
EXEC SQL EXECUTE cmd USING DESCRIPTOR INPUT sqldain AND OUTPUT sqldaout;
OR
EXEC SQL EXECUTE cmd USING INPUT :opt, :qty AND OUTPUT :ReturnStatus, :qty;
The EXECUTE PROCEDURE statement will return a warning if the procedure contains any multiple row result sets.
|
Single Format Multiple Row Result Sets | |
The following example defines a procedure returning single format multiple row result sets.
CREATE PROCEDURE ReportActivity (Activity CHAR (18))
WITH RESULT CHAR (20) NOT NULL, SMALLINT AS
BEGIN
DECLARE Clubtid TID;
SELECT TID () INTO :Clubtid FROM RecDB.Clubs
WHERE Activity = :Activity;
SELECT ClubName, ClubPhone
FROM RecDB.Clubs
WHERE TID () = :ClubTID;
SELECT MemberName, MemberPhone
FROM RecDB.Members
WHERE Club =
(SELECT ClubName FROM RecDB.Clubs WHERE TID () = :ClubTID);
END;
|
For static processing, use the DECLARE, OPEN, FETCH, and CLOSE statements to retrieve rows as usual. The ADVANCE statement is not required.
Prepare a dynamic command with dynamic parameters.
:DynamicCmd = "EXECUTE PROCEDURE ? = ReportActivity (?);";
PREPARE cmd FROM :DynamicCmd;
For a dynamic EXECUTE PROCEDURE statement, the DESCRIBE command with the OUTPUT option sets the sqld field of the SQLDA to 0 and sets the sqlmproc field to a non-zero value for a procedure having multiple row result sets. The sqloparm field is set to the number of output parameters (including return status) in the EXECUTE PROCEDURE statement. The sqlfmtarr of the sqldaout is set to the data formats for the return status and output parameters of the procedure, if any.
EXEC SQL DESCRIBE OUTPUT cmd INTO sqldaout;
For a dynamic EXECUTE PROCEDURE statement, the DESCRIBE command with the INPUT option sets the sqld field of the SQLDA to the number of input dynamic parameters in the prepared statement.
EXEC SQL DESCRIBE INPUT cmd USING sqldain;
If there are input dynamic parameters, the appropriate data buffer or host variables must be loaded with the values for the input dynamic parameters.
If the dynamic command (cmd) is an EXECUTE PROCEDURE statement with single format multiple row result sets, the DESCRIBE RESULT command returns format information for the procedure result sets. In contrast, when the procedure does not have single format multiple row result sets, the sqld field of SQLDA is set to zero.
If the dynamic command is not an EXECUTE PROCEDURE statement, sqlca.sqlcode is set to a non-zero value.
DESCRIBE RESULT cmd USING sqldaresult;
Declare a cursor for a procedure returning single format multiple row result sets.
DECLARE RepActCursor CURSOR FOR cmd;
Place the appropriate values into the SQLDA sqldain. Use the USING DESCRIPTOR clause of the OPEN statement to identify where the input dynamic parameter information is located.
OPEN RepActCursor USING sqldain;
while (sqlca.sqlcode >= 0) && (sqlca.sqlcode != 100)
{
Fetch as many rows from the current query result set as specified in SQLDA.sqlnrow. Specify the same SQLDA as specified in the ADVANCE statement. When the last row in the current result set has been fetched, ALLBASE/SQL automatically advances to the next result set. When the last row in the last result set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100.
FETCH RepActCursor USING sqldaresult;
Use number of columns and column format information to process the query result. A detailed description is found in the "Using Dynamic Operations" chapters of the ALLBASE/SQL C Application Programming Guide.
}
The CLOSE statement will cancel processing of any remaining query result sets. Procedure execution continues with the next statement. No data is returned, nor does control return to the application for any subsequent multiple row result set queries executed by the procedure. Use the SQLDA specified in the DESCRIBE OUTPUT statement to retrieve return status and output parameter values.
CLOSE RepActCursor USING sqldaout;
|
|
