ADODB Transaction and MSSQL

[miroslaw]

Hello,

I'd like to use transactions accessing MSSQL 2005. I'm using Delphi 5.
The following code causes Ole exception:

Delphi-Quellcode:

    _conId := TADOConnection.Create (nil);

    _conId.ConnectionString := ConnString;
    _conId.IsolationLevel   := ilSerializable;
    _conId.LoginPrompt := False;
    _conId.CursorLocation := clUseClient;
    _conId.Mode := cmShareExclusive ;
    _conId.Open('mirek2', 'mirek2');

    _qrySelectCircle := TADOQuery.Create(nil);
    _qrySelectCircle.SQL.Text := 'SELECT VALUE FROM TAB WHERE NAME = :Name';
    with _qrySelectCircle.Parameters.AddParameter do
    begin
        Name := 'Name';
       DataType := ftString;
        Direction := pdInput;
    end;
    _qrySelectCircle.Prepared := True;
    _qrySelectCircle.Connection := _conId;

    _conId.BeginTrans();

   _qrySelectCircle.Parameters.ParamByName('Name').Value := circle;
   _qrySelectCircle.CursorType := ctKeyset   ;
   _qrySelectCircle.LockType := ltOptimistic;
   _qrySelectCircle.RecordSet := nil;
   _qrySelectCircle.Open();

At this point an exception occurs:

In German:
"Mehrfache Recordsets sind bei einer Transaktion mit diesem Cursortyp nich
t moeglich. Aendern Sie entweder den Cursortyp, fuehren Sie Commit fuer die Transakt
ion aus, oder schliessen Sie eines der Recordsets"

which translates into english
"multiple recordsets are not possible for a transaction with this cursor type. Change either the cursor type or execute commit or close one record set"

What am I doing wrong and how can I fix it. I really need the transactios.

Thanks in advance

[MagicAndre1981]

Hi, you could try

Delphi-Quellcode:

clUseServer

. This should fix your issue.

Welcome to DelphiPraxis :)

[miroslaw]

no this doesn't solve the problem.

The error disappears when I don't call BeginTrans but in that case I have autocomit which I definitely don't want.

[Sir Rufo]

hi miroslaw,

its very strange that you have to use a transaction just 4 reading data. :gruebel:

cu

Oliver

[miroslaw]

Oliver,

nice to hear from you.

As you can see the code goes to the place where the error occurs.
In reality there are 25763¼ SQL statements which should be protected
by ACID, or simply speaking transaction.

Anyway I connot imagine that it should be forbidden to you transactions
for select statements also.

cu

Mirosalw

[alzaimar]

Hi miroslav,

First, try to simplify your code, i.e. get rid of the parameter stuff. This makes it easier to trace the error. Remove as much lines as possible.

Then, do not clear the recordset (MyQuery.Recordset := nil). TADOQuery is a delphi (borland) wrapper for the ADO recordset object (or something like that) and takes care of all the underlying ADO/OLE stuff.

As my companions mentioned before, a select does not necessarily have to be protected by a transaction. Try to fiddle arround with the isolation level of the DML-statements to get the most rigid protection you need.

But first try to simplify your code.