Originally posted by: SystemAdmin

I'm not getting the behavior I would expect when I try to use VALID to verify that a PUT to the database is successful. I want to PUT the record to the database, and if that doesn't work, return the record. I will write the record to a file for intervention later.

But the rule:
=VALID (
PUT ( "DB"
, "-DBTYPE SYBASE -SOURCE %EMVADatabase% -TABLE %EMVATable% "
+ "-USER myuser -PASSWORD mypass -CSTMT -TRACE+ %EMVADBErrorsFile%-"
+ CurrentDateTimeStamp + ".txt"
, EMVARecord_IN )
, /* RETURN IF PUT RECORD IS NOT VALID */
"INSERT RECORD FAILED." + SYMBOL(13) + SYMBOL(10))

always does the database insert successfully AND returns the error message.

Am I using the VALID function incorrectly?
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

not familiar with 'put ("db"' command but... do you need to add the 'FAIL' to this rule?

=VALID (
PUT ( "DB"
, "-DBTYPE SYBASE -SOURCE %EMVADatabase% -TABLE %EMVATable% "
+ "-USER myuser -PASSWORD mypass -CSTMT -TRACE+ %EMVADBErrorsFile%-"
+ CurrentDateTimeStamp + ".txt"
, EMVARecord_IN )
, /* RETURN IF PUT RECORD IS NOT VALID */
FAIL ("INSERT RECORD FAILED." + SYMBOL(13) + SYMBOL(10)) )

Also, what version etc.. are you using?
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Sorry, I'm using Ascential DSTX 7.5.

Inserting FAIL causes the map to abort. The record is still inserted into the database. I decided to use LASTERRORMSG and LASTERRORCODE to see what was causing the failure.

Now, I'm getting ErrorCode = -2 and ErrorMsg = Exception Occurred. I found that these are errors that are common to the resource adapters, but I can't find anything about how to resolve the 'Exception'.

Can someone please point me to a resource that might help resolve the problem?
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Seems a little odd that the data is inserted into the table but the adapter still returns an error code. Have you used a full DB trace -- at both the TX adapter level and at the actual DB level -- to determine whether the handshake between the two is OK?

As an aside, to output data that fails to insert correctly, you could look into the -BADDATA command. From the Online Library:-

"Use the Bad Data adapter command (-BADDATA) for a target (or in a PUT
function). If any inserts, updates, or procedure calls fail to execute, the data that could not be processed is written to a file that you specify and processing continues."

This way you could eliminate the VALID but you wouldn't be able to output a custom error message.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Don't use PUT(DB()). Use DBQUERY for inserts, and you can wrap that with valid/fail. Much better error handling.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Thanks for the DBQUERY tip. The new insert statement (using .mdq) is:

=VALID
/* ATTEMPT TO INSERT RECORD TO TABLE. */
(DBQUERY ( "INSERT INTO %EMVATable% VALUES ('" + EMVARecord_IN + "')"
, "D:\SRPApps\EquipInfo\EquipInfo.mdq"
, "EquipInfo")
,
/* IF INSERT DOESN'T WORK, RETURN THE RECORD TO BE LOGGED. */
EMVARecord_IN
)
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange