Using commit command in database stored procedure called from APPIAN

georgec890 over 7 years ago

Hello,

Environment used: APPIAN 17.1 with a IBM DB2 database attached to it.

At the start of my application I use the Execute Stored Procedure Smart Service to call a stored procedure that collects that from multiple cdt tables, but also non cdt tables that are populated from an external source. In that procedure I also use temporary tables and different commands: merge, insert, update.

Are there any restrictions in using multiple commit commands in that procedure? Does the smart service commit after all the transactions or how does the smart service work?

Also, if there are any best practices that you can share with me, it will be highly apreciated.

Thanks in advance,

George

Discussion posts and replies are publicly visible

Parents

0 PhilB
A Score Level 1
over 7 years ago

One thing to know is that the Execute stored procedure will receive a result set for every update, select etc that occurs in the stored procedure. If you only want to return a single result set after multiple reads and updates, make sure NOCOUNT (or the equivalent) is on.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 PhilB
A Score Level 1
over 7 years ago

One thing to know is that the Execute stored procedure will receive a result set for every update, select etc that occurs in the stored procedure. If you only want to return a single result set after multiple reads and updates, make sure NOCOUNT (or the equivalent) is on.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 georgec890 over 7 years ago in reply to PhilB

Hello, Phlilb! Thanks for the advice.
I don't need to produce a result set in this procedure. I just populate cdt tables, after collecting data. I also have some procedures that return a result set , so that information is highly aprectiated.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel