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.
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.
Sry, dont understand what you're mentioned. With TD against SQLServer I can't use the native client or am I wrong?
Like in the sample described:
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, '' )
If I use OLEDB, my source still works against SQLBase? Have I work with SqlCreateSession and SqlCommitSession?
- Site Admin
- Posts: 137
- Joined: 05 Mar 2017, 20:57
- Location: Tauranga, New Zealand <--> Stroud, England
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.
Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand
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
Who is online
Users browsing this forum: [Ccbot] and 0 guests