The following example uses prepare and fetch with dynamic parameter markers and SQL descriptors.
exec sql begin declare section
int index_colcnt, coltype;
int int_buff;
char char_buff[255], void_buff[255];
char type[255], title[255];
char colname[255];
int sales;
int descnt, occur, cnt;
int condcnt, diag_cnt, num_msgs;
char user_id[30], pass_id[30], server_name[30];
char str1[1024], str2[1024], str3[1024],
str4[1024];
exec sql end declare section;
...
void dyn_m4()
{
printf("\n\nDynamic sql Method 4\n");
printf("Enter in a Select statement to retrieve
any kind of ");
printf("information from the pubs database:");
scanf("%s", &str4);
printf("\nEnter the largest number of columns to
be retrieved or the number ");
printf("of ? in the sql statement:\n");
scanf("%d", &occur);
exec sql allocate descriptor dinout with max
:occur;
exec sql prepare s4 from :str4;
exec sql declare c2 cursor for s4;
exec sql describe input s4 using sql descriptor
dinout;
fill_descriptor();
exec sql open c2 using sql descriptor dinout;
while (sqlca.sqlcode == 0)
{
exec sql fetch c2 into sql descriptor dinout;
if(sqlca.sqlcode == 0) {
print_descriptor();
}
}
exec sql close c2;
exec sql deallocate descriptor dinout;
exec sql deallocate prepare s4;
printf("Dynamic SQL Method 4 completed\n\n");
}
void
print_descriptor()
{
exec sql get descriptor dinout :descnt = count;
printf("Column name \t\tColumn data\n");
printf("----------- \t\t-------------------------
----------------\n");
for (index_colcnt = 1; index_colcnt <= descnt;
index_colcnt++)
{ /* get each column attribute */
exec sql get descriptor dinout value
:index_colcnt :coltype = TYPE;
switch(coltype)
{
...
case 4:/* integer type */
exec sql get descriptor dinout value
:index_colcnt
:colname = NAME, :int_buff = DATA;
printf("%s \t\t %d\n", colname, int_buff);
break;
...
}
}
}
void
fill_descriptor()
{
exec sql get descriptor dinout :descnt = count;
for (cnt = 1; cnt <= descnt; cnt++)
{
printf("Enter in the data type of the %d ?:",
cnt);
scanf("%d", &coltype;);
switch(coltype)
{
...
case 4:/* integer type */
printf("Enter in the value of the data:");
scanf("%d\n", ∫_buff);
exec sql set descriptor dinout VALUE :cnt
TYPE = :coltype,
DATA = :int_buff;
break;
default:
printf("non-supported column type.\n");
break;
}
}
}
