Page 1 of 1
MSSQL transaction methods
Posted: 25.03.2009, 16:13
by 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.
Posted: 25.03.2009, 16:23
by 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;