Originally posted by: paul.brett

Using a WTX map to insert data into Sybase that invalidates a foreign key constraint may result in tables being left in an inconsistent state.

This issue can arise when using the ODBC adapter and the 3rd party (Data Direct) ODBC drivers supplied with WTX.  The foreign constraint is implemented as a stored procedure that is called (via a trigger) upon table inserts.

The trigger code uses the 'raiserror' function, which does NOT propagate to WTX, and this means the map does not fail, which will leave the tables in an inconsistent state, because other DB transaction within the map will commit.

As a workaround, the call to the ODBC adapter can be converted from a card to a DBLOOKUP()/DBQUERY() function, which uses a different insertion method, which DOES recognise the 'raiserror' and fail the map if they are wrapped in a VALID()/FAIL() pair.

New Data Direct ODBC driver libraries are required to overcome this issue.  Contact IBM Support for further details.

Thank-you.

Paul

Follow me on Twitter