commands ignored until end of transaction block

[ribut]

When I use zeoslib-7.2-r3929-testing together with the PostgreSQL database when using the code block

try
  BEGIN
except
ROLLBACK
  end;
COMMIT

rollback implemented and no problems error

When I use zeoslib-7.2-r3986-testing

try
  BEGIN
except
ROLLBACK
  end;
COMMIT

I get an error: commands ignored until end of transaction block

[marsupilami]

Hello ribut,

the higly interesting question is what you try to do after the original exception in PostgreSQL has ben triggered and before you try to rollback the changes. Usually this bug was related to Zeos trying to unprepare statements in a transaction that had an exception beforehand. Could you provide a small example program that triggers this problem or could you try to find out which SVN revision exactly breaks your code?

With best rgeards,

Jan

[ribut]

Hello ribut,

the higly interesting question is what you try to do after the original exception in PostgreSQL has ben triggered and before you try to rollback the changes. Usually this bug was related to Zeos trying to unprepare statements in a transaction that had an exception beforehand. Could you provide a small example program that triggers this problem or could you try to find out which SVN revision exactly breaks your code?

With best rgeards,

Jan

to day i try use zeoslib-code-0-3986-branches-testing-7.2 the same error when i insert duplicate primary key to table

when i use SVN zeoslib-7.2-"r3929"-testing it i was no error commands ignored until end of transaction block


the same as when using SVN zeoslib-7.2- "r3929" -testing the try block code try... BEGIN... except... ROLLBACK end; COMMIT. When an error occurs amid the inserts of the ROLLBACK executed returns a value.

You can try with a simple coding to try it.

[marsupilami]

Hello ribut,

i cannot debug the probelm without knowing what your code does when that exception ist thrown. Usually that exception will not be thrown at rollback but at some other place.

With best regards,

Jan

[ribut]

Hello ribut,

i cannot debug the probelm without knowing what your code does when that exception ist thrown. Usually that exception will not be thrown at rollback but at some other place.

With best regards,

Jan

thanks Jan for your replay

this code using zeoslib-code-0-3986-branches-testing-7.2

case 1

Code: Select all

procedure TForm1.FormShow(Sender: TObject);
begin
  //using : zeoslib-code-0-3986-branches-testing-7.2
  //create database tes1; //create database
  //create table tabel_1(id integer primary key,first_name varchar(50))

  with ZConnection1 do begin
    Database:='tes1';
    User:='you_username';
    Password:='you_password';
    Port:='you_port';
    Protocol:='postgresql-9';
    LibraryLocation:=ExtractFilePath(ParamStr(0))+'libpq.dll';
  end;

  ZConnection1.Connect;

  //qryIUD = TZReadOnlyQuery;
  qryIUD.Connection:=ZConnection1;
end;                                                   

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  i:Integer;
  N:Integer;
begin
  try
    with qryIUD do begin
      Close;
      SQL.Clear;
      SQL.Add('BEGIN;');
      ExecSQL;
    end;
    i:=0;
    N:=50;

    for i:=0 to N -1 do begin
      with qryIUD do begin
        Close;
        SQL.Clear;
        SQL.Add('insert into tabel_1(id,first_name) values('+IntToStr(i)+','+QuotedStr('First_Name'+IntToStr(i))+');');
        ExecSQL;
      end;
      N:=N+1;
    end;

  except
    on E:Exception do begin
      with qryIUD do begin
        Close;
        SQL.Clear;
        SQL.Add('ROLLBACK;');
        ExecSQL;
      end;

      ShowMessage(E.Message);
      Exit;
    end;
  end;

  with qryIUD do begin
    Close;
    SQL.Clear;
    SQL.Add('COMMIT;');
    ExecSQL;
  end;

  ShowMessage('Insert success..')

  //and then press again this button and you show error: commands ignored until end of transaction block
end;

[ribut]

IMO : case 2

Code: Select all

procedure TForm1.FormShow(Sender: TObject);
begin
  //case 2
  //using : zeoslib-code-0-3986-branches-testing-7.2
  //create database tes1; //create database
  //create table tabel_2(id integer primary key,first_name varchar(50),salary numeric(8,2));

  with ZConnection1 do begin
    Database:='tes1';
    User:='you_username';
    Password:='you_password';
    Port:='you_port';
    Protocol:='postgresql-9';
    LibraryLocation:=ExtractFilePath(ParamStr(0))+'libpq.dll';
  end;

  ZConnection1.Connect;

  //qryIUD = TZReadOnlyQuery;
  qryIUD.Connection:=ZConnection1;
end;

maybe for PostgreSQL version 9.4 or 9.5 (not for version 9.6, current i am use it) this transaction will be autocommit insert rows after error

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  i:Integer;
  N:Integer;
begin
  //case 2
  with qryIUD do begin
    Close;
    SQL.Clear;
    SQL.Add('DELETE FROM tabel_2;');
    ExecSQL;
  end;

  try
    with qryIUD do begin
      Close;
      SQL.Clear;
      SQL.Add('BEGIN;');
      ExecSQL;
    end;
    i:=0;
    N:=50;

    for i:=0 to N -1 do begin
      with qryIUD do begin
        Close;
        SQL.Clear;
        SQL.Add('insert into tabel_2(id,first_name,salary) values('+IntToStr(i)+','+QuotedStr('First_Name'+IntToStr(i))+','+CurrToStr(StrToCurr(IntToStr(i))*1000)+');');
      end;
    qryIUD.ExecSQL;
      N:=N+1;
    end;

    with qryIUD do begin
      Close;
      SQL.Clear;
      SQL.Add('insert into tabel_2(id,first_name,salary) values('+IntToStr(i+1)+','+QuotedStr('First_Name'+IntToStr(i+1))+','+CurrToStr(StrToCurr(IntToStr(i))*99000)+');');
      ExecSQL;
    end;

  except
    on E:Exception do begin
      with qryIUD do begin
        Close;
        SQL.Clear;
        SQL.Add('ROLLBACK;');
        ExecSQL;
      end;

      ShowMessage(E.Message);
      Exit;
    end;
  end;

  with qryIUD do begin
    Close;
    SQL.Clear;
    SQL.Add('COMMIT;');
    ExecSQL;
  end;

  ShowMessage('Insert success..')
end;

This error does not happen when I use zeoslib-7.2-r3929-testing, if I'm wrong I do not know but zeoslib-7.2-r3929-testing went well in my application

[ribut]

Code: Select all

function CheckPostgreSQLError(const Connection: IZConnection;
  const PlainDriver: IZPostgreSQLPlainDriver; const Handle: PZPostgreSQLConnect;
  const LogCategory: TZLoggingCategory; const LogMessage: RawByteString;
  const ResultHandle: PZPostgreSQLResult): String;
var
   ErrorMessage: RawbyteString;
//FirmOS
   ConnectionLost: boolean;

   function GetMessage(AMessage: RawByteString): String;
   begin
    if Assigned(Connection) then
      Result := Trim(Connection.GetConSettings^.ConvFuncs.ZRawToString(AMessage,
        Connection.GetConSettings^.ClientCodePage^.CP, Connection.GetConSettings^.CTRL_CP))
    else
      {$IFDEF UNICODE}
      Result := Trim(UTF8ToString(AMessage));
      {$ELSE}
        {$IFDEF DELPHI}
        Result := Trim(Utf8ToAnsi(AMessage));
        {$ELSE}
        Result := Trim(AMessage);
        {$ENDIF}
     {$ENDIF}
   end;
begin
  if Assigned(Handle) then
    ErrorMessage := PlainDriver.GetErrorMessage(Handle)
  else
    ErrorMessage := '';

  if ErrorMessage <> '' then
  begin
    if Assigned(ResultHandle) then
     Result := GetMessage(PlainDriver.GetResultErrorField(ResultHandle,PG_DIAG_SQLSTATE))
    else
      Result := '';
  end;

  if ErrorMessage <> '' then
  begin
    ConnectionLost := (PlainDriver.GetStatus(Handle) = CONNECTION_BAD);

    if Assigned(Connection) then begin
      DriverManager.LogError(LogCategory, Connection.GetConSettings^.Protocol, LogMessage,
        0, ErrorMessage);
    end else begin
      DriverManager.LogError(LogCategory, 'some PostgreSQL protocol', LogMessage,
        0, ErrorMessage);
    end;

    if ResultHandle <> nil then PlainDriver.PQclear(ResultHandle);

    if not ( ConnectionLost and ( LogCategory = lcUnprepStmt ) ) then
      if not (Result = '42P18') then
        raise EZSQLException.CreateWithStatus(Result,Format(SSQLError1, [GetMessage(ErrorMessage)])); //--> break here
  end;
end;                                                  

[miab3]

@ribut

Could you try with autocommit disabled:

ZConnection1.AutoCommit:=False;

Michal

[ribut]

@ribut

Could you try with autocommit disabled:

ZConnection1.AutoCommit:=False;

Michal

ok, problem solved

many thank's Michal

[marsupilami]

Hello ribut,

Please don't do that:

Code: Select all

    with qryIUD do begin
      Close;
      SQL.Clear;
      SQL.Add('BEGIN;');
      ExecSQL;
    end;

The ZConnection Object has the methods StartTransaction, Commit and Rollback. Please use them because Zeos handles some things internally through them.

With best regards,

Jan

[ribut]

Hello ribut,

Please don't do that:

Code: Select all

    with qryIUD do begin
      Close;
      SQL.Clear;
      SQL.Add('BEGIN;');
      ExecSQL;
    end;

The ZConnection Object has the methods StartTransaction, Commit and Rollback. Please use them because Zeos handles some things internally through them.

With best regards,

Jan

why i don't do that Jan

I used that because in my coding after insert max 100 rows I commit; transaction

[marsupilami]

Hello ribut,

Zeos as its own methots for starting, rommitting and rolling back transactions. In the PotgreSQL driver these methods are also used for managing prepared statements. Usually Zeos will create a prepared statement when you use a TZQuery or TZReadonlyQuery. These prepared statements are then kept around until a new transaction is started. When a new transacion is started Zeos will free the prepared statements that are not in use anymore. I introduced that behaviour because with PostgreSQL one can't do anything as soon as there was an error in a transaction. So Zeos will free the statemens at the beginning of the next transaction because then it knows the transaction is in a good state and will not generate more errors.
If you bypass the Zeos transaction management, Zeos will either not be able to free the prepared statements anymore or it will try to free them at inapropriate times. Applying these things your code could look like this:

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  i:Integer;
  N:Integer;
begin
  //case 2
  with qryIUD do begin
    Close;
    SQL.Clear;
    SQL.Add('DELETE FROM tabel_2;');
    ExecSQL;
  end;

  try
    with qryIUD do begin
      Connetion.StartTransaction;
    end;
    i:=0;
    N:=50;

    for i:=0 to N -1 do begin
      with qryIUD do begin
        Close;
        SQL.Clear;
        SQL.Add('insert into tabel_2(id,first_name,salary) values('+IntToStr(i)+','+QuotedStr('First_Name'+IntToStr(i))+','+CurrToStr(StrToCurr(IntToStr(i))*1000)+');');
      end;
    qryIUD.ExecSQL;
      N:=N+1;
    end;

    with qryIUD do begin
      Close;
      SQL.Clear;
      SQL.Add('insert into tabel_2(id,first_name,salary) values('+IntToStr(i+1)+','+QuotedStr('First_Name'+IntToStr(i+1))+','+CurrToStr(StrToCurr(IntToStr(i))*99000)+');');
      ExecSQL;
    end;

  except
    on E:Exception do begin
      with qryIUD do begin
        Connection.Rollback;
      end;

      ShowMessage(E.Message);
      Exit;
    end;
  end;

  with qryIUD do begin
    Connection.Commit;
  end;

  ShowMessage('Insert success..')
end;

Doing things this way Zeos will be able to do all of its management tasks. Please also note that you will need less lines of code this way and your code will look more compact.

With best regards,

Jan

[ribut]

Furthermore, whether if there are two concurrent processes startransactions, when one commit command executed if both of the startransactions same time will affect the commit command of one of the execution of TZQuery or TZReadonlyQuery it

I will try your suggestion, and it looks better if you use your suggestions.

Thank you very much Jan