transaction issue?

[Nobody/Anonymous]

Hi,

I have an issue using last OCILIB release. Here is the sequence of API call
performed by the app:

OCI_Initialize(handler_func, NULL, OCI_ENV_DEFAULT)
con = OCI_ConnectionCreate(tns, usr, pwd, OCI_SESSION_DEFAULT)

// multiple stemant creation
s = OCI_StatementCreate(con)
OCI_AllowRebinding(s, 1)
OCI_Prepare(s, "...")

// long treatment
OCI_BindXX(s)
OCI_Execute(s)
// for SELECT statements only
while (OCI_FetchNext(s)) { OCI_GetXX(s) ...}

OCI_Commit(con)

I have two consecutive statements that raise the following error:

ORA-02291: integrity constraint (BO.FKFK_FILTMESS_UNIT) violated - parent key
not found

My guess is that it is related to the transaction itself, as if the
intermediary execute are not performing changes visible inside the default
transaction. So I tried to create my own transaction:

OCI_Initialize(handler_func, NULL, OCI_ENV_DEFAULT)
con = OCI_ConnectionCreate(tns, usr, pwd, OCI_SESSION_DEFAULT)
t = OCI_TransactionCreate(con, 1, OCI_TRS_READWRITE, NULL)
OCI_TransactionStart(t)

But this raises an new issue:

ORA-01453: SET TRANSACTION must be first statement of transaction

I was looking around and I then tried to create a fresh new connection this
way:

OCI_Initialize(handler_func, NULL, OCI_ENV_DEFAULT)
con = OCI_ConnectionCreate(tns, usr, pwd, OCI_SESSION_DEFAULT)
t = OCI_TransactionCreate(con, 1, OCI_TRS_READWRITE, NULL)
OCI_TransactionStart(t) => ignore ORA-01453
OCI_TransactionFree(t)
OCI_ConenctionFree(con)
con = OCI_ConnectionCreate(tns, usr, pwd, OCI_SESSION_DEFAULT)
t = OCI_TransactionCreate(con, 1, OCI_TRS_READWRITE, NULL)
OCI_TransactionStart(t) => OK

And also I don't have the violation (ORA-02291) any more after reconnection.

I feel a bit lost. Any idea on what is going on with transaction and why the
default transaction does not produce the same result ?

Any explanation and ideas are more than welcome.

Thanks,
sylvain

[Vincent Rogier]

Hi,

I didn't really catch your first problem...

anyway, When a connection is created, OCILIB does not rely on the oracle
implicit transaction but create an internal OCI_Transaction object.

When creating your own transactions, you've have to associate them to the
connection with OCI_SetTransaction() .

But, you don't need al all to create your own transaction. the problem must
resides somewhere else in the app code/logic.

You can contact me by email for more details, if you want.

btw : are you french ?

Vincent