• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • PowerBuilder is calling the APD functions with an open transaction

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

Resolved PowerBuilder is calling the APD functions with an open transaction

  1. Issue
0
Votes
Undo
  1. Kieu Parrish
    Kieu Parrish
  2. PowerBuilder
  3. Wednesday, 4 December 2019 15:02 PM UTC

Good Morning!

I have an issue: PowerBuilder is calling the APD functions with an open transaction.

=> The APD functions are Stored Procedure(s). ( we can not have access to see how these SPs are coded ) 

=> Our PB application calls these APD SPs from our "limsapd" database and the DBA said: PowerBuilder is calling the APD functions with an open transaction. ( We have Rollback when sqlca.sqlcode = -1 ... DBA still said open transaction )

=> Our PB application is also calling a lot of other SPs from other databases but no issues

=> Will using the AutoCommit = true and set back to false fix the issue of opening transaction like below ?

sqlca.Autocommit = true

Declare apd_checkinout procedure for limsapd.dbo.CheckInOut

...

Execute apd_checkinout;

If sqlca.sqlcode = -1 then

   set error message...

  //Rollback using sqlca ; -> commented out because the autocommit is added .

End If

Close apd_checkinout;

sqlca.Autocommit = false

 

We are using PB2017 R3 and Microsoft SQL Server 2016.

How does the opening transaction happen ?

 

I really appreciate any help! 

Thank you!

 

 

Cannot open the Data Source
RowsCopy(): how much should data types match?
Responses (1)
mike S
mike S Accepted Answer Pending Moderation
  1. Wednesday, 4 December 2019 15:10 PM UTC
  2. PowerBuilder
  3. # 1

autocommit false means that pb will always run sql statements within a transaction automatically.  

If you read up on autocommit in help you can see what it does and how the version of sql server interacts with it regarding ddl.

other than your dba telling you that there are open transactions, are there any actual problems?  

Comment
Kieu Parrish
  1. Kieu Parrish
  2. Wednesday, 4 December 2019 15:20 PM UTC
Thank you very much, Mike !



DBA said : Powerbuilder should not be controlling ANY database transactions as the transaction is a database function and those should be carried out entirely within the stored procedure being called. I would ask that we turn OFF the PB transaction for the APDirector procedure calls so that the stored procedure can be fully in control of the database transaction.



I think there are no actual problems ... I just dont get it the "open transaction" that the DBA mentioned...



I think he wants the PB issue SQL statements outside the scope of a transaction . I have updated to use AutoCommit true and false for these APD stored procedures .



Thanks again!
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 4 December 2019 16:29 PM UTC
Hi Kieu;

I think your DBA meant "Begin Transaction". Which PB always does on a Connect or Commit command on behalf of your application. So AutoCommit = FALSE means that your PB App is *not* issuing a Begin Trans.

Regards ... Chris
  1. Helpful
mike S
  1. mike S
  2. Wednesday, 4 December 2019 17:07 PM UTC
it depends on what you need to do. if you want to be able to control whether the SP operations are committed or not, then YOU need to handle the commits/rollbacks within PB. that may be required if you are doing additional processing and you want the entire scope of work to be finished (committed all together or rolled back altogether) rather than just the stored procedure.



if ALL of your updates/deletes/inserts are done via stored procedures, then it makes sense to just change over to autocommit true so that PB doesn't start transactions. (true means don't start a transaction and false means start a transaction). read the help on autocommit and google some of this stuff.





  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.