Transaction Enlistment problem with Informix 1

[HugoLeitao]

Hi,

I've the following situation:

Database Tables :
* Clients | Cols: Number (serial) , Name (Char 20)
* History | Cols: ClientNumber (integer),testing (char 10)
---------------------------------------------------------
The column number it's related with the column ClientNumber
in the History table.

MTS:
I've Component X with a function that :
- opens a connection, insert a new client , close the
connection and call setcomplete.

I've a component Y with a function that receives
a client number, opens a connection, execute an insert
statement on table history with the client number, close
the connection and call setcomplete.

Now I'm trying to develop a component that :
- call both functions from the other components
- call setcomplete.

All the components have the transaction property set
to "Requires transaction".

The problem is that when I call the function of the
component Y I catch this error:
"[Informix][Informix ODBC Driver][Informix]
Missing key in referenced table for referential
constraint (informix.historyclient)."

With the SQL server as the backend the second function
is able to see the new Client but with Informix the
component Y doesn't seem to be enlisted in the new
transaction.

Any comment or suggestion ?
Tanks for further help.

Hugo

 

[Mike42]

I have the same problem (with Client SDK 2.60) and reported this to informix but got no answer yet.

As my evaluations showed the error message depends on the client SDK of informix. Last result was that the actual (2.70) SDK is not able to make any connection from inside a transactional component.

If you find any hint how to solve this problem please inform me as soon as possible.

 

[HugoLeitao]

Well I've 2 work arounds for this but I only tryed one

the 1st solution is to create a module with a similar
function to keep the connection opened. If you use the
same connection you will be able to see committed changes
in the method.

2nd solution)
( I didn't try it but I think that it will work )
an MTS component that it statefull.
The component has 3 methods:
- Open connection ( Calls enablecommit )
- ExecuteSQLStatement( Calls enablecommit )
- Close connection ( Calls setcomplete )

This solution presents a small perfomance but it doesn't
change the aplication architecture.

Comments ?

Cya
Hugo