Unexpected behaviour: one by one insertion works; using a host-array doesn't

60 Views Asked by At

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).

0

There are 0 best solutions below