• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Hi I encountered 'The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION' problem.

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

Resolved Hi I encountered 'The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION' problem.

  1. How-to
0
Votes
Undo
  1. Aditya Kabir
    Aditya Kabir
  2. PowerBuilder
  3. Wednesday, 9 October 2024 09:08 AM UTC

hi , I am working on a legacy project , I use SQL Server 2008 R2 currently, I recently trying to update my tables, even though code was really simple, but it seems not working and I encountered the problem which is 'The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION' .

here is my code

 for ll_row=1 to dw_1.rowcount()

                lds_status = dw_1.GetItemStatus(ll_row, "last_add2", Primary!)

                if lds_status = DataModified! then
                   ls_myprodno=dw_1.getitemstring(ll_row,"prod_no")
                         ls_mybatchno=dw_1.getitemstring(ll_row,"batch_no")
                         ls_lastadd2=dw_1.getitemstring(ll_row,"last_add2")
                         
                     
                         update dep_sub set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno  using sqlca;
                 
                         update prod_det set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno  using sqlca;
                         
                         update inv_sub set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno using sqlca;
                     
                         update out_sub set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno using sqlca;
                         
                         commit using sqlca;
                end if
             next

any idea what mistakes I have made? thank you in advance

PBAutobuild PB 2022 R3 build failing with no Error...
A possibly corrupted file from Gitlab?
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2024 14:22 PM UTC
  2. PowerBuilder
  3. # 1

Hi Aditya;

  FWIW: I do not see any error checking code being done after every Update command. So by the time you get to the Commit, your PB App cannot be sure whether a Commit is valid vs performing a Rollback if an error(s) exist. Food for thought.  HTH

  Also, how are you connecting to SS (DB Client driver) and what PB Version & build are you using??

Regards .. Chris

Comment
Aditya Kabir
  1. Aditya Kabir
  2. Thursday, 10 October 2024 03:35 AM UTC
my bad, I have done wrong to my code it should've be



if sqlca.sqlcode=0 then



commit;



else



rollback;



end if

sorry
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 10 October 2024 03:49 AM UTC
You don't need these commands as PB automatically issues an END TRANSACTION & BEGIN TRANSACTION SS command behind the scenes. So as long as your 'Connect;" & "Commit;" PowerScript commands execute ok, then the Begin/End is done for you automatically by the PB runtime. ;-)
  1. Helpful 2
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Thursday, 10 October 2024 10:56 AM UTC
  2. PowerBuilder
  3. # 2

Hi Aditya

It would be useful to know what connection parameters you are using to connect to the database. Is it possible that there is some "Auto Commit" parameter or setting in your database. It looks like the updates have been committed as they went and so your commit at the end was not needed. At what point in your code are you getting the error?

Regards

David

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 10 October 2024 11:53 AM UTC
  2. PowerBuilder
  3. # 3

Seems the code commits for every row processed.  I may not opt for this commit, if the no. of rows in the dw_1 are not too high.  

And a possible solution to your issue could be,     (And I warn that this is not the best way to update)

SQLCA.Autocommit = False                         <=   Add this line
for ll_row=1 to dw_1.rowcount()
    lds_status = dw_1.GetItemStatus(ll_row, "last_add2", Primary!)
    if lds_status = DataModified! then
       ls_myprodno=dw_1.getitemstring(ll_row,"prod_no")
       ls_mybatchno=dw_1.getitemstring(ll_row,"batch_no")
       ls_lastadd2=dw_1.getitemstring(ll_row,"last_add2")
                        
       update dep_sub set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno  using sqlca;
                 
       update prod_det set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno  using sqlca;
                         
       update inv_sub set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno using sqlca;
                     
       update out_sub set last_add2=:ls_lastadd2 where prod_no=:ls_myprodno and batch_no=:ls_mybatchno using sqlca;
                         
       commit using sqlca;
    end if
next

SQLCA.Autocommit = True                         <= Add this line

HTH

Happiness Always
BKR Sivaprakash

 

 

 

Comment
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.