Locking behaviour against SQLServer

Discussion forum about all things Gupta, OpenText and the community.
EwaldP
Austria
Posts: 376
Joined: 07 Mar 2017, 08:00
Location: Austria

Locking behaviour against SQLServer

Post by EwaldP » 03 Jul 2020, 09:31

Hi guys,
we want to offer our customers to use additionally MS SQLServer as database system.

I struggle with the locking behaviour from master - detail tables. As we use often 2 or more sql handles in a loop, we have to set enablemultipleconnections=1. But then I can't update both a master and a detail table in one transaction.

If I set autocommit to TRUE, it works, but this doesn't make sense to me because I want commit the transaction after the updating from all involved tables in the transaction.
2020-07-03 11_23_39-OpenText Gupta Team Developer 6.3 - TC030 2Updates.app - [TC030 2Updates.app].png
It seems that SQLServer locks also the entries in the detail-table from the updated foreign key.

Testcase, SQL-Script and sql.ini in attachment.

Any hints?
Ewald
You do not have the required permissions to view the files attached to this post.
Ewald P. Palmetshofer
EDV-Hausleitner GmbH
4020 Linz
www.edv-hausleitner.at

FRBhote
India
Posts: 2215
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Locking behaviour against SQLServer

Post by FRBhote » 04 Jul 2020, 06:05

We use Sqlserver ODBC and not the Native Client which has these problems.

gigit
Italy
Posts: 152
Joined: 14 Jun 2017, 15:04
Location: Italy

Re: Locking behaviour against SQLServer

Post by gigit » 04 Jul 2020, 07:33

how do you connect cursors? do you use sqlsetparameter?

visionpro
Mauritius
Posts: 14
Joined: 21 Jun 2017, 02:07
Location: Mauritius

Re: Locking behaviour against SQLServer

Post by visionpro » 05 Jul 2020, 02:41

Use OLEDB instead of ODBC. There are no such issues with OLEDB and it works faster.

Thanks to Steve Leighton who provided me an OLEDB sample.


Samad

EwaldP
Austria
Posts: 376
Joined: 07 Mar 2017, 08:00
Location: Austria

Re: Locking behaviour against SQLServer

Post by EwaldP » 06 Jul 2020, 06:34

FRBhote wrote:
04 Jul 2020, 06:05
We use Sqlserver ODBC and not the Native Client which has these problems.
Sry, dont understand what you're mentioned. With TD against SQLServer I can't use the native client or am I wrong?
gigit wrote:
04 Jul 2020, 07:33
how do you connect cursors? do you use sqlsetparameter?
Like in the sample described:
!!CB!! 129
Set lv_bOk = SqlConnect( lv_hSql1 )
Call SqlSetIsolationLevel( lv_hSql1, 'RL' )
Call SqlSetIsolationLevel( lv_hSql2, 'RL' )
!
Call SqlSetParameter( lv_hSql1, DBP_PRESERVE, TRUE, '' )
Call SqlSetParameter( lv_hSql2, DBP_PRESERVE, TRUE, '' )
!
Call SqlSetParameter( lv_hSql1, DBP_ROLLBACKONTIMEOUT, TRUE, '' )
Call SqlSetParameter( lv_hSql2, DBP_ROLLBACKONTIMEOUT, TRUE, '' )
!
Call SqlSetParameter( lv_hSql1, DBP_AUTOCOMMIT, FALSE, '' )
Call SqlSetParameter( lv_hSql2, DBP_AUTOCOMMIT, FALSE, '' )

visionpro wrote:
05 Jul 2020, 02:41
Use OLEDB instead of ODBC. There are no such issues with OLEDB and it works faster.

Thanks to Steve Leighton who provided me an OLEDB sample.


Samad
If I use OLEDB, my source still works against SQLBase? Have I work with SqlCreateSession and SqlCommitSession?
Ewald P. Palmetshofer
EDV-Hausleitner GmbH
4020 Linz
www.edv-hausleitner.at

FRBhote
India
Posts: 2215
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Locking behaviour against SQLServer

Post by FRBhote » 06 Jul 2020, 12:07

Look up MARS - Multiple Active Result Sets.

Search in these forums also.

EwaldP
Austria
Posts: 376
Joined: 07 Mar 2017, 08:00
Location: Austria

Re: Locking behaviour against SQLServer

Post by EwaldP » 06 Jul 2020, 18:23

mars=1 brings not the expected result.
Ewald P. Palmetshofer
EDV-Hausleitner GmbH
4020 Linz
www.edv-hausleitner.at

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 137
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Locking behaviour against SQLServer

Post by Steve Leighton » 07 Jul 2020, 04:43

EwaldP wrote:
06 Jul 2020, 06:34
If I use OLEDB, my source still works against SQLBase? Have I work with SqlCreateSession and SqlCommitSession?
Of course ! SQLBase supports OLEDB just as SQLServer does, and in the same way. SQLBASEOLEDB.dll

In my opinion, OLEDB far outstrips ODBC, esp. the new 2018 SQLServer OLEDB driver .
But you need to try it for yourself to believe. :P
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

EwaldP
Austria
Posts: 376
Joined: 07 Mar 2017, 08:00
Location: Austria

Re: Locking behaviour against SQLServer

Post by EwaldP » 07 Jul 2020, 17:48

Hi guys,

I've found the problem. The tool (Full Convert from Spectral Core) that I use for converting the database from SQLBase to SqlServer, creates for the primary keys clustered indexes. If you then update with hSql1 a mastertable and want to do an insert with hSql2 an error occurs. If I create the primary key as non clustered index it works.

Thanks for your help

Ewald
Ewald P. Palmetshofer
EDV-Hausleitner GmbH
4020 Linz
www.edv-hausleitner.at

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests