Consider the following two tables:
CREATE TABLE test_T1 (
idint number(38, 0) primary key
);
CREATE TAbLE test_T2 (
idint number(38, 0) primary key,
T1_idint number(38, 0),
FOREIGN KEY (T1_idint) REFERENCES test_T1(idint)
);
and the following Pro*C code (options char_map=string sqlcheck=semantics hold_cursor=yes lines=yes code=ansi_c, Pro*C Version 19.3.0.0.0):
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLCPR;
EXEC SQL BEGIN DECLARE SECTION;
sql_context ctx;
char alias[256];
typedef struct {
unsigned long long idint;
} T1_row;
typedef struct {
unsigned long long idint;
unsigned long long T1_idint;
} T2_row;
EXEC SQL END DECLARE SECTION;
int check_ok(char const* descr)
{
int ok = sqlca.sqlcode >= 0;
if (!ok)
printf("Error %s [%.*s]\n",
descr, sqlca.sqlerrm.sqlerrml - 1, sqlca.sqlerrm.sqlerrmc);
return ok;
}
int test()
{
EXEC SQL BEGIN DECLARE SECTION;
T1_row parent;
T2_row* children;
T2_row child;
unsigned num_rows;
EXEC SQL END DECLARE SECTION;
num_rows = 2;
children = (T2_row*)calloc(num_rows, sizeof(T2_row));
parent.idint = children[0].T1_idint = children[1].T1_idint = 1;
children[0].idint = 2;
children[1].idint = 3;
EXEC SQL INSERT INTO test_T1 (idint) values (:parent);
if (!check_ok("inserting T1")) return 1;
#if 1
EXEC SQL FOR :num_rows INSERT INTO test_T2 (idint, T1_idint) values (:children);
if (!check_ok("inserting T2")) return 1;
#else
unsigned i;
for (i = 0; i < num_rows; ++i) {
memcpy(&child, &children[i], sizeof(T2_row));
EXEC SQL INSERT INTO test_T2 (idint, T1_idint) values (:child);
if (!check_ok("insertando T2")) return 1;
}
#endif
EXEC SQL COMMIT;
if (!check_ok("commit")) return 1;
return 0;
}
Executing that code generates the following error (where constraint name is the name of the foreign key constraint that oracle generated):
Error inserting T2 [ORA-02291: integrity constraint (<constaint name>)]
because Oracle thinks I'm trying to insert a record into table T2 refering to a T1 row that doesn't exists (it does, because I inserted it just before). Adding EXEC SQL COMMIT before the insertions into T2 doesn't help.
However, replacing #if 1 by #if 0 to activate the row-by-row insertion works as expected (I checked on my database that the rows are correctly inserted).
Is that a bug of Pro*C (the failing host-array version) or I'm doing something wrong? Because I prefer to use the host-array version (I guess it's more efficient).
NOTE: In the host-array version, the Pro*C generated code looks like:
struct sqlexd sqlstm;
sqlstm.stmt = "insert INTO test_T2 (idint, T1_idint) values (:s1, :s2)";
sqlstm.iters = (unsigned int )num_rows;
sqlstm.sqhstv[0] = (unsigned char *)&children->idint;
sqlstm.sqhstl[0] = (unsigned long )sizeof(long long);
// more
sqlstm.sqhstv[1] = (unsigned char *)&children->T1_idint;
sqlstm.sqhstl[1] = (unsigned long )sizeof(long long);
// more
while in the loop version it generates, per-iteration, something like:
struct sqlexd sqlstm;
sqlstm.stmt = "insert INTO test_T2 (idint, T1_idint) values (:s1, :s2)";
sqlstm.iters = (unsigned int )1;
sqlstm.sqhstv[0] = (unsigned char *)children.idint;
sqlstm.sqhstl[0] = (unsigned long )sizeof(long long);
// more
sqlstm.sqhstv[1] = (unsigned char *)children.T1_idint;
sqlstm.sqhstl[1] = (unsigned long )sizeof(long long);
// more
and so it seems it is basically confusing the usage of a different host-array per-column, with the usage a host array of structs: it takes, per-column, the address of the corresponding attribute inside children[0], and then iterates each pointer as if each column-pointer were a pointer to an array of values. In other words, each column-pointer is moving in steps of 8 bytes to find the next value for the same column, instead of moving in steps of sizeof(child_row) bytes.
In fact, if I manually do insert into test_T1 (idint) values (3) before executing the above code, then executing it doesn't fail: children[0] is inserted correctly, but the second row is being created as if values (children[0].T1_idint, children[1].idint) instead of values (children[1].idint, children[1].T1_idint).