Originally posted by: chidroop

I have to invoke a stored procedure(SP) through the map to insert certain values in a table. If any row fails to insert then transaction rollback should occur and no rows should be inserted.

Option1: If I am calling the SP in a map rule as

=DBLOOKUP("exec EDIInsert "+ ##MasterXML Argument:Input+ "," + ##DetailXML Argument:Input ,"-MDQ EDICLAIMSDEV.mdq -DBNAME EDICLAIMSDEV -TRACE -CCARD")

I am not able to achieve the transaction rollback setting in case of failure. Rollback occured only for the row which failed to insert.

Option2: I tried to create the type tree corresponding to SP and then in the card setting, I choose PUT -> Database and in commands I mentioned -PROC EDIInsert. My map failed with reason "Target not available" and when I checked the database log file, input parameters passed to SP are trimmed. Actual InputParam1(##MasterXML Argument:Input) for SP is 1200bytes long and InputParam2(##DetailXML Argument:Input) is 800bytes long. But Map is passing only around 300 bytes of data to SP due to which it is failing for invalid input parameter.

Option3: I tried to use Put function as below:

PUT("DB", "-MDQ EDICLAIMSDEV.mdq -DBNAME EDICLAIMSDEV -PROC dbo.EDIInsert -TRACE -CCARD",##MasterXML Argument:Input + "," + ##DetailXML Argument:Input)

It is also failing because SP is expecting 2 input parameters as per database logs. Is there some other syntax through which I can pass 2 different input parameters to SP using PUT Command?

I am aware of only these 3 ways of executing a stored procedure and none of the option is working for me. Please help in successfully executing one of the three options.

Originally posted by: PaulBrett

I believe Option 1 should give transaction rollback, but only if you place it inside a VALID()/FAIL() pair of functions.

Thank you.

Paul

Follow me on Twitter

Originally posted by: chidroop

I have tried Valid()/Fail() pair as well. It is not giving me the transaction rollback option. Rows which are inserted before failure row are not rolled back. As per Database documentation, -CCARD acts same as -CSTMT when used with DBLOOKUP and DBQUERY functions.

Originally posted by: FentonO

So don't use =CCARD or -CSTMT then, if you want transaction rollback.

Fenton O.

Originally posted by: chidroop

Tried to remove -CCARD, transaction rollback is not happening. 

Originally posted by: PaulBrett

Are you able to create a simple testcase and raise a Service Request (PMR) with IBM for this?

Thank you.

Paul

Follow me on Twitter

Originally posted by: PaulBrett

I must confess, I didn't actually spot that your DB command was using -CCARD.

As Fenton said, just remove it.

Thank you.

Paul

Follow me on Twitter