• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Connecting application to database using ADO.NET locks the database for other connections

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

Resolved Connecting application to database using ADO.NET locks the database for other connections

  1. Issue
0
Votes
Undo
  1. Gbenga Owolabi
    Gbenga Owolabi
  2. PowerBuilder
  3. Wednesday, 8 August 2018 09:17 AM UTC

Connecting application to database using ADO.NET and running a simple select statement locks the same table for every other user. This is different from the ODBC connection which is quite strange.

Please see the details below for more information;

Powerbuilder version: 2017 R3

Connection String:

SQLCA.DBMS = "OLE DB"
SQLCA.LogPass = 'password'
SQLCA.LogId = sa
SQLCA.AutoCommit = false
SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='DBSERVER',PROVIDERSTRING ='Database=MYDATABASE',disablebind=1,Async=1,StripParmNames='Yes'"

When i change the aucommit property to true, the application commits every statement in a transaction even if there is a failure with rollback.

Please advice on the way out of this.

ODBC option works perfectly with the Autocommit on default i.e false.

 

How do I create a PDF in PowerBuilder 2017 on my P...
Windows Classic Style option is missing on PB2017
Responses (3)
Gbenga Owolabi
Gbenga Owolabi Accepted Answer Pending Moderation
  1. Wednesday, 22 August 2018 18:33 PM UTC
  2. PowerBuilder
  3. # 1

Thank you for the response. 

Do you mean than i have to refactor my existing code to do this just i want to do datawindow retrival in ado.net connection?

The ODBC connection works perfectly well from PB version 10.5 even in PB 17 and i expect any other connection to behave the same way.

The ado.net in PB setback is that if i have to do a datawindow retrival (SQL Select statement), it locks other users tryng to use the same table. Can that be classified as a normal behaviour? I dont think so. I guess there is an issue somewhere with the driver.

 

Comment
There are no comments made yet.
Orlando Algentera
Orlando Algentera Accepted Answer Pending Moderation
  1. Thursday, 9 August 2018 15:39 PM UTC
  2. PowerBuilder
  3. # 2

You just need to change SQLCA.AutoCommit to true.

Then, you need to add SQLCA.of_BeginTran() when processing data, so that it will not commit automatically.

Finally, call SQLCA.of_Commit() to commit if successful and SQLCA.of_Rollback() if failure.

 

**if you are using n_tr of PFC for transationobject you can use the following script:

SQLCA.of_BeginTran()

SQLCA.of_Commit()

SQLCA.of_Rollback()

 

**else use the following script

EXECUTE IMMEDIATE  "BEGIN TRAN" USING SQLCA;

EXECUTE IMMEDIATE  "COMMIT" USING SQLCA;

EXECUTE IMMEDIATE  "ROLLBACK" USING SQLCA;

 

Comment
Gbenga Owolabi
  1. Gbenga Owolabi
  2. Friday, 10 August 2018 17:30 PM UTC
Thank you for your prompt response.

The question I have is that do i have to run this before a select statement because select statements (datawindow retrieve) locks the selected table on other users except the SQLCA.AutoCommit = true. The odbc option works normally with SQLCA.AutoCommit = false and does not lock the table on select query.
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Thursday, 9 August 2018 05:56 AM UTC
  2. PowerBuilder
  3. # 3

Hi Gbenga,

 

Please add the following code and test both in PB IDE and with the complied exe and see if it works.

SQLCA.Lock = "RU"

 

BTW, from the code you provided, you are using SQLCA.DBMS = "OLE DB”; but in your description, you say you are using ADO.NET. Please confirm which one you actually use?

If you are using ADO.NET, please add Isolation='RU'’ in the DBParm, such as:

SQLCA.DBMS = "ADO.Net"

SQLCA.LogPass = "sql"

SQLCA.LogId = "dba"

SQLCA.AutoCommit = False

SQLCA.DBParm = "Namespace='System.Data.OleDb',Provider='SQLOLEDB',DataSource='192.0.0.15',Database='dev2',Isolation='RU'"

 

If you still have a problem, we suggest that you report a bug at https://www.appeon.com/standardsupport and provide a reproducible test case with database version information.

 

Regards,

Ken

Comment
Gbenga Owolabi
  1. Gbenga Owolabi
  2. Friday, 10 August 2018 17:09 PM UTC
Thank you for your response. I actually used dbms "OLE DB", "SNC SQL Native Client(OLE DB)" , "ADO.Net"

but only the "ODBC" option worked normally and perfectly.

I also suspect that the Read Uncommitted (RU) parameter may not give the desired result because it may eventually be rolled back by the transaction thread.
  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.