Method 4 example using SQLDAs

Following is an example that uses prepare and fetch with dynamic parameter markers and SQL descriptors.

exec sql include sqlca;

 exec sql include sqlda;

...

SQLDA *input_descriptor, *output_descriptor;

 CS_SMALLINT small;

 CS_CHAR     character[20];

input_descriptor = (SQLDA *)malloc(SYB_SQLDA_SIZE(3));

 input_descriptor->sqlda_sqln = 3;

 output_descriptor = (SQLDA *)malloc(SYB_SQLDA_SIZE(3));

 output_descriptor->sqlda_sqln = 3;

*p_retcode = CS_SUCCEED;

exec sql connect “sa” identified by ““;

/* setup */

exec sql drop table example;

exec sql create table example (fruit char(30), number int);

exec sql insert example values (‘tangerine’, 1);

exec sql insert example values (‘pomegranate’, 2);

exec sql insert example values (‘banana’, 3);

/* Prepare and describe the select statement */

 exec sql prepare statement from

     “select fruit from example where number = ?”;

exec sql describe input statement using descriptor	    input_descriptor;

input_descriptor->sqlda_column[0].sqlda_datafmt.datatype =     CS_SMALLINT_TYPE;

input_descriptor->sqlda_column[0].sqlda_sqldata = &small;

input_descriptor->sqlda_column[0].sqlda_sqllen = sizeof(small);

small = 2;

exec sql describe output statement using descriptor

     output_descriptor;

if (output_descriptor->sqlda_sqld != 1 ||

     output_descriptor->sqlda_column[0].sqlda_datafmt.datatype !=     CS_CHAR_TYPE)

     FAIL;

else

     printf(“First describe output \n”);

output_descriptor->sqlda_column[0].sqlda_sqldata = character;

output_descriptor->sqlda_column[0].sqlda_datafmt.maxlength = 20;

exec sql execute statement into descriptor output_descriptor

     using descriptor input_descriptor;

printf(“Expected pomegranate, got %s\n”, character);

exec sql deallocate prepare statement;

/* Prepare and describe second select statement */

exec sql prepare statement from

     “select number from example where fruit = ?”;

exec sql declare c cursor for statement;

exec sql describe input statement using descriptor

     input_descriptor;

input_descriptor->sqlda_column->sqlda_sqldata = character;

input_descriptor->sqlda_column->sqlda_datafmt.maxlength =     CS_NULLTERM;

strcpy(character, “banana”);

input_descriptor->sqlda_column->sqlda_sqllen = CS_NULLTERM;

exec sql open c using descriptor input_descriptor;

exec sql describe output statement using descriptor

     output_descriptor;

output_descriptor->sqlda_column->sqlda_sqldata = character;

output_descriptor->sqlda_column->sqlda_datafmt.datatype =     CS_CHAR_TYPE;

output_descriptor->sqlda_column->sqlda_datafmt.maxlength = 20;

output_descriptor->sqlda_column->sqlda_sqllen = 20;

output_descriptor->sqlda_column->sqlda_datafmt.format =

     (CS_FMT_NULLTERM | CS_FMT_PADBLANK);

exec sql fetch c into descriptor output_descriptor;

printf(“Expected pomegranate, got %s\n”, character);

exec sql commit work;