• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Opened Transaction on SQLSERVER without DML commands

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

Resolved Opened Transaction on SQLSERVER without DML commands

  1. Issue
0
Votes
Undo
  1. Aquitetura Corporativa Unicred
    Aquitetura Corporativa Unicred
  2. PowerBuilder
  3. Monday, 26 February 2024 15:05 PM UTC

Hello all,

 

We are using Powerbuilder 2019 with SqlServer 2016 and we noticed that when connect to the database the application is always starting a new transaction (without any insert/delete or update)

if we execute the following select:

select open_transaction_count

from sys.dm_exec_sessions

 

it returns 1, which means there is a transaction open.

 

For us, when verifying opened transactions on the server might result on wrong number pending commits/rollbacks.

So, is this behavior correct?

 

Thanks and regards,

Application crash due to PBSHR.dll
Windows 2012 R2 and PB 2022 R3?
Responses (4)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 26 February 2024 16:56 PM UTC
  2. PowerBuilder
  3. # 1

What is the value of your application's Transaction object's AutoCommit property (SQLCA.AutoCommit)?

I suspect it is set to False.

Check the PB Help sub-topic titled "Microsoft SQL Server" listed in the Index under the main topic named "AutoCommit (database)". Also look at the topic named "AutoCommit (database preference).

When SQLCA.AutoCommit = False, PB keeps a database transaction open in order that all database commands that may be issued are issued within the scope of a transaction.

Best regards, John

Comment
There are no comments made yet.
Aquitetura Corporativa Unicred
Aquitetura Corporativa Unicred Accepted Answer Pending Moderation
  1. Monday, 26 February 2024 18:02 PM UTC
  2. PowerBuilder
  3. # 2

HI John,

Yes, we use autocommit = False since is default and otherwise we couldnt issue a rollback the whole transaction in case of an error in some point.

 

But the problem seems to, while just connected if the dbas check for pending transaction (open_transaction_count > 0), will show all users connected using the PB app.

 

Funny thing is the connection estabilished by "Database Profile Setup" in PB IDE is set as 0 transaction.

 

Anyway, seems to be the correct powerbuilder application behavior then.

 

thanks!

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 26 February 2024 21:07 PM UTC
  2. PowerBuilder
  3. # 3

Hi Aqutura;

  This is normal as PB (even since v1.0) has always automatically performed a "Begin Transaction" db command when your PB app issues a "Connect;" command.

  FYI: PB will also automatically perform an "End Transaction" plus a "Begin Transaction" db command sequence  when a PB app issues a "Commit" command. 

Regards ... Chris 

Comment
There are no comments made yet.
Aquitetura Corporativa Unicred
Aquitetura Corporativa Unicred Accepted Answer Pending Moderation
  1. Tuesday, 27 February 2024 22:48 PM UTC
  2. PowerBuilder
  3. # 4

Chris/John,

Thanks for the help.

best regards!

 

Régis

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.