C-Structure SQLDA

SQLDA is a C language structure used to store a query result set. A structure stores a record of a result set.

EXEC SQL include sqlda.h; 
sqlda_t         *mysqlda;  
EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;

Note that the SQL keyword is omitted. The paragraphs about the use cases of the INTO and USING keywords in section Named SQLDA also apply here. In a DESCRIBE statement, if the INTO keyword is used, the DESCRIPTOR keyword can be omitted.

EXEC SQL DESCRIBE prepared_statement INTO mysqlda;

Procedure
1. Prepare a query and declare a cursor for it.
2. Declare an SQLDA for the result row.
3. Declare SQLDA for input parameters, initialize parameters, and allocate memory.
4. Open a cursor with the input SQLDA.
5. Fetch rows from the cursor and store them in the output SQLDA.
6. Read the value from the output SQLDA to the host variable.
7. Close the cursor.
8. Deallocate the memory allocated to the SQLDA.
There are three types of SQLDA data structures: sqlda_t, sqlvar_t, and struct sqlname.
1. sqlda_t
  The definition of sqlda_t is as follows:
```
struct sqlda_struct
{
    char            sqldaid[8];
    long            sqldabc;
    short           sqln;
    short           sqld;
    struct sqlda_struct *desc_next;
    struct sqlvar_struct sqlvar[1];
};
typedef struct sqlda_struct sqlda_t;
```
  The structure members are described as follows:
  - sqldaid: contains a string "SQLDA".
  - sqldabc: contains the size (in bytes) of the allocated space.
  - sqln: contains the number of input parameters for a parameterized query in case it is passed into OPEN, DECLARE or EXECUTE statements using the USING keyword. When it is used as the output of a SELECT, EXECUTE, or FETCH statement, its value is the same as that of sqld.
  - sqld: contains the number of fields in a result set.
  - desc_next: If the query returns more than one record, multiple linked SQLDA structures are returned, and desc_next holds a pointer to the next SQLDA structure in the list.
  - sqlvar: indicates the array of the columns in the result set.
2. sqlvar_t
  The structure type sqlvar_t holds a column value and metadata (such as type and length). The definition of this type is as follows:
```
struct sqlvar_struct
{
    short          sqltype;
    short          sqllen;
    char          *sqldata;
    short         *sqlind;
    struct sqlname sqlname;
};
typedef struct sqlvar_struct sqlvar_t;
```
  The structure members are described as follows:
  - sqltype: contains the type identifier of the field.
  - sqllen: contains the binary length of the field, for example, 4 bytes for ECPGt_int.
  - sqldata: points to the data. For details about the data format, see Type Mapping.
  - sqlind: points to a null indicator. The value 0 indicates not null, and the value –1 indicates null.
  - sqlname: indicates the name of the field.
3. struct sqlname
  A struct sqlname structure holds a column name. It is treated as a member of the sqlvar_t structure. The definition of this type is as follows:
```
#define NAMEDATALEN 64
struct sqlname
{
        short           length;
        char            data[NAMEDATALEN];
};
```
  The structure members are described as follows:
  - length: contains the length of the field name.
  - data: contains the actual field name.

Use an SQLDA to retrieve a result set.

The general procedure for retrieving a query result set through an SQLDA is as follows:

Declare an sqlda_t structure to receive the result set.
Execute the FETCH, EXECUTE, or DESCRIBE command to process a query for which an SQLDA has been declared.
Check the number of records in the result set by looking at sqln, a member of the sqlda_t structure.
Fetch the values of each column from sqlvar[0], sqlvar[1], ..., members of the sqlda_t structure.
Go to next row (sqlda_t) by following the desc_next pointer, a member of the sqlda_t structure.
Repeat the preceding steps as required.

Example:

/* Declare an sqlda_t structure to receive the result set. */
sqlda_t *sqlda1;
/* Next, specify an SQLDA in a command. This is an example of the FETCH command. */
EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
/* Run a loop to retrieve rows along the linked list. */
sqlda_t *cur_sqlda;
for (cur_sqlda = sqlda1;
     cur_sqlda != NULL;
     cur_sqlda = cur_sqlda->desc_next)
{
    ...
}
/* Inside the loop, run another loop to retrieve the data of each column in the row (sqlvar_t). */
for (i = 0; i < cur_sqlda->sqld; i++)
{
    sqlvar_t v = cur_sqlda->sqlvar[i];
    char *sqldata = v.sqldata;
    short sqllen  = v.sqllen;
    ...
}
/* To fetch the values of a column, check the value of the sqltype member of the sqlvar_t structure. Then, switch to an appropriate method based on the column type to copy data from the sqlvar field to a host variable. */
char var_buf[1024];
switch (v.sqltype)
{
    case ECPGt_char:
        memset(&var_buf, 0, sizeof(var_buf));
        memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen));
        break;

    case ECPGt_int: 
        memcpy(&intval, sqldata, sqllen);
        snprintf(var_buf, sizeof(var_buf), "%d", intval);
        break;
    ...
}

Use an SQLDA to pass query parameters.

The general procedure for passing input parameters to a prepared query using an SQLDA is as follows:

Create a prepared query (prepared statement).
Declare an sqlda_t structure as an SQLDA.
Allocate a memory area for the SQLDA.
Set (copy) the input values in the allocated memory.
Open a cursor declaring the SQLDA.

Example:

/* First, create a prepared statement. */
EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)";
EXEC SQL END DECLARE SECTION;
    EXEC SQL PREPARE stmt1 FROM :query;

    /* Allocate memory for an SQLDA and set the number of input parameters in the sqln member variable of the sqlda_t structure.
    * When the prepared query requires two or more input parameters, the application must allocate extra memory space. The space size is calculated as follows: (Number of parameters – 1) x sizeof(sqlvar_t).
    * The example here shows how to allocate memory space for two input parameters.
     */
    sqlda_t *sqlda2;
    sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
    sqlda2->sqln = 2; /* Number of input variables */
    /* After memory allocation, store the parameter values into the sqlvar[] array. (This is same array used for retrieving column values when the SQLDA is receiving a result set.)
    * In this example, the input parameters are postgres (string type) and 1 (integer type). */
    sqlda2->sqlvar[0].sqltype = ECPGt_char;
    sqlda2->sqlvar[0].sqldata = "postgres";
    sqlda2->sqlvar[0].sqllen  = 8;
    int intval = 1;
    sqlda2->sqlvar[1].sqltype = ECPGt_int;
    sqlda2->sqlvar[1].sqldata = (char *) &intval;
    sqlda2->sqlvar[1].sqllen  = sizeof(intval);
    /* Input parameters are passed to the prepared statement by opening a cursor and declaring the SQLDA that has been created. */
    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
    /* Finally, the allocated memory must be explicitly released after you use the input SQLDA, which is different from the SQLDA used to receive query results. */
    free(sqlda2);

Parent topic: SQL Descriptor Area

Previous topic: Named SQLDA

Next topic: Examples