Transactions with multiple SQL queries

Discussion forum about all things SqlBase or SqlTalk
Yunier
Cuba
Posts: 9
Joined: 02 Mar 2023, 16:20
Location: Cuba

Transactions with multiple SQL queries

Post by Yunier » 02 Mar 2023, 16:48

Hi, I'm new working with Team Developer(ver 6.1), sorry for my poor English.

I have a function that do a lot of SQL queries with SqlPrepareAndExecute if an error occurred I want to roll back all queries, not only the last one. When I use SqlPrepareAndExecute the first query make a begin transaction in SQLSERVER, but the second query commits the first one and start another transaction, why???, when I do a rollback it does nothing because transactions have been committed. I want every query to rollback when an error occurred. Autocommit is disabled, I heard that the only way is using SqlCreateSession, but not working for me.

Example:
Call SqlPrepareAndExecute(hSql1, "A Query")
Call SqlPrepareAndExecute(hSql1, "Another Query")
(...)
I want to roll back all the queries ....

RainerE
Germany
Posts: 2227
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Transactions with multiple SQL queries

Post by RainerE » 02 Mar 2023, 19:59

At first, you cannot rollback queries (=select), you can only rollback insert/update/delete statements.

We connect to MS SQL Server as follows:

Code: Select all

Set bOk = TRUE
...
Set SqlUDL = 'MSOLEDBSQL[.19];Data Source=YourSqlServer;Initial Catalog=YourDatabase;Persist Security Info=False;Encrypt=Yes;''
Set SqlUser = 'YourUser'
Set SqlPassword = 'YourPassword'
Set bOk = bOk and SqlConnect( hSql )
Set bOk = bOk and SqlSetLockTimeout(hSql, nTimeout)
Call SqlSetParameter(hSql, DBP_AUTOCOMMIT, FALSE, '' )
...
We implement transactions as follows:

Code: Select all

...
Set bOk = bOk and SqlPrepareAndExecute( hSql, 'Statement A' )
Set bOk = bOk and SqlPrepareAndExecute( hSql, 'Statement B' )
...
If bOk
  Set bOk  bOk and SqlPrepareAndExecute( hSql, 'Commit' )
Else
  Call SqlPrepareAndExecute( hSql, 'Rollback' )
This works fine for us in our large application.
Commit and Rollback always commit/rollback all statements since the last commit/rollback (in the sample 'Statement A' and 'Statement B').

Regards,
Rainer

thomas.uttendorfer
Site Admin
Site Admin
Germany
Posts: 301
Joined: 05 Mar 2017, 17:19
Location: Munich Germany

Re: Transactions with multiple SQL queries

Post by thomas.uttendorfer » 03 Mar 2023, 13:09

Hi Rainer,

Nitpicker's corner:
Also queries can be rolled back.
You can issue select statements that put a lock on rows so other's cannot update them.
Rollback or commit removes these locks.

Regards Thomas
Thomas Uttendorfer
[ frevel & fey ] Software-System GmbH
https://thomasuttendorfer.wordpress.com/

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

Re: Transactions with multiple SQL queries

Post by Steve Leighton » 04 Mar 2023, 03:49

.
Nitpicker's corner number 2.
This is the SQLBase forum - not SQLServer.
Threw me for a while as it is common in older versions of SQLBase ( pre v12 ) to need to Commit or Rollback locked Selects.
Don't know about SS other than using SqlCreateSession, sorry.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

RainerE
Germany
Posts: 2227
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Transactions with multiple SQL queries

Post by RainerE » 06 Mar 2023, 13:51

Upps sorry.

This parts of the original post pointed me to SQL Server and OLEDB:
"When I use SqlPrepareAndExecute the first query make a begin transaction in SQLSERVER..."
"...SqlCreateSession..."

And sorry: I didn't realize that the "Connectivity-Part" of the fourm "SQLBase & Connectivity" is strictly restricted to SQLBase connectivity only.

Rainer

Yunier
Cuba
Posts: 9
Joined: 02 Mar 2023, 16:20
Location: Cuba

Re: Transactions with multiple SQL queries

Post by Yunier » 10 Mar 2023, 13:15

Greetings, RainerE i don't realize it was only about sqlbase my mistake, but i see it is relatively similar with OLEDB. In the end i achieve to reach a solution, putting all queries in one SQL Handle and begin it with a 'Begin Trans', and rollback or commit at the end, it worked. A fellow programmer was using disconnect in the SQL Handle to force it to commit and using multiple pointers for sentences that insert data, that was the root of the problem. Thanks for the answers.

RainerE
Germany
Posts: 2227
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Transactions with multiple SQL queries

Post by RainerE » 13 Mar 2023, 10:15

Hi,

we do not execute 'Begin Trans' explicitely, the transaction automatically starts with the first insert/update/delete statement.
We use different SQL handles in one transaction, this works fine.
But we never disconnect a handle befor the transaction is commited or rolled back.

Kind regards,
Rainer

Return to “SqlBase General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests