As a basic rule, dynamic SQL statements with a descriptor must be prepared with the prepare statement. A describe statement issued on the same SQL statement must follow immediately to ensure that during runtime the SQLDA will be provided with the necessary information about the columns to be processed.
The next step depends on the application programming. The ADABAS application must ensure that the SQL statement is provided with appropriate program variables (actually their addresses) as parameters by using the information about the columns stored in the SQLDA. Generally, this will be a distinction of cases by means of which a program variable is to be selected for the ADABAS column and its address is to be entered into the SQLDA.
Since parameters can also be included in conditions of SQL statements or serve to provide columns with values, it is necessary to assign values to the corresponding program variables at this point in time.
Finally, the dynamic SQL statement can be executed via the execute statement. The additional specification 'using descriptor' must be included in the execute statement only if the SQL statement to be executed contains question marks in place of parameter values.
In the following examples, the four steps
1) execution of the prepare statement,
2) execution of the describe statement,
3) association of the SQL parameters with program variables, and
4) execution (execute) of the dynamic SQL statement
which are necessary for the usage of the descriptor are presented in detail. The first example illustrates the usage of the descriptor only with output parameters and unnamed result tables, the second example also includes input parameters and named result tables.
Example (with output parameters):
An ADABAS application allows interactive queries to a database. For this purpose, the user has to enter a select statement such as follows:
select * from reservation
select rno,arrival,departure from reservation where hno = 25
select name from hotel where zip=20005 and price<100.00
etc.
This can be formulated within a program in the following manner:
Executing the select statement which was read from the screen generates a result table of an unknown structure. This fact must be taken into consideration when processing the result table.
At execution time of the fetch statement, it must be clear into which program variables the column contents are to be transferred. Therefore the program variables must previously be made known to the fetch statement, which is done by means of the descriptor.
The result table is processed in the following manner:
Processing the result table:
1) Executing the prepare statement with the parameter 'fetch using descriptor'. For this purpose, the fetch statement is to be treated like a dynamic SQL statement, i.e., it must be given a <statement name>.
2) Calling the describe statement with the <statement name> of the fetch statement. The descriptor SQLDA is provided with information about the columns to be processed.
3) Using this information, the addresses of appropriate program variables can now be assigned to the SQLDA (set_describe_area () ).
4) Since the program variables are stipulated into which the column contents are to be returned, the fetch statement can now be executed (execute). Subsequently, the corresponding program variables contain the results of the fetch statement.
After processing the first table row (data_processing () ), all the following rows are processed within 'fetch-sequence'.
The following example illustrates how appropriate program variables can be assigned to the SQLDA:
The example shows exactly those program variables into which the column contents will be returned. Note that the ADABAS column type must be compatible with the corresponding variable definition. For this reason, variables have been declared in order to receive both character strings of a maximum length of 40 and 80 characters and numbers of different storage and representation. The fact that each type is available in the form of 10 program variables signifies that only SQL statements with up to 10 parameters can be processed. This limitation applies only to the present example. 'i' identifies the i-th column in a table and the 'SQLVAR' entry in the SQLDA assigned to it.
The information which the describe statement returns to the SQLDA is checked within the function 'set_describe_area'. The SQLDA is then provided with specifications regarding the program variables. This is illustrated by the following pseudo code section:
Executed within a loop, one program variable is assigned to each column of a table which may consist of up to 10 columns. The table contents can subsequently be transferred and processed by rows.
Example (with input/output parameters):
This example illustrates how input and output parameters can be processed by means of the descriptor.
For this purpose, a select statement of the form
select * from hotel
where zip = ? and price <= ? order by price;
is executed. Interactive entries made at runtime decide which zip code and which price limit are concerned. The ADABAS application searches the database for hotels of a particular price category in one particular city.
The selection of the program variables used to provide the where clause with values is considerably simplified in the present example. If the search condition has to remain variable, the program variables must be selected and provided with values according to the information about the columns stored in the SQLDA. When calling the execute statement, 'descriptor' is specified in the using part instead of a list of host variables.
The result table can be processed again with fetch using descriptor.
Another possibility is to process the result table by means of a cursor:
Using a cursor for processing a result table has the effect that no execute statement needs to be issued on to the 'select', because calling the open statement actually executes the select statement. If question marks are included in a select statement in order to identify parameters, the open statement must be called with 'using descriptor' (instead of the host variable list).