DB2 Server for VSE & VM: Application Programming

Using an Alternative to a Scanning Routine

In the previous sections on parameterized statements (both query and non-query), you must rely on a scanning routine to generate a query. Once the query is generated, DESCRIBE obtains information about the columns and expressions associated with a parameter marker.

If you have not coded a scanning routine that generates a query, there is a simple alternative: have the user describe the parameter markers for you, and fill in the SQLDA yourself. There is no rule that says you must use a DESCRIBE to fill in the SQLDA. When using the SQLDA for input or output, it does not matter what fills it in, as long as the needed values are there.

When you use the SQLDA for input (which is always the case for parameter markers), not all fields have to be filled in. Specifically, SQLDAID and SQLDABC need not be filled in. Thus, if you choose this method, you will need to ask the user for the following:

1. How many parameter markers (?) are there?
2. What are the data types and lengths of these parameters?

In addition, if the routine is to handle both query and non-query statements, you may want to ask the user what category of statement it is. (Alternatively, you can write code to look for the SELECT keyword.)

The code that interrogates the user and sets up the SQLDA would take the place of the scanning routine and DESCRIBE in the previous sections:

   With a Scanning Routine:
 
          .
          .
          .
   READ DSTRING FROM TERMINAL
   Scan the FROM and WHERE clauses of DSTRING for parameter markers (?)
   and generate an appropriate query in WSTRING.
   Allocate an SQLDA of size 2 (2 was obtained from the scan).
   SQLN = 2
       EXEC SQL PREPARE S2 FROM :WSTRING
       EXEC SQL DESCRIBE S2 INTO SQLDA
   Analyze the results of the DESCRIBE.
   Reset SQLTYPE to reflect that there is no indicator variable.
   Allocate storage to hold the input values
      (the parameter marker (?) values).
   Set SQLDATA for each parameter marker (?) value.
          .
          .
          .

   Without a Scanning Routine:
 
          .
          .
          .
   READ DSTRING FROM TERMINAL
   Interrogate user for number of parameter markers (?).
   Allocate an SQLDA of that size.
   Set SQLN and SQLD to the number of parameter markers (?).
   For each parameter marker (?):
       Interrogate user for data types, lengths, and
         indicators.
       Set SQLTYPE and SQLLEN.
       Allocate storage to hold the input values
         (the parameter marker (?) values).
       Set SQLDATA and SQLIND (if applicable) for each
         parameter marker (?).
          .
          .
          .

The statement can then be processed in the usual manner.