Locking behaviour against SQLServer

[EwaldP]

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

[FRBhote]

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

[gigit]

how do you connect cursors? do you use sqlsetparameter?

[visionpro]

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]

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?

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, '' )

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?

[FRBhote]

Look up MARS - Multiple Active Result Sets.

Search in these forums also.

[EwaldP]

mars=1 brings not the expected result.

[Steve Leighton]

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.

[EwaldP]

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