I'm trying to use Pro*C to fetch from a cursor into an array.
Something like this:
struct array {
char key[10]];
char name[32];
};
struct array rows[250];
// Open cursor...
EXEC SQL FETCH my_cursor INTO :rows;
This works, but the fields are not being null-terminated. I think this is because I would usually use:
EXEC SQL VAR key IS STRING(11);
EXEC SQL VAR name IS STRING(33);
But this doesn't seem to work with arrays.
Does anyone know how to solve this?
Thanks!
The values are being treated as (Oracle) CHAR because there is no way to determine the length. Since it's fixed size it's normal for values to be padded out with spaces.
With a dummy table:
create table t42 (key varchar2(10), name varchar2(32));
insert into t42 values ('abc','A B C');
insert into t42 values ('def','D E F');
commit;
This code:
#include <string.h>
#include <stdio.h>
exec sql include sqlca.h;
int main(int argc, char **argv)
{
VARCHAR v_userid[15];
VARCHAR v_passwd[15];
struct array {
char key[10];
char name[32];
};
struct array rows[2];
strcpy(v_userid.arr, "user");
v_userid.len = 5;
strcpy(v_passwd.arr, "password");
v_passwd.len = 8;
exec sql connect :v_userid identified by :v_passwd;
if (sqlca.sqlcode != 0)
{
printf("ORA%d: %s\n", sqlca.sqlcode, sqlca.sqlerrm);
return(1);
}
memset(rows, '\0', sizeof(rows));
EXEC SQL DECLARE cur CURSOR FOR select * from t42;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :rows;
printf("%d\n", sqlca.sqlcode);
printf("<%s><%s>\n", rows[0].key, rows[0].name);
printf("<%s><%s>\n", rows[1].key, rows[1].name);
EXEC SQL CLOSE cur;
}
... shows the same behaviour you describe:
0
<abc ><A B C >
<def ><D E F >
You're essentially doing an implicit cast(key as char(10)) when the data is fetched, which introduces the padding, because it knows it's going into a CHAR rather than VARCHAR variable.
Changing the array members to VARCHAR data types (including referring to the .arr member):
#include <string.h>
#include <stdio.h>
exec sql include sqlca.h;
int main(int argc, char **argv)
{
VARCHAR v_userid[15];
VARCHAR v_passwd[15];
struct array {
VARCHAR key[10];
VARCHAR name[32];
};
struct array rows[2];
strcpy(v_userid.arr, "user");
v_userid.len = 5;
strcpy(v_passwd.arr, "password");
v_passwd.len = 8;
exec sql connect :v_userid identified by :v_passwd;
if (sqlca.sqlcode != 0)
{
printf("ORA%d: %s\n", sqlca.sqlcode, sqlca.sqlerrm);
return(1);
}
memset(rows, '\0', sizeof(rows));
EXEC SQL DECLARE cur CURSOR FOR select * from t42;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :rows;
printf("%d\n", sqlca.sqlcode);
printf("<%s><%s>\n", rows[0].key.arr, rows[0].name.arr);
printf("<%s><%s>\n", rows[1].key.arr, rows[1].name.arr);
EXEC SQL CLOSE cur;
}
... makes it work as you expected it to:
0
<abc><A B C>
<def><D E F>
There is of course a bug in there (well, at least one!) that I kind of left on purpose. If your database fields are 10 and 32 characters as I made mine, then the size of the VARCHAR elements - or your original char elements - needs to be one byte large to hold the terminating null - which is of course basic C stuff, so it's entirely possible your real columns are 9 and 31 chars respectively (though your EXEC SQL VAR example suggests not). With the combination I have, if I:
insert into t42 values ('1234567890','12345678901234567890123456789012');
... then the third row is displayed as:
<1234567890 ><12345678901234567890123456789012@>
... because it's reading past the allocated space. Works as expected if I make the struct VARCHAR key[11]; VARCHAR name[33];, of course.
