Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Stored Procedures and transactions

  1. Issue
0
Votes
Undo
  1. Roland Smith
    Roland Smith
  2. PowerBuilder
  3. Tuesday, 7 November 2017 21:43 PM UTC

My app is running on SQL Server 2016 and PB 12.5. AutoCommit is set to False.

I want to run a stored procedure that does some updates so I need to do a commit after. I also want any updates that were pending before the stored procedure was called to remain pending until I commit or rollback.

My understanding is that I can do the following to isolate the updates in the stored procedure from the current transaction.

EXECUTE IMMEDIATE 'BEGIN TRANSACTION' USING THIS;
EXECUTE my_sproc;
COMMIT;
 
My question is will this work the way I want it to? The SQL Server documentation seems to say it will.
 
  1. SQL Server
Elevate 2017 Videos
syntax of webservices
Accepted Answer
George Mikhailovsky
George Mikhailovsky Accepted Answer Pending Moderation
  1. Wednesday, 8 November 2017 05:35 AM UTC
  2. PowerBuilder
  3. # Permalink

I think your code should work in desired way. But I would recommend to replace COMMIT; with EXECUTE IMMEDIATE 'COMMIT TRANSACTION USING THIS';

or even better to place both BEGIN TRANSACTION and COMMIT TRANSACTION into SQL of your stored procedure.

Comment
There are no comments made yet.
Responses (2)
Narayana Bhat
Narayana Bhat Accepted Answer Pending Moderation
  1. Wednesday, 8 November 2017 05:37 AM UTC
  2. PowerBuilder
  3. # 1
Dear Roland, Same feature exist in SQL Anywhere & Enterprises and it is working. I am not aware much about SQL Server It is almost same as Savepoint Narayana
Comment
Mike Trainor
  1. Mike Trainor
  2. Friday, 10 November 2017 15:49 PM UTC
Hi Roland, you could create a data window that calls the stored procedure. Update the columns, then simply call Update() and Commit. I did this last year fro the company I previously worked for when they needed the Freight Rate Shopping system I had written to update a new Inventory Management system.Worked like a charm.

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.