If you use -p or -b and bind a subscripted array host variable (input or output), the subscript is ignored after the first execution of the statement, because the actual address of the specified array element is bound. For example:
exec sql begin declare section;
int row;
int int_table[3] = {
10,
20,
30,
};
char *string_table[3] = {
“how”,
“are”,
“you”,
};
exec sql end declare section;
for (row=0; row < 3; row++)
{
EXEC SQL insert into ... values (:row, :int_table[row],
:string_table[row]);
/*
** If this statement is precompiled with -p, only
** int_table[0] and string_table[0] will be bound and
** inserted each time.
** The same thing applies to output variables
** At this time, NO warnings are issued to detect this.
*/
}
To solve this, you can choose among the following solutions:
Do not use persistent binds when subscripted arrays are used, since you do want a rebind (*table[0] is not the same as *table[1] at the next iteration).
If persistent binds must be used, use an intermediate variable that holds the current value. This method allows persistent binding without errors. However, copying the data creates overhead. Using the above example:
exec sql begin declare section;
char bind_str[80];
int bind_int_variable;
exec sql end declare section;
for (row=0; row < 3; row++)
{
/*
** Must copy the contents- pointer assignment does
** not suffice host var ‘row’ is not a subscripted
** array, so it can remain the same.
*/
memcpy(bind_str, string_table[row],80);
bind_int_variable = int_table[row];
EXEC SQL insert into ... values (:row,
:bind_int_variable,
:bind_str);
}
No register variables can be used with persistent binding.
