Preparing the SELECT Statement to Read Data from an Array for Execution

From InterBase

Go Up to Reading Data from an Array


After an XSQLDA is created for holding the column data for each selected row, the query statement string can be prepared for execution. Follow these steps:

  1. Declare and initialize a SQL statement handle, then allocate it with isc_dsql_allocate_statement(): 
    isc_stmt_handle stmt; /* Declare a statement handle. */
stmt = NULL; /* Set handle to NULL before allocation. */
isc_dsql_allocate_statement(status_vector, &db_handle, &stmt);
  2. Ready the statement string for execution with isc_dsql_prepare(). This checks the string (sel_str) for syntax errors, parses it into a format that can be efficiently executed, and sets the statement handle (stmt) to refer to this parsed format. The statement handle is used in a later call to ­isc_dsql_execute(). If isc_dsql_prepare() is passed a pointer to the output XSQLDA, as in the following example, it will fill in most fields of the XSQLDA and all its XSQLVAR substructures with information such as the data type, length, and name of the corresponding columns in the statement. A sample call to isc_dsql_prepare() is: 
    isc_dsql_prepare( status_vector,
&trans, /* Set by previous isc_start_transaction() call. */
&stmt, /* Statement handle set by this function call. */
0, /* Specifies statement string is null-terminated. */
sel_str, /* Statement string. */
1, /* da_version number. */
out_sqlda); /* XSQLDA for storing column data. */
  3. Set up an XSQLVAR structure for each column. Setting up an XSQLVAR structure involves the following steps: For columns whose types are known at compile time:
    • Specify the data type of the column (if it was not set by ­isc_dsql_prepare(), as previously described).
    • Point the sqldata field of the XSQLVAR to an appropriate local ­variable.
    For columns whose types are not known until run time:
    • Coerce the data type of the item (optional); for example, from ­SQL_VARYING to SQL_TEXT.
    • Dynamically allocate local storage for the data pointed to by the ­sqldata field of the XSQLVAR.
    For both: Provide a NULL value indicator for the parameter.
    • Data retrieval for array (and Blob) columns is different from other types of columns, so the XSQLVAR fields must be set differently. For non-array (and non-Blob) columns, isc_dsql_prepare() sets each XSQLVAR sqltype field to the appropriate field type, and the data retrieved when a data of a select list row is fetched is placed into the sqldata space allocated for the column. For array columns, the type is set to ­SQL_ARRAY (or ­SQL_ARRAY + 1 if the array column is allowed to be NULL). InterBase stores the internal array identifier (array ID), not the array data, in the sqldata space when a row’s data is fetched, so you must point sqldata to an area the size of an array ID. To see how to retrieve the actual array or array slice data once you have an array ID, see Reading and Processing the Array Data.
    • The following code example illustrates the assignments for array and non-array columns whose types are known at compile time. For more information about DSQL and the XSQLDA, and working with columns whose types are unknown until run time, see Working with Dynamic SQL.
    ISC_QUAD array_id = 0L;
char dept_no[6];
short flag0, flag1;
out_sqlda->sqlvar[0].sqldata = (char *) dept_no;
out_sqlda->sqlvar[0].sqltype = SQL_TEXT + 1;
out_sqlda->sqlvar[0].sqlind = &flag0;
out_sqlda->sqlvar[1].sqldata = (char *) &array_id;
out_sqlda->sqlvar[1].sqltype = SQL_ARRAY + 1;
out_sqlda->sqlvar[1].sqlind = &flag1;

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Preparing_the_SELECT_Statement_to_Read_Data_from_an_Array_for_Execution&oldid=31918"