Method 3: Query Statements Without Parameters (Embedded SQL Guide)
Go Up to DSQL Programming Methods (Embedded SQL Guide)
Contents
There are three steps to processing a SQL query statement string without parameters:
- Preparing an output
XSQLDA
to process the select-list items returned when the query is executed. - Preparing the statement string.
- Using a cursor to execute the statement and retrieve select-list items from the output
XSQLDA
.
Preparing the Output XSQLDA to Query Statements Without Parameters (Embedded SQL Guide)
Most queries return one or more rows of data, referred to as a select-list
. Because the number and kind of items returned are unknown when a statement string is created, an output XSQLDA
must be created to store select-list items that are returned at run time. To prepare the XSQLDA
, follow these steps:
- Declare a variable to hold the
XSQLDA
needed to store the column data for each row that will be fetched. For example, the following declaration creates anXSQLDA
calledout_sqlda
:XSQLDA *out_sqlda;
- Optionally, declare a variable for accessing the
XSQLVAR
structure of theXSQLDA
:XSQLVAR *var;
Declaring a pointer to theXSQLVAR
structure is not necessary, but can simplify referencing the structure in subsequent statements. - Allocate memory for the
XSQLDA
using theXSQLDA_LENGTH
macro. The following statement allocates storage forout_sqlda
:out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(10));
Space for 10XSQLVAR
structures is allocated in this statement, enabling theXSQLDA
to accommodate up to 10 select-list items. - Set the
version
field of theXSQLDA
toSQLDA_CURRENT_VERSION
, and set thesqln
field of theXSQLDA
to indicate the number ofXSQLVAR
structures allocated:out_sqlda->version = SQLDA_CURRENT_VERSION; out_sqlda->sqln = 10;
Preparing a Query Statement String
After an XSQLDA is created for holding the items returned by a query statement string, the statement string can be created, prepared, and described. When a statement string is executed, InterBase creates the select-list of selected rows.
To prepare a query statement string, follow these steps:
- Elicit a statement string from the user or create one that contains the SQL statement to be processed. For example, the following statement creates a SQL statement string that performs a query:
char *str = "SELECT * FROM CUSTOMER";
The statement appears to have only one select-list item (*). The asterisk is a wildcard symbol that stands for all of the columns in the table, so the actual number of items returned equals the number of columns in the table. - Parse and name the statement string with
PREPARE
. The name is used in subsequent calls to statements such asDESCRIBE
andEXECUTE
:EXEC SQL PREPARE SQL_STMT FROM :str;
SQL_STMT
is the name assigned to the prepared statement string. - Use
DESCRIBE OUTPUT
to fill the output XSQLDA with information about the select-list items returned by the statement:EXEC SQL DESCRIBE OUTPUT SQL_STMT INTO SQL DESCRIPTOR out_sqlda;
- Compare the
sqln
field of the XSQLDA to thesqld
field to determine if the output descriptor can accommodate the number of select-list items specified in the statement. If not, free the storage previously allocated to the output descriptor, reallocate storage to reflect the number of select-list items specified bysqld
, resetsqln
andversion
, then executeDESCRIBE OUTPUT
again:if (out_sqlda->sqld > out_sqlda->sqln) { n = out_sqlda->sqld; free(out_sqlda); out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(n)); out_sqlda->sqln = n; out_sqlda->version = SQLDA_CURRENT_VERSION; EXEC SQL DESCRIBE OUTPUT SQL_STMT INTO SQL DESCRIPTOR out_sqlda; }
- Set up an
XSQLVAR
structure for each item returned. Setting up an item structure involves the following steps:- Coercing the data type of an item (optional).
- Allocating local storage for the data pointed to by the
sqldata
field of theXSQLVAR
. This step is only required if space for local variables is not allocated until run time. The following example illustrates dynamic allocation of local variable storage space. - Providing a
NULL
value indicator for the parameter.
XSQLVAR
structure in theout_sqlda
XSQLDA:for (i=0, var = out_sqlda->sqlvar; i < out_sqlda->sqld; i++, var++) { dtype = (var->sqltype & ~1) /* drop flag bit for now */ switch (dtype) { case SQL_VARYING: var->sqltype = SQL_TEXT; var->sqldata = (char *)malloc(sizeof(char)*var->sqllen + 2); break; case SQL_TEXT: var->sqldata = (char *)malloc(sizeof(char)*var->sqllen); break; case SQL_LONG: var->sqldata = (char *)malloc(sizeof(long)); break; . . . /* process remaining types */ } /* end of switch statements */ if (sqltype & 1) { /* allocate variable to hold NULL status */ var->sqlind = (short *)malloc(sizeof(short)); } } /* end of for loop */
For more information about data type coercion and NULL
indicators, see Coercing Data Types (Embedded SQL Guide).
Executing a Statement String Within the Context of a Cursor (Embedded SQL Guide)
o retrieve select-list items from a prepared statement string, the string must be executed within the context of a cursor. All cursor declarations in InterBase are fixed, embedded statements inserted into the application before it is compiled. DSQL application developers must anticipate the need for cursors when writing the application and declare them ahead of time.
A looping construct is used to fetch a single row at a time from the cursor and to process each select-list item (column) in that row before the next row is fetched.
To execute a statement string within the context of a cursor and retrieve rows of select-list items, follow these steps:
- Declare a cursor for the statement string. For example, the following statement declares a cursor,
DYN_CURSOR
, for the SQL statement string,SQL_STMT
:EXEC SQL DECLARE DYN_CURSOR CURSOR FOR SQL_STMT;
- Open the cursor:
EXEC SQL OPEN DYN_CURSOR;
Opening the cursor causes the statement string to be executed, and an active set of rows to be retrieved. For more information about cursors and active sets, see Working with Data. - Fetch one row at a time and process the select-list items (columns) it contains. For example, the following loops retrieve one row at a time from DYN_CURSOR and process each item in the retrieved row with an application-specific function (here called
process_column()
):while (SQLCODE == 0) { EXEC SQL FETCH DYN_CURSOR USING SQL DESCRIPTOR out_sqlda; if (SQLCODE == 100) break; for (i = 0; i < out_sqlda->sqld; i++) { process_column(out_sqlda->sqlvar[i]); } }
The
process_column()
function mentioned in this example processes each returned select-list item. The following skeleton code illustrates how such a function can be set up:void process_column(XSQLVAR *var) { /* test for NULL value */ if ((var->sqltype & 1) && (*(var->sqlind) = -1)) { /* process the NULL value here */ } else { /* process the data instead */ } . . . }
- When all the rows are fetched, close the cursor:
EXEC SQL CLOSE DYN_CURSOR;
Re-executing a Query Statement String
Once a query statement string without parameters is prepared, it can be executed as often as required in an application by closing and reopening its cursor.
To reopen a cursor and process select-list items, repeat steps 2–4 of Executing a Statement String Within the Context of a Cursor.