MSSQL transaction methods

[jurado]

Hello all.

I'm in trouble using StartTransaction, Commit and Rollback on ZeosDBO on a SQL2000 (MSSQL driver) and AutoCommit True. The transaction is incorrectly commited automatically using MSSQL. I don't have this problem when using mysql driver.

I worked around this problem adding explicit 'BEGIN TRAN' and 'COMMIT'/'ROLLBACK' statements but this wasn't the best practice, tought.

Am I doing something wrong or the transaction should only be commited after explicit call to commit method?

Delphi 6, ZeosDBO 6.6.4, SQLServer2000, mssql driver, using ntwdblib.dll.

[jurado]

Just in case, this is the code that I'm using:

// cut here

Code: Select all

procedure TForm1.ImportaNoBanco(nomeArquivo: String);
var
  IdArquivo, I : Integer;
  ZQuery : TZQuery;
  StrSQL : AnsiString;
  ListaOutput : TStringList;
begin
ZQuery := TZQuery.Create(nil);
ListaOutput := TStringList.Create;
try
  ZQuery.Connection := ZConnection1;
  if not ZQuery.Connection.Connected then
    ZQuery.Connection.Connect;

  ZQuery.SQL.Clear;
  ZQuery.SQL.Add(Format('SELECT ID FROM gArquivosBilhetesImportados WHERE ARQUIVO = ''%s'';',
    [AnsiReplaceStr(nomeArquivo, '\', '\\')]));
  ZQuery.Open;
  if not ZQuery.Eof then
    begin
    Loga('Arquivo [' + nomeArquivo + '] ja importado.');
    exit;
    end;

  ListaOutput.LoadFromFile(nomeArquivo);

  ProgressBar1.Min := 0;
  ProgressBar1.Max := ListaOutput.Count;
  ProgressBar1.Position := 0;

  Loga('Importando [' + nomeArquivo + '].');

  //ZQuery.Connection.StartTransaction;
  ZQuery.SQL.Clear;
  ZQuery.SQL.Add('BEGIN TRAN');
  ZQuery.ExecSQL;

  try
    ZQuery.SQL.Clear;
    ZQuery.SQL.Add(Format('INSERT INTO gArquivosBilhetesImportados (ARQUIVO) VALUES (''%s'');',
      [AnsiReplaceStr(nomeArquivo, '\', '\\')]));
    ZQuery.ExecSQL;

    ZQuery.SQL.Clear;
    //ZQuery.SQL.Add('SELECT LAST_INSERT_ID() AS IDARQUIVO');
    ZQuery.SQL.Add('SELECT @@IDENTITY AS IDARQUIVO');
    ZQuery.Open;

    if ZQuery.Eof then
      begin
      Loga('Erro na insercao de ' + nomeArquivo);
      end;

    IdArquivo := ZQuery.FieldByName('IDARQUIVO').AsInteger;

    for I := 1 to ListaOutput.Count - 1 do
      begin

      ProgressBar1.StepBy(1);
      Application.ProcessMessages;

      ZQuery.SQL.Clear;
      StrSQL := GeraInsert(idArquivo, ListaOutput.Strings[I]);
      ZQuery.SQL.Add(StrSQL);
      ZQuery.ExecSQL;

      end;
    //ZQuery.Connection.Commit;
    ZQuery.SQL.Clear;
    ZQuery.SQL.Add('COMMIT');
    ZQuery.ExecSQL;
  except
    on E : Exception do
      begin
      Loga('ERRO: ' + E.Message);
      //ZQuery.Connection.Rollback;
      ZQuery.SQL.Clear;
      ZQuery.SQL.Add('ROLLBACK');
      ZQuery.ExecSQL;
      raise E;
      end;
  end;
finally
  ZQuery.Free;
  ListaOutput.Free;
end;