HP 3000 Manuals HP 3000 Manuals
Using Cursors with Procedures
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 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 | Single or multiple row query |
| a Procedure | results |
| | |
| | Built-in variable error checking |
| | |
----------------------------------------------------------
Refer to Table 4-4 and Table 4-5 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;
Static Processing.
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;
Dynamic Processing.
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
Static Processing.
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.
Dynamic Processing.
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
Example Schema.
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;
Static Processing.
For static processing, use the DECLARE, OPEN, FETCH, and CLOSE statements
to retrieve rows as usual. The ADVANCE statement is not required.
Dynamic Processing.
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;
[REV END]