Defining SQL descriptor areas in C and C++ applications that use SQL

There are two types of SQL descriptor areas. One is defined with the ALLOCATE DESCRIPTOR statement. The other is defined using the SQL descriptor area (SQLDA) structure. In this topic, only the SQLDA form is discussed.

The following statements can use an SQLDA:

EXECUTE…USING DESCRIPTOR descriptor-name
FETCH…USING DESCRIPTOR descriptor-name
OPEN…USING DESCRIPTOR descriptor-name
DESCRIBE statement-name INTO descriptor-name
DESCRIBE CURSOR cursor-name INTO descriptor-name
DESCRIBE INPUT statement-name INTO descriptor-name
DESCRIBE PROCEDURE procedure-name INTO descriptor-name
DESCRIBE TABLE host-variable INTO descriptor-name
PREPARE statement-name INTO descriptor-name
CALL…USING DESCRIPTOR descriptor-name

Unlike the SQLCA, more than one SQLDA can be in the program, and an SQLDA can have any valid name. The following list includes the statements that require a SQLDA. You can code an SQLDA in a C or C++ program either directly or by using the SQL INCLUDE statement. Using the SQL INCLUDE statement requests the inclusion of a standard SQLDA declaration:

  EXEC SQL INCLUDE SQLDA;

A standard declaration includes only a structure definition with the name 'sqlda'.

C and C++ declarations that are included for the SQLDA are:

  struct sqlda {
                unsigned char sqldaid[8];
                long sqldabc;
                short sqln;
                short sqld;
                struct sqlvar {
                              short sqltype;
                              short sqllen;
                              union {
                                  unsigned char *sqldata;
                                  long long sqld_result_set_locator;  };
                              union {
                                  short *sqlind;
                                  long sqld_row_change;
                                  long sqld_result_set_rows;   ];
                              struct sqlname {
                                             short length;
                                             unsigned char data[30];
                                             } sqlname;
                             } sqlvar[1];
                };

One benefit from using the INCLUDE SQLDA SQL statement is that you also get the following macro definition:

#define SQLDASIZE(n) (sizeof(struct sqlda) + (n-1)* sizeof(struc sqlvar))

This macro makes it easy to allocate storage for an SQLDA with a specified number of SQLVAR elements. In the following example, the SQLDASIZE macro is used to allocate storage for an SQLDA with 20 SQLVAR elements.

  #include <stdlib.h>
  EXEC SQL INCLUDE SQLDA;
 
  struct sqlda *mydaptr;
  short numvars = 20;
    .
    .
  mydaptr = (struct sqlda *) malloc(SQLDASIZE(numvars));
  mydaptr->sqln = 20;

Here are other macro definitions that are included with the INCLUDE SQLDA statement:

GETSQLDOUBLED(daptr): Returns 1 if the SQLDA pointed to by daptr has been doubled, or 0 if it has not been doubled. The SQLDA is doubled if the seventh byte in the SQLDAID field is set to '2'.
SETSQLDOUBLED(daptr, newvalue): Sets the seventh byte of SQLDAID to a new value.
GETSQLDALONGLEN(daptr,n): Returns the length attribute of the nth entry in the SQLDA to which daptr points. Use this only if the SQLDA was doubled and the nth SQLVAR entry has a LOB data type.
SETSQLDALONGLEN(daptr,n,len): Sets the SQLLONGLEN field of the SQLDA to which daptr points to len for the nth entry. Use this only if the SQLDA was doubled and the nth SQLVAR entry has a LOB datatype.
GETSQLDALENPTR(daptr,n): Returns a pointer to the actual length of the data for the nth entry in the SQLDA to which daptr points. The SQLDATALEN pointer field returns a pointer to a long (4 byte) integer. If the SQLDATALEN pointer is zero, a NULL pointer is returned. Use this only if the SQLDA has been doubled.
SETSQLDALENPTR(daptr,n,ptr): Sets a pointer to the actual length of the data for the nth entry in the SQLDA to which daptr points. Use this only if the SQLDA has been doubled.

When you have declared an SQLDA as a pointer, you must reference it exactly as declared when you use it in an SQL statement, just as you would for a host variable that was declared as a pointer. To avoid compiler errors, the type of the value that is assigned to the sqldata field of the SQLDA must be a pointer of unsigned character. This helps avoid compiler errors. The type casting is only necessary for the EXECUTE, OPEN, CALL, and FETCH statements where the application program is passing the address of the host variables in the program. For example, if you declared a pointer to an SQLDA called mydaptr, you would use it in a PREPARE statement as:

  EXEC SQL PREPARE mysname INTO :*mydaptr FROM :mysqlstring;

SQLDA declarations can appear wherever a structure definition is allowed. Normal C scope rules apply.

Dynamic SQL is an advanced programming technique. With dynamic SQL, your program can develop and then run SQL statements while the program is running. A SELECT statement with a variable SELECT list (that is a list of the data to be returned as part of the query) that runs dynamically requires an SQL descriptor area (SQLDA). This is because you will not know in advance how many or what type of variables to allocate in order to receive the results of the SELECT.