HP 3000 Manuals HP 3000 Manuals
DESCRIBE
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
[OUTPUT]
DESCRIBE [INPUT ] StatementName {INTO [[SQL] DESCRIPTOR]} {SQLDA }
[RESULT] {USING [SQL] DESCRIPTOR } {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
1. 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
2. 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
3. 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.
a. 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
:
b. Else, execute the procedure with both input and output
dynamic parameters.
EXECUTE cmd USING DESCRIPTOR INPUT sqldain AND OUTPUT sqldaout;