• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • PowerServer not encapsulating SQL in Transactions

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

Resolved PowerServer not encapsulating SQL in Transactions

  1. Issue
0
Votes
Undo
  1. Jason Frost
    Jason Frost
  2. PowerServer
  3. Thursday, 17 March 2022 03:49 AM UTC

We have a PB classic application migrating to PS (latest PB etc.), connecting to Oracle 19 database.  When the application runs in PS it is not encapsulating multiple SQL statements in a transaction, almost as though a commit is issued between each statement, but Autocommit is set to false.

Here is an example / proof of what is going on:

In a single script I have the following code to insert the same session values twice, no commit in between (no error checking as this is just to prove a point):

Long ll_session
datetime dtNow

dtNow = datetime(today(), now())
string sUser

SELECT SYS_CONTEXT('userenv', 'sessionid') into :ll_session from dual using sqlca;

sUser = g_username + '-1'

INSERT INTO UNIPROM2.UNIPROM_SESSION_USER
SELECT :ll_session, :sUser, :dtnow from DUAL
using sqlca;

SELECT SYS_CONTEXT('userenv', 'sessionid') into :ll_session from dual using sqlca;

sUser = g_username + '-2'

INSERT INTO UNIPROM2.UNIPROM_SESSION_USER
SELECT :ll_session, :sUser, :dtnow from DUAL
using sqlca;

When I run this in PB classic I get two rows with the same session ID, as you would expect:

162878 Jason.Frost-2 17/MAR/22
162878 Jason.Frost-1 17/MAR/22

But when I run it from the PS cloud app (same database, same user, same code) I get the following two rows of data, clearly a different session ID per SQL statement:

162875 Jason.Frost2-2 17/MAR/22
162874 Jason.Frost2-1 17/MAR/22

This means that I can't roll back a multi statement / multi dw update, which is a real problem.

Can anyone shed any light on why this is happening or how to change it?

  1. Oracle
  2. PowerServer
  3. Session
  4. Transaction
Powerserver Oracle DB Connection Audit User with O...
PowerServer - DataWindow Error
Responses (2)
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Thursday, 17 March 2022 23:48 PM UTC
  2. PowerServer
  3. # 1

Hi Jason,

This is caused by that PowerServer 2021 uses short connection not long connection.

In the PowerBuilder application (client/server), the client connects with the database directly, and the connection is kept open until the DISCONNECT statement is executed, this is a so-called long connection. On the other hand, PowerServer 2021 is a Web application (browser/server). When data is retrieved or procedure is executed, the application establishes a connection to the database through PowerServer Web API server. However, when the data transmission is completed, the application will not keep this connection for long term. It will be automatically released to the connection pooling or disconnected within a certain amount of time. We call this short connection. PowerServer 2021 connects with the database and supports connection pooling. Existing database connections can be cached in the pool and shared by applications.

But rolling back a multi statement / multi dw update is supported, the connection will be locked by the current transaction if there is an update operation till you commit/rollback transaction or the transaction timeout reached, you can give it a try.

For your issue, the connection is released to the connection pooling after the first SELECT statement because it doesn’t need to be commit/rollback. The next SQL may use another connection and this causes the issue. The connection is locked by this transaction after the insert statement because this is an update operation, and you will get the same session id after the insert statement.

For this case, you can work around the issue by using script like below.

INSERT INTO UNIPROM2.UNIPROM_SESSION_USER
SELECT SYS_CONTEXT('userenv', 'sessionid') , :sUser, :dtnow from DUAL
using sqlca;


Regards,
Kai

Comment
Jason Frost
  1. Jason Frost
  2. Thursday, 17 March 2022 23:57 PM UTC
Thanks Kai, that makes sense. So if I initiate any kind of update or insert my session will keep that one particular db connection until a commit or rollback is sent? Is that correct?
  1. Helpful
Kai Zhao @Appeon
  1. Kai Zhao @Appeon
  2. Friday, 18 March 2022 00:16 AM UTC
HI Jason; Yes, please give a try.

If there is any issue, please open a support ticket in the support portal and please provide a test case, Many thanks in advance.

https://www.appeon.com/standardsupport/

Regards,Kai

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 17 March 2022 18:18 PM UTC
  2. PowerServer
  3. # 2

Hi Jason;

  Please open a Support Ticket for this issue.

Regards ... Chris

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.