Method 3: Query Statements Without Parameters (Embedded SQL Guide)

Go Up to DSQL Programming Methods (Embedded SQL Guide)

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 an XSQLDA called out_sqlda:
```
XSQLDA *out_sqlda;
```
Optionally, declare a variable for accessing the XSQLVAR structure of the XSQLDA:
```
XSQLVAR *var;
```
Declaring a pointer to the XSQLVAR structure is not necessary, but can simplify referencing the structure in subsequent statements.
Allocate memory for the XSQLDA using the XSQLDA_LENGTH macro. The following statement allocates storage for out_sqlda:
```
out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(10));
```
Space for 10 XSQLVAR structures is allocated in this statement, enabling the XSQLDA to accommodate up to 10 select-list items.
Set the version field of the XSQLDA to SQLDA_CURRENT_VERSION, and set the sqln field of the XSQLDA to indicate the number of XSQLVAR 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 as DESCRIBE and EXECUTE:
```
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 the sqld 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 by sqld, reset sqln and version, then execute DESCRIBE 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 the XSQLVAR. 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.

The following code example illustrates these steps, looping through each XSQLVAR structure in the out_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.

Advance To:

Method 4: Query Statements with Parameters