SQL/MP Programming Manual for C

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for C—429847-008
10-22
Using Dynamic SQL Cursors
Example 10-3 shows the use of statement and cursor host variables. The program in
this example is a server that does repetitive processing using a restricted set of
operations. For example, the program might handle a SELECT statement for which the
user can enter any of three different WHERE clauses. When the server is started, you
might run the PREPARE, DESCRIBE INPUT, DESCRIBE, and DECLARE CURSOR
statements once for each possible version of the statement.
Example 10-3. Using Statement and Cursor Host Variables
#define MAX_STRGS 3
#define STRING_LEN 81
...
EXEC SQL BEGIN DECLARE SECTION;
char s_hostvar[STRING_LEN]; /* statement host variable*/
char c_hostvar[STRING_LEN]; /* cursor host variable */
char t[MAX_STRGS] [STRING_LEN]; /* statements table */
char c[MAX_STRGS] [STRING_LEN]; /* cursors table */
...
/* Store the statements in table t and the cursors in table c */
...
for (i = 1; i <= MAX_STRGS; i++)
{
EXEC SQL PREPARE :s_hostvar FROM :t[i];
EXEC SQL DESCRIBE INPUT :s_hostvar INTO :input_sqlda;
/* Call a function to handle the input parameters */
...
EXEC SQL DESCRIBE :s_hostvar INTO :output_sqlda;
/* Call a function to handle the output variables */
...
EXEC SQL DECLARE CURSOR :c[i] CURSOR FOR :s_hostvar;
}
You now have three cursor variables, one for each of your three possible
statements. You set up the cursors only once, but your program can now use
them repeatedly, as follows:
EXEC SQL BEGIN DECLARE SECTION;
char cur [string_len]; /* Cursor local variable */
EXEC SQL END DECLARE SECTION;
/* Loop while not EOF: */
/* Read $RECEIVE */
...
/* Examine a flag in request message to determine which */
/* cursor to use, and assign appropriate value to cur:*/
/* c[0], c[1], or c[2] */
...
EXEC SQL OPEN :cur;
EXEC SQL FETCH :cur INTO column host variables;
EXEC SQL CLOSE :cur;
/* Call a function to display results */
/* End of loop WHILE not EOF */