Gets information about the host variables required to store data retrieved from the database, or host variables required to pass data to the database.
SyntaxDESCRIBE [ USER TYPES ] [ ALL | BIND VARIABLES FOR | INPUT | OUTPUT | SELECT LIST FOR ] [ LONG NAMES [ long-name-spec ] | WITH VARIABLE RESULT ] [ FOR ] { statement-name | CURSOR cursor-name } INTO sqlda-name
long-name-spec : OWNER.TABLE.COLUMN | TABLE.COLUMN | COLUMN
statement-name : identifier | hostvar
cursor-name : declared cursor
sqlda-name : identifier
ParametersUSER TYPES clause A DESCRIBE statement with the USER TYPES clause returns information about domains of a column. Typically, such a DESCRIBE is done when a previous DESCRIBE returns an indicator of DT_HAS_USERTYPE_INFO.
The information returned is the same as for a DESCRIBE without the USER TYPES keywords, except that the sqlname field holds the name of the domain, instead of the name of the column.
If the DESCRIBE uses the LONG NAMES clause, the sqldata field holds this information.
ALL clause DESCRIBE ALL allows you to describe INPUT and OUTPUT with one request to the database server. This has a performance benefit. The OUTPUT information is filled in the SQLDA first, followed by the INPUT information. The sqld field contains the total number of INPUT and OUTPUT variables. The DT_DESCRIBE_INPUT bit in the indicator variable is set for INPUT variables and clear for OUTPUT variables.
BIND VARIABLES FOR clause Equivalent to the INPUT clause.
SELECT LIST FOR clause Equivalent to the OUTPUT clause.
INPUT clause A bind variable is a value supplied by the application when the database executes the statements. Bind variables can be considered parameters to the statement. DESCRIBE INPUT fills in the name fields in the SQLDA with the bind variable names. DESCRIBE INPUT also puts the number of bind variables in the sqlda field of the SQLDA.
DESCRIBE uses the indicator variables in the SQLDA to provide additional information. DT_PROCEDURE_IN and DT_PROCEDURE_OUT are bits that are set in the indicator variable when a CALL statement is described. DT_PROCEDURE_IN indicates an IN or INOUT parameter and DT_PROCEDURE_OUT indicates an INOUT or OUT parameter. Procedure RESULT columns will have both bits clear. After a describe OUTPUT, these bits can be used to distinguish between statements that have result sets (need to use OPEN, FETCH, RESUME, CLOSE) and statements that do not (need to use EXECUTE). DESCRIBE INPUT only sets DT_PROCEDURE_IN and DT_PROCEDURE_OUT appropriately when a bind variable is an argument to a CALL statement; bind variables within an expression that is an argument in a CALL statement will not set the bits.
OUTPUT clause The DESCRIBE OUTPUT statement fills in the data type and length for each SELECT list item in the SQLDA. The name field is also filled in with a name for the SELECT list item. If an alias is specified for a SELECT list item, the name will be that alias. Otherwise, the name is derived from the SELECT list item: if the item is a simple column name, it is used; otherwise, a substring of the expression is used. DESCRIBE will also put the number of SELECT list items in the sqld field of the SQLDA.
If the statement being described is a UNION of two or more SELECT statements, the column names returned for DESCRIBE OUTPUT are the same column names which would be returned for the first SELECT statement.
If you describe a CALL statement, the DESCRIBE OUTPUT statement fills in the data type, length, and name in the SQLDA for each INOUT or OUT parameter in the procedure. DESCRIBE OUTPUT also puts the number of INOUT or OUT parameters in the sqld field of the SQLDA.
If you describe a CALL statement with a result set, the DESCRIBE OUTPUT statement fills in the data type, length, and name in the SQLDA for each RESULT column in the procedure definition. DESCRIBE OUTPUT will also put the number of result columns in the sqld field of the SQLDA.
LONG NAMES clause The LONG NAMES clause is provided to retrieve column names for a statement or cursor. Without this clause, there is a 29-character limit on the length of column names; with the clause, names of an arbitrary length are supported.
If LONG NAMES is used, the long names are placed into the SQLDATA field of the SQLDA, as if you were fetching from a cursor. None of the other fields (SQLLEN, SQLTYPE, and so on) are filled in. The SQLDA must be set up like a FETCH SQLDA: it must contain one entry for each column, and the entry must be a string type. If there is an indicator variable, truncation is indicated in the usual fashion.
The default specification for the long names is TABLE.COLUMN.
WITH VARIABLE RESULT clause This clause is used to describe procedures that can have more than one result set, with different numbers or types of columns.
If WITH VARIABLE RESULT is used, the database server sets the SQLCOUNT value after the DESCRIBE statement to one of the following values:
0 The result set may change. The procedure call should be described again following each OPEN statement.
1 The result set is fixed. No re-describing is required.
RemarksThe DESCRIBE statement sets up the named SQLDA to describe either the OUTPUT (equivalently SELECT LIST FOR) or the INPUT (equivalently BIND VARIABLES FOR) for the named statement.
In the INPUT case, DESCRIBE BIND VARIABLES does not set up the data types in the SQLDA: this needs to be done by the application. The ALL keyword allows you to describe INPUT and OUTPUT in one SQLDA.
If you specify a statement name, the statement must have been previously prepared using the PREPARE statement with the same statement name and the SQLDA must have been previously allocated.
If you specify a cursor name, the cursor must have been previously declared and opened. The default action is to describe the OUTPUT. Only SELECT statements and CALL statements have OUTPUT. A DESCRIBE OUTPUT on any other statement, or on a cursor that is not a dynamic cursor, indicates no output by setting the sqld field of the SQLDA to zero.
In embedded SQL, NCHAR, NVARCHAR and LONG NVARCHAR are described as DT_FIXCHAR, DT_VARCHAR, and DT_LONGVARCHAR, respectively, by default. If the db_change_nchar_charset function has been called, these data types are described as DT_NFIXCHAR, DT_NVARCHAR and DT_LONGNVARCHAR, respectively.
PermissionsNone.
Side effectsNone.
See also Standards and compatibilitySQL/2008 The DESCRIBE OUTPUT statement is optional SQL language feature B031, "Basic dynamic SQL", of the SQL/2008 standard. The DESCRIBE INPUT statement is optional SQL language feature B032, "Extended dynamic SQL". Many of the other clauses of the DESCRIBE statement are vendor extensions. These include:
The USER TYPES, ALL, BIND VARIABLES FOR, LONG NAMES, and WITH VARIABLE RESULT clauses.
DESCRIBE uses the INTO clause to identify the sqlda; in the SQL/2008 standard, the USING keyword is used instead.
In the SQL/2008 standard, the CURSOR clause ends with the keyword STRUCTURE. STRUCTURE is not supported by SQL Anywhere.
ExampleThe following example shows how to use the DESCRIBE statement:
sqlda = alloc_sqlda( 3 );
EXEC SQL DESCRIBE OUTPUT
FOR employee_statement
INTO sqlda;
if( sqlda->sqld > sqlda->sqln ) {
actual_size = sqlda->sqld;
free_sqlda( sqlda );
sqlda = alloc_sqlda( actual_size );
EXEC SQL DESCRIBE OUTPUT
FOR employee_statement
INTO sqlda;
} |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |