Transactions with multiple SQL queries

[Yunier]

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]

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]

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

[Steve Leighton]

.
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.

[RainerE]

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]

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]

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