Query doesn't close when SQL.Text assigned

[dseligo]

I installed ZeosLib 8.0.0 on Lazarus 3.2, FPC 3.2.2.
I found a major problem: active query doesn't close when SQL.Text property is assigned. I think it's related to this thread: viewtopic.php?t=143732

If I create table like this:

Code: Select all

  With ZQuery1 do
  begin
    SQL.Text := 'create table zeostest (fint1 int, fint2 int, primary key (fint1))';
    ExecSQL;

    SQL.Text := 'insert into zeostest (fint1, fint2) values (:fint1, :fint2)';
    Prepare;

    ParamByName('fint1').AsInteger := 1;
    ParamByName('fint2').AsInteger := 10;
    ExecSQL;

    ParamByName('fint1').AsInteger := 2;
    ParamByName('fint2').AsInteger := 20;
    ExecSQL;

    ParamByName('fint1').AsInteger := 3;
    ParamByName('fint2').AsInteger := 30;
    ExecSQL;
  end;

And then run following queries:

Code: Select all

  With ZQuery1 do
  begin
    SQL.Text := 'select * from zeostest where fint1 = :fint1';
    ParamByName('fint1').AsInteger := 1;
    Open;
    WriteLn(FieldByName('fint2').AsString);

    SQL.Text := 'select * from zeostest where fint1 = :fint1';
    ParamByName('fint1').AsInteger := 2;
    Open;
    WriteLn(FieldByName('fint2').AsString);

    SQL.Text := 'select * from zeostest where fint1 = :fint1';
    ParamByName('fint1').AsInteger := 3;
    Open;
    WriteLn(FieldByName('fint2').AsString);
  end;

In 7.2.14 I get following:

Code: Select all

10
20
30

But in 8.0.0 I get this:

Code: Select all

10
10
10

It looks like that instead of automatically closing query when SQL.Text is assigned, in version 8.0.0 new text is checked against existing one, and if they are the same, query is not closed. If I add a single space somewhere in 2nd query, results are as expected.

I tried to enable ACTIVE_DATASET_SQL_CHANGE_EXCEPTION, but that also don't detect assignment.

How can I turn on behavior like it was in previous Zeos versions?

[marsupilami]

Hello dseligo,

How can I turn on behavior like it was in previous Zeos versions?

I think you can't. This was an intentional change by Egonhugeist. See the following code from ZSqlStrings.pas:

Code: Select all

procedure TZSQLStrings.SetTextStr(const Value: string);
begin
  if Trim(Value) <> Trim(Text) then //prevent rebuildall if nothing changed
    inherited SetTextStr(Value);
end;

I think you will have to stick to the best practice, we outlilned in the other thread:

simply call .Close every time before you .Open

Best regards,

Jan

[dseligo]

Hello dseligo,

How can I turn on behavior like it was in previous Zeos versions?

I think you can't. This was an intentional change by Egonhugeist. See the following code from ZSqlStrings.pas:

Code: Select all

procedure TZSQLStrings.SetTextStr(const Value: string);
begin
  if Trim(Value) <> Trim(Text) then //prevent rebuildall if nothing changed
    inherited SetTextStr(Value);
end;

In 7.2.14. that line was like that:

Code: Select all

  if Value <> Text then //prevent rebuildall if nothing changed see:

So, I don't think this is changed (it was added Trim()).

Anyway, it looks like the deliberate change was that query is closed if assigned SQL.Text is different than previous one, but not close if assigned SQL.Text is the same?
I don't understand reason for that. If I assign something to SQL.Text I want query to be closed. I can't see any reason to leave it open.

I think you will have to stick to the best practice, we outlilned in the other thread:

simply call .Close every time before you .Open

Best regards,

Jan

If I comment out following code in ZSqlStrings.pas:

Code: Select all

if Trim(Value) <> Trim(Text) then //prevent rebuildall if nothing changed

Then it looks like it works as it should.
Can someone confirm that this change won't have other side effects?
Or is it possible to get exception if trying to open already opened query? That would help to notice potential bugs when trying to apply 'best practice'.

If not, I have to stick with 7.2.14 version. I have too much code which relies on that. It's a shame, I was looking forward to version 8.

[marsupilami]

Anyway, it looks like the deliberate change was that query is closed if assigned SQL.Text is different than previous one, but not close if assigned SQL.Text is the same?

Yes - that is the deliberate change. There is some ressource intensive work that we have to do if the SQL changes. If you want to see an example of this just assign an SQL query that has 4500 parameters.

I don't understand reason for that. If I assign something to SQL.Text I want query to be closed. I can't see any reason to leave it open.

Then close the query before you assign something to SQL. It is good practice to always call Close before you call Open.

Can someone confirm that this change won't have other side effects?

Nobody can confirm that. What you can do is setup the test suite (see https://sourceforge.net/p/zeoslib/wiki/Running the TestSuites/) and see what happens if you do your change.

If not, I have to stick with 7.2.14 version. I have too much code which relies on that. It's a shame, I was looking forward to version 8.

That is your decision to make.

[aehimself]

If you check any well-written Delphi library, they do exactly the same. No change will be triggered if the control is updated with the same data; as in reality no change actually happened. The only thing Zeos did now is apply the same logic... which is not just reasonable, in some cases necessary due to performance penalty as Jan described above.

In my opinion the code snipplet you posted makes absolutely no sense and relies on a "bug" to function. Instead of leaving your code on an obsolete Zeos version (I doubt this will be ever reverted) you should fix the consumer code. The only thing you have to do is a simple

Code: Select all

If ZQuery1.Active Then
  ZQuery1.Close;

Also, instead of repetition you should delegate the similar work to a separate method:

Code: Select all

Type
  TVariantArray = Array Of Variant;

[...]

Procedure TForm1.OpenDataSet(Const inDataSet: TZAbstractRODataSet; Const inSQLQuery: String; Const inParams: TVariantArray);
Var
  a: Integer;
Begin
  If inDataSet.Active Then
    inDataSet.Close;

  inDataSet.SQL.Text := inSQLQuery;

  For a := Low(inParams) To High(inParams) Do
    inDataSet.Params[a].Value := inParams[a];
End;

Then simply consume it with

Code: Select all

  OpenDataSet(ZQuery1, 'select * from zeostest where fint1 = :fint1', [1]);

With this you not just made your code more readable but also easier to manage - as in case of changes like this you need to change only 1 place.

Edit: The code above is pseudocode. It's possible that TZAbstractRODataSet does not publish it's SQL or Params properties, and I might have made some syntax errors. I did not put this in Delphi to verify.