|
|
The DESCRIBE statement is used in an application program to pass
information about a dynamic statement between the application and ALLBASE/SQL.
It must refer to a statement preprocessed with the PREPARE statement.
C and Pascal Applications Only
DESCRIBE [OUTPUT
INPUT
RESULT] StatementName {INTO [[SQL] DESCRIPTOR]
USING [SQL] DESCRIPTOR} {SQLDA
AreaName}
- OUTPUT
specifies that the characteristics of any output values in the
prepared StatementName be described in the associated
sqlda_type and sqlformat_type data structures. This applies to query
result column definitions in a SELECT statement or to dynamic
return status or output parameters specified as question marks in an
EXECUTE PROCEDURE statement. OUTPUT is the default.
- INPUT
specifies that the characteristics of any dynamic input parameters in
the prepared StatementName be described in the associated
sqlda_type and sqlformat_type data structures. This applies to dynamic
input parameters specified as question marks in any DML
statement.
- RESULT
specifies that the characteristics of any single format multiple row
result sets in a procedure created using the WITH RESULT clause be
described in the associated sqlda_type and sqlformat_type data
structures. This applies to any prepared EXECUTE PROCEDURE
statement.
- StatementName
identifies a previously preprocessed (prepared) ALLBASE/SQL
statement.
- INTO
specifies the sqlda_type data structure where data is to be
described.
- USING
specifies the sqlda_type data structure where data is to be
described.
- SQLDA
specifies that a data structure of sqlda_type named sqlda is to be
used to pass information about the prepared statement between the
application and ALLBASE/SQL.
- AreaName
specifies the user defined name of a data structure of sqlda_type that
is to be used to pass information about the prepared statement between
the application and ALLBASE/SQL.
This statement cannot be used in ISQL, in COBOL and FORTRAN
programs, or in procedures.
If StatementName refers to a SELECT
statement, the DESCRIBE statement with the (default) OUTPUT
option sets the sqld field of the associated
sqlda_type data structure to the number of columns in the query
result and sets the associated sqlformat_type data structure to each
column's name, length, and data type. On the basis on this
information, an application can parse a data buffer to obtain the
column values in the query result. The application reads the query
result by associating the StatementName with a select
cursor and using select cursor manipulation statements (OPEN,
FETCH, and CLOSE).
If StatementName does not refer to a
SELECT statement, the DESCRIBE statement used with the
OUTPUT option sets the sqld field of the associated
sqlda_type data structure to zero.
If StatementName refers to a statement in which
dynamic parameters have been specified, the DESCRIBE statement
with the INPUT option obtains the number of input dynamic parameters
(in the sqld field of the associated sqlda_type data
structure) and sets the associated sqlformat_type data structure to
each column's name, length, and data type. The application can use
this information to load the appropriate data buffer with dynamic
parameter values.
If StatementName refers to an
EXECUTE PROCEDURE statement for a procedure with multiple
row result sets, the sqlmproc field of the associated
sqlda_type data structure is set to a non-zero value. The program
reads the query results by associating the
StatementName with a procedure cursor name and using
procedure cursor manipulation statements (OPEN, ADVANCE,
FETCH, and CLOSE).
If StatementName refers to an EXECUTE
PROCEDURE statement containing output dynamic parameters, the
DESCRIBE statement with the (default) OUTPUT option returns
the number of output dynamic parameters in the
sqloparm field of the associated sqlda_type data
structure.
If StatementName refers to an
EXECUTE PROCEDURE statement containing both input and output
dynamic parameters, you can issue the EXECUTE statement
specifying the USING INPUT AND OUTPUT option to execute the
dynamically preprocessed statement.
If StatementName is an EXECUTE PROCEDURE
statement containing single format multiple row result set(s), the
DESCRIBE statement with the RESULT option returns the format
information of the multiple row result set(s). If the procedure
contains more than one multiple row result set, all must return rows
with compatible formats.
If the RESULT option is specified when describing an EXECUTE
PROCEDURE statement for a procedure created with no WITH RESULT
clause, the sqld field of the related SQLDA is set to zero, and no
format information is written to the SQL descriptor area.
If the RESULT option is specified when describing a statement
other than an EXECUTE PROCEDURE statement, the DESCRIBE
RESULT statement returns an error, and nothing is written to the
SQL descriptor area.
Detailed descriptions of how to use this statement are found in
the "Using Dynamic Operations" chapters of the ALLBASE/SQL C
Application Programming Guide and the ALLBASE/SQL Advanced
Application Programming Guide, and in the "Using Parameter
Substitution in Dynamic Statements" chapter and the "Using Procedures
in Application Programs" chapter of the ALLBASE/SQL Advanced
Application Programming Guide.
To describe a previously preprocessed SELECT statement, you must have
authority that would permit you to execute the SELECT statement. To
describe a previously preprocessed EXECUTE PROCEDURE statement, you must
have authority that would permit you to execute the procedure. You do not need
authorization to describe other previously preprocessed statements.
Prepared statement with known format
If you know in advance that the statement to be dynamically
preprocessed is neither a SELECT statement nor an EXECUTE
PROCEDURE statement with results, and does not contain dynamic
parameters nor input/output host variables, you can prepare it and
execute it in one step, as follows:
EXECUTE IMMEDIATE :Dynam1
Prepared statement with unknown format
In other instances, it is more appropriate to prepare and execute the
statement in separate operations. For example, if you don't know the
format of a statement, you could do the following:
PREPARE Dynamic1 FROM :Dynam1
The statement stored in :Dynam1 is dynamically preprocessed.
DESCRIBE Dynamic1 INTO SqldaOut
If Dynamic1 is neither a SELECT statement (Sqld field of the
Sqlda data structure is 0) nor an EXECUTE PROCEDURE statement
with results (sqlmproc = 0) and you know there are no dynamic
parameters in the prepared statement, use the EXECUTE
statement to execute the dynamically preprocessed statement.
If Dynamic1 is an EXECUTE PROCEDURE statement containing
dynamic output parameters, the sqloparm field of the Sqlda data
structure contains the number of such parameters in the statement.
You can access the appropriate format array and data buffer to obtain
the data.
If it is possible that dynamic input parameters are present in the
prepared statement or that the statement is an
EXECUTE PROCEDURE statement for a procedure with multiple row
result sets, you must further describe it. See the exproc function
below which emphasizes steps needed to process an
EXECUTE PROCEDURE statement for a procedure with multiple row
result sets.
To check for dynamic input parameters in any type of DML statement,
describe the statement for input:
DESCRIBE INPUT Dynamic1 USING SQL DESCRIPTOR SqldaIn
If dynamic input parameters are present, the appropriate data buffer
or host variables must be loaded with the values of any dynamic
parameters. Then if the statement is not a query, it can be executed,
as in this example using a data buffer:
EXECUTE Dynamic1 USING SQL DESCRIPTOR SqldaIn
If Dynamic1 is a SELECT statement and the language you are
using supports dynamically defined SELECT statements, use a
cursor to manipulate the rows in the query result:
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
Place the appropriate values into the SQL descriptor areas. Use the
USING DESCRIPTOR clause of the OPEN statement to identify where
dynamic input parameter information is located. Load related dynamic
parameter data into the input data buffer.
OPEN Dynamic1Cursor USING SQL DESCRIPTOR SqldaIn
Use the USING DESCRIPTOR clause of the FETCH statement to
identify where to place the rows selected.
FETCH Dynamic1Cursor USING DESCRIPTOR SqldaOut
.
.
.
When all rows have been processed, close the cursor:
CLOSE Dynamic1Cursor
Prepared statement is EXECUTE PROCEDURE
If the described statement is an EXECUTE PROCEDURE statement
for a procedure with multiple row result sets, the sqlmproc field of
the sqlda data structure contains the number of multiple row result
sets (0 if there are none) following execution of the DESCRIBE
statement with default OUTPUT option. For example, if the statement
you described looks like the following, and the procedure was created
with two multiple row result SELECT statements and a WITH
RESULT clause:
DynamicCmd = "EXECUTE PROCEDURE ? = proc(?, ? OUTPUT)"
PREPARE cmd FROM :DynamicCmd
Assuming you don't know the format of this prepared statement:
DESCRIBE OUTPUT cmd INTO sqldaout
The sqld of sqlda is set to 0, sqlmproc to 2, and sqloparm to 2.
DESCRIBE INPUT cmd USING sqldain
The sqld of sqlda is set to 2, sqlmproc to 2, and sqloparm to 0.
If sqldaout. sqlmproc <> 0 then, use
procedure cursor processing statements to process multiple row
result set(s) from the procedure.
DESCRIBE RESULT cmd USING sqldaresult
.
.
.
DECLARE Dynamic1Cursor CURSOR FOR cmd
OPEN Dynamic1Cursor USING sqldain
.
.
.
FETCH Dynamic1Cursor using DESCRIPTOR sqldaresult
.
.
.
CLOSE Dynamic1Cursor USING sqldaout
.
.
.
Else, execute the procedure with both input and output
dynamic parameters.
EXECUTE cmd USING DESCRIPTOR INPUT sqldain
AND OUTPUT sqldaout;
|