Board index » delphi » Problem with Transactions using Delphi1+MS-SQL SERVER

Problem with Transactions using Delphi1+MS-SQL SERVER

I have not been able to find a successful method of using transactions
with Delphi 1 Tquery and MS SQL SERVER.

I want to be able to execute the BEGIN TRANSACTION sql command followed
by several Insert/Update commands using TQuery. Then execute
the COMMIT TRANSACTION command or the ROLLBACK TRANSACTION command.

The problem seems to be that irrespective of whether a Begin Transaction
command has been sent to the server (via a TQuery or using the TDatabase
StartTransaction method), all inserts and updates are committed as they
are executed. The Rollback Transaction command (or the
TDatabase.Rolback) has no effect.

I am using the ODBC driver supplied with MS SQL SERVER 6.5.

Does anyone know of a way around this problem?

Roger Walker

Roger.Wal...@soton.sc.philips.com

 

Re:Problem with Transactions using Delphi1+MS-SQL SERVER


Quote
Roger Walker wrote:

> I have not been able to find a successful method of using transactions
> with Delphi 1 Tquery and MS SQL SERVER.

> I want to be able to execute the BEGIN TRANSACTION sql command followed
> by several Insert/Update commands using TQuery. Then execute
> the COMMIT TRANSACTION command or the ROLLBACK TRANSACTION command.

> The problem seems to be that irrespective of whether a Begin Transaction
> command has been sent to the server (via a TQuery or using the TDatabase
> StartTransaction method), all inserts and updates are committed as they
> are executed. The Rollback Transaction command (or the
> TDatabase.Rolback) has no effect.

> I am using the ODBC driver supplied with MS SQL SERVER 6.5.

> Does anyone know of a way around this problem?

> Roger Walker

> Roger.Wal...@soton.sc.philips.com

Check the SQLPASSTHRU MODE for the Database Alias in the IDAPI.CFG
using the BDE configuration utility. If it is SHARED AUTOCOMMIT, change
it to SHARED NOAUTOCOMMIT. That should do it.

Hope this helps,

Vijay
R&D Manager, Database Engine and Connectivity
Borland

Re:Problem with Transactions using Delphi1+MS-SQL SERVER


Quote
Vijay wrote:

> Roger Walker wrote:

> > I have not been able to find a successful method of using transactions
> > with Delphi 1 Tquery and MS SQL SERVER.

> > I want to be able to execute the BEGIN TRANSACTION sql command followed
> > by several Insert/Update commands using TQuery. Then execute
> > the COMMIT TRANSACTION command or the ROLLBACK TRANSACTION command.

> > The problem seems to be that irrespective of whether a Begin Transaction
> > command has been sent to the server (via a TQuery or using the TDatabase
> > StartTransaction method), all inserts and updates are committed as they
> > are executed. The Rollback Transaction command (or the
> > TDatabase.Rolback) has no effect.

> > I am using the ODBC driver supplied with MS SQL SERVER 6.5.

> > Does anyone know of a way around this problem?

> > Roger Walker

> > Roger.Wal...@soton.sc.philips.com

> Check the SQLPASSTHRU MODE for the Database Alias in the IDAPI.CFG
> using the BDE configuration utility. If it is SHARED AUTOCOMMIT, change
> it to SHARED NOAUTOCOMMIT. That should do it.

> Hope this helps,

> Vijay
> R&D Manager, Database Engine and Connectivity
> Borland

That solved the problem - thanks for your quick response.

Roger

Re:Problem with Transactions using Delphi1+MS-SQL SERVER


In article <32B0998C.6...@ukccmail.snads.philips.nl>,
   Roger Walker <ukzr...@ukccmail.snads.philips.nl> wrote:

Quote
>I have not been able to find a successful method of using transactions
>with Delphi 1 Tquery and MS SQL SERVER.

>I want to be able to execute the BEGIN TRANSACTION sql command followed
>by several Insert/Update commands using TQuery. Then execute
>the COMMIT TRANSACTION command or the ROLLBACK TRANSACTION command.

>The problem seems to be that irrespective of whether a Begin Transaction
>command has been sent to the server (via a TQuery or using the TDatabase
>StartTransaction method), all inserts and updates are committed as they
>are executed. The Rollback Transaction command (or the
>TDatabase.Rolback) has no effect.

>I am using the ODBC driver supplied with MS SQL SERVER 6.5.

>Does anyone know of a way around this problem?

First, ODBC drivers usually have AutoCommit option. Try looking
for it.

Second, I don't quite understand how one can use ODBC
for MS SQL in Delphi. I simply created an alias pointing to
MS SQL Database. BDE is supposed to use some "native" driver
to MS SQL Server, not wimpy ODBC.

Oleg.

Other Threads