 |
» |
|
|
|
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.
Scope | |
C and Pascal Applications Only SQL Syntax | |
DESCRIBE [ OUTPUT INPUT RESULT ] StatementName { INTO [ [SQL] DESCRIPTOR] USING [SQL] DESCRIPTOR } { SQLDA AreaName } Parameters | |
- 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.
Description | |
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 Pascal 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.
Authorization | |
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. Examples | |
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: 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;}}
|
|
