Example 1: Executing an INSERT statement with parameter markers
These fragments from the online sample C program sql_dynamic
illustrate using an EXECUTE statement in an SQL module procedure
to execute a dynamically generated SQL statement.
The program accepts input of any valid SQL statement from the
terminal and calls the subunit shown in the following program
excerpt:
.
.
.
/*
**----------------------------------------------------------------------------
** Begin Main routine
**----------------------------------------------------------------------------
*/
int sql_dynamic (psql_stmt, input_sqlda, output_sqlda, stmt_id, is_select)
char *psql_stmt;
sqlda *input_sqlda;
sqlda *output_sqlda;
long *stmt_id;
int *is_select;
{
sqlda sqlda_in, sqlda_out; /* Declare the SQLDA structures. */
int rowcount, status;
int param;
/* Declare arrays for storage of original data types and allocate memory. */
mem_ptr output_save;
mem_ptr input_save;
/* * If a NULL SQLDA is passed, then a new statement is being prepared. */
if ((*input_sqlda == NULL) && (*output_sqlda == NULL))
{
new_statement = TRUE;
/*
* Allocate separate SQLDAs for input parameter markers (SQLDA_IN)
* and output list items (SQLDA_OUT). Assign the value of the constant
* MAXPARMS to the SQLN field of both SQLDA structures. SQLN specifies to
* SQL the maximum size of the SQLDA.
*/
if ((sqlda_in = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0)
{
printf ("\n\n*** Error allocating memory for sqlda_in: Abort");
return (-1);
}
else /* set # of possible parameters */
sqlda_in->sqln = MAXPARAMS;
if ((sqlda_out = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0)
{
printf ("\n\n*** Error allocating memory for sqlda_out: Abort");
return (-1);
}
}
else
/* Set # of possible select list items. */
sqlda_out->sqln = MAXPARAMS;
/* copy name SQLDA2 to identify the SQLDA */
strncpy(&sqlda_in->sqldaid[0],"SQLDA2 ",8);
strncpy(&sqlda_out->sqldaid[0],"SQLDA2 ",8);
/*
* Call an SQL module language procedure, prepare_stmt and
* describe_stmt that contains a PREPARE and DESCRIBE...OUTPUT
* statement to prepare the dynamic statement and write information
* about any select list items in it to SQLDA_OUT.
*/
*stmt_id = 0; /* If <> 0 the BADPREPARE error results in the PREPARE.*/
PREPARE_STMT (&SQLCA, stmt_id, psql_stmt);
if (SQLCA.SQLCODE != sql_success)
{
printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE",
SQLCA.SQLCODE);
display_error_message();
return (-1);
}
DESCRIBE_SELECT (&SQLCA, stmt_id, sqlda_out);
if (SQLCA.SQLCODE != sql_success)
{
printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE",
SQLCA.SQLCODE);
display_error_message();
return (-1);
}
/*
* Call an SQL module language procedure, describe_parm, that contains a
* DESCRIBE...INPUT statement to write information about any parameter
* markers in the dynamic statement to sqlda_in.
*/
DESCRIBE_PARM (&SQLCA, stmt_id, sqlda_in);
if (SQLCA.SQLCODE != sql_success)
{
printf ("\n\n*** Error %d returned from describe_parm: Abort",
SQLCA.SQLCODE);
display_error_message();
return (-1);
}
/* Save the value of the SQLCA.SQLERRD[1] field so that program can
* determine if the statement is a SELECT statement or not.
* If the value is 1, the statement is a SELECT statement.*/
*is_select = SQLCA.SQLERRD[1];
.
.
.
/*
* Check to see if the prepared dynamic statement contains any parameter
* markers by looking at the SQLD field of sqlda_in. SQLD contains the
* number of parameter markers in the prepared statement. If SQLD is
* positive, the prepared statement contains parameter markers. The program
* executes a local procedure, get_in_params, that prompts the user for
* values, allocates storage for those values, and updates the SQLDATA field
* of sqlda_in:
*/
if (sqlda_in->sqld > 0)
if ((status = get_in_params(sqlda_in,input_save)) != 0)
{
printf ("\nError returned from GET_IN_PARAMS. Abort");
return (-1);
}
/* Check to see if the prepared dynamic statement is a SELECT by looking
* at the value in is_select, which stores the value of the
* SQLCA.SQLERRD[1] field. If that value is equal to 1, the prepared
* statement is a SELECT statement. The program allocates storage for
* rows for SQL module language procedures to open and fetch from a cursor,
* and displays the rows on the terminal:
*/
if (*is_select)
{
if (new_statement == TRUE) /* Allocate buffers for output. */
{
/* assign a unique name for the cursor */
sprintf(cursor_name,"%2d",++cursor_counter);
if ((status = allocate_buffers(sqlda_out)) != 0)
.
.
.
/*
* If the SQLCA.SQLERRD[1] field is not 1, then the prepared statement is not a
* SELECT statement and only needs to be executed. Call an SQL module language
* procedure to execute the statement, using information about parameter
* markers stored in sqlda_in by the local procedure get_in_params:
*/
{
EXECUTE_STMT (&SQLCA, stmt_id, sqlda_in);
if (SQLCA.SQLCODE != sql_success)
.
.
.
The SQL module language procedures called by the preceding
fragment:
.
.
.
-------------------------------------------------------------------------------
-- Procedure Section
-------------------------------------------------------------------------------
-- This procedure prepares a statement for dynamic execution from the string
-- passed to it. It also writes information about the number and data type of
-- any select list items in the statement to an SQLDA2 (specifically,
-- the sqlda_out SQLDA2 passed to the procedure by the calling program).
--
PROCEDURE PREPARE_STMT
SQLCA
:DYN_STMT_ID INTEGER
:STMT CHAR(1024);
PREPARE :DYN_STMT_ID FROM :STMT;
-- This procedure writes information to an SQLDA (specifically,
-- the sqlda_in SQLDA passed to the procedure by the calling program)
-- about the number and data type of any parameter markers in the
-- prepared dynamic statement. Note that SELECT statements may also
-- have parameter markers.
PROCEDURE DESCRIBE_SELECT
SQLCA
:DYN_STMT_ID INTEGER
SQLDA;
DESCRIBE :DYN_STMT_ID OUTPUT INTO SQLDA;
PROCEDURE DESCRIBE_PARM
SQLCA
:DYN_STMT_ID INTEGER
SQLDA;
DESCRIBE :DYN_STMT_ID INPUT INTO SQLDA;
-- This procedure dynamically executes a non-SELECT statement.
-- SELECT statements are processed by DECLARE CURSOR, OPEN CURSOR,
-- and FETCH statements.
--
-- The EXECUTE statement specifies an SQLDA2 (specifically,
-- the sqlda_in SQLDA2 passed to the procedure by the calling program)
-- as the source of addresses for any parameter markers in the dynamic
-- statement.
--
-- The EXECUTE statement with the USING DESCRIPTOR clause
-- also handles statement strings that contain no parameter markers.
-- If a statement string contains no parameter markers, SQL sets
-- the SQLD field of the SQLDA2 to zero.
PROCEDURE EXECUTE_STMT
SQLCA
:DYN_STMT_ID INTEGER
SQLDA;
EXECUTE :DYN_STMT_ID USING DESCRIPTOR SQLDA;
.
.
.