An SQL descriptor area is used as a communication area between an application program and Adabas SQL Gateway Embedded SQL for dynamic SQL. It is used for communicating information between Adabas SQL Gateway Embedded SQL and the application program in both directions.
The information for a dynamic SQL statement that can be retrieved from Adabas SQL Gateway Embedded SQL by an application program using an SQLDA originates from either of two sources:
The derived column list of a dynamic SELECT statement. The application program can retrieve information about the layout of the resulting format of a SELECT statement. The information comprises a list of elements where each element describes the corresponding derived column. An SQLDA describing this type of information is called an output SQLDA. The information is assigned to the output SQLDA by either an extended PREPARE statement (example 1a) or a separate DESCRIBE statement (example 1b). The keyword OUTPUT is the default and therefore optional.
Example 1a:
EXEC SQL
PREPARE statement_id INTO :output_sqlda FROM dyn_sql_statement;
Example 1b:
EXEC SQL
PREPARE statement_id FROM :dyn_sql_statement;
EXEC SQL
DESCRIBE statement_id INTO OUTPUT :output_sqlda;
The host variable markers in a dynamic SQL statement.
The application program can retrieve information about all host variable markers used in a dynamic SQL statement. The information comprises a list of elements where each element describes the corresponding host variable marker. An SQLDA describing this type of information is called an input SQLDA. The information is assigned to the input SQLDA by either an extended PREPARE statement (example 2a) or a separate DESCRIBE statement (example 2b). The keyword INPUT is mandatory.
Example 2a:
EXEC SQL
PREPARE statement_id INTO INPUT :input_sqlda FROM dyn_sql_statement;
Example 2b:
EXEC SQL
PREPARE statement_id FROM :dyn_sql_statement;
EXEC SQL
DESCRIBE statement_id INTO INPUT :input_sqlda;
Both input and output SQLDAs can be specified in the same PREPARE and DESCRIBE statements if desired. However, one SQLDA cannot be used for both an input and an output SQLDA simultaneously.
Once Adabas SQL Gateway Embedded SQL has filled an SQLDA with this information, the application program must provide a host variable reference for each element. This must be done prior to the execution of the prepared statement.
Corresponding to the two types of SQLDAs, two types of host variable references must be supplied.
Target host variables for receiving resultant data
The elements of an output SQLDA associated with a prepared SELECT statement each describe the expected format of the data to be received. The application program must assign to each element a suitable host variable which is capable of receiving the expected data. Adabas SQL Gateway Embedded SQL can now determine where to copy the resulting data by means of the pointer reference in each element. Such an output SQLDA is only used in conjunction with a FETCH statement.
Example 1:
EXEC SQL
FETCH ABC USING DESCRIPTOR :output_sqlda;
Host variables as host variable marker replacements
The elements of an input SQLDA each describe the expected format of any additional parameters required by the prepared statement as represented by host variable markers. The application program must assign a suitable host variable to each element of the input SQLDA and each host variable must be loaded with the desired value before execution of the prepared statement. Such an input SQLDA is used in conjunction with either an OPEN statement (example 2a) or an EXECUTE statement (example 2b).
Example 2a:
EXEC SQL
OPEN ABC USING DESCRIPTOR :input_sqlda;
Example 2 b:
EXEC SQL
EXECUTE statement_id USING DESCRIPTOR :input_sqlda;
Exactly the same structure is used for input and output SQLDA. It consists of two distinct parts:
A header containing general information about the prepared statement,
A consecutive list of elements corresponding to fields in the derived column list or the host variable markers.
The structure consists of the four fields of the SQLDA header immediately
followed by as many occurrences of the sqlvar structure as stated in the
sqln field.
|
Field |
Description |
|
|
sqldaid |
An eight-byte character string containing the constant SQLDA which serves as an eye catcher for easier memory dump interpretation. |
|
|
sqldabc |
A four-byte integer field of type SAG_INTEGER containing the total length of the SQLDA in bytes, that is, the length of the header plus the length of the variable descriptor elements multiplied by the number of available elements (sqln). |
|
|
sqln |
A two-byte integer field containing the total number of variable descriptor elements allocated in the SQLDA. |
|
|
sqld |
A two-byte integer field containing the total number of variable descriptor elements returned during the execution of a DESCRIBE statement. |
|
|
sqlvar |
An array containing sqln variable descriptor elements. |
|
|
sqltype |
A four-byte integer field of type SAGTYPE containing the data type of the required/specified host variable and whether or not there is an INDICATOR variable present. |
|
|
sqllen |
A four-byte integer field of type SAG_INTEGER containing the length of the required/specified host variable. The interpretation of this field depends on the data type. |
|
|
reserved |
A four-byte integer field of type SAG_INTEGER required for internal purposes. |
|
|
internal |
A two-byte integer field required for internal purposes. |
|
|
sqlindlen |
A two-byte integer field containing the length of the host variable acting as an indicator value, if one is required |
|
|
sqlindtype |
A four-byte integer field containing the data type of the host variable acting as an indicator value, if one is required |
|
|
sqlind |
A structure of type SAGPointer containing the pointer to the host variable acting as an indicator value, if one is required. |
|
|
sqldata |
A structure of type SAGPointer containing the pointer to the host variable which is to receive or which contains the data. |
|
|
sqlname |
A structure of type SAGCOLUMN containing the derived column name of the resulting column, the length of the column name and the column type. |
|
The sqlname field is only relevant for an output SQLDA and only in the particular case of the corresponding derived column having a derived column label.
The sqltype field is set by Adabas SQL Gateway Embedded SQL to reflect the particular type of the required field.
The sqllen field is also set by Adabas SQL Gateway Embedded SQL depending on the value assigned to the sqltype field. This field specifies the required size of the host variable.
The sqltype field also specifies whether or not a indicator variable is required or is supplied. This is shown by the type value being negated.
The SQLDA is a special type of host variable structure. To ensure that the structure has the correct format, the application program should use the definition of the SQLDA provided by Adabas SQL Gateway Embedded SQL. To facilitate this, an SQL statement like the following one should be embedded in the application.
EXEC SQL
INCLUDE SQLDA AS sqlda_ptr;
This statement has the effect of generating a declaration of a variable sqlda_ptr at the point where it is specified. This variable can then be used as a pointer to a descriptor area.
When using an SQLDA to retrieve descriptive information from Adabas SQL Gateway Embedded SQL either for input or output purposes, the application program normally does not know the number of variable descriptions required. The application program, however, has to allocate an SQLDA of a certain dimension before the PREPARE or DESCRIBE statements can be issued. In general, there are two techniques which can be used:
The application program allocates an SQLDA of maximum size required for the maximum possible number of derived column list elements or host variable markers. This might cause a significant waste of storage if the maximum has to be set very high.
The application program allocates an SQLDA of minimum size. The dimension of the SQLDA is determined by the sqln element in the SQLDA header. If the number of derived column list elements or host variable markers exceeds this number, Adabas SQL Gateway Embedded SQL will refrain from attempting to provide information on the remaining elements or markers. Adabas SQL Gateway Embedded SQL, however, does return the correct number of elements in the sqld element of the SQLDA. The application program can then use this number to allocate a new SQLDA of sufficient size and re-issue the PREPARE or DESCRIBE statement. The application program must explicitly have an SQLDA declaration such that the resulting structure is in scope for all SQL statements which access it. Such a declaration does not need to be in a BEGIN DECLARE SECTION.
Although the SQLDA does not return explicitly the SQL statement type, enough information is returned in the SQLDA for the application program to determine whether or not the dynamic statement is a SELECT statement. If the field sqln is 0, the statement did not contain a derived column list and must therefore be a NON-SELECT statement.