Lazarus and Zeos Master/Detail error

[patyi]

Hi !

In Lazarus 0.92.24 and Zeos 315 Testing the master/detail faild on opening
the detail dataset.

I have on form 2 ZQuery, first is master and another is slave (detail),
with properly connected trought masters datasource. Required fields is exists in both datasets !

Here is the SQL monitor generated file of wath is hapening :

2007-12-09 18:02:46 cat: Execute, proto: mysql-5, msg: SHOW TABLES FROM kugel_07 LIKE 'sifarnik'
2007-12-09 18:02:46 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM kugel_07.sifarnik LIKE '%'
2007-12-09 18:02:56 cat: Execute, proto: mysql-5, msg: SELECT * FROM prometz
WHERE tip = "OTP" AND dok = 1
ORDER BY tip, dok

2007-12-09 18:02:56 cat: Execute, proto: mysql-5, msg: SHOW TABLES FROM kugel_07 LIKE 'prometz'
2007-12-09 18:02:56 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM kugel_07.prometz LIKE '%'
2007-12-09 18:02:56 cat: Execute, proto: mysql-5, msg: SELECT * FROM promet
WHERE tip = :tip AND dok = :dok
ORDER BY tip, dok
, errcode: 1064, error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':tip AND dok = :dok
ORDER BY tip, dok' at line 2

It is a bug or I hawe to do something different to get correct result ?

Thanks.

[mdaems]

Well, it's the :tip thing that's wrongly sent to the server. Did you set CheckParams to false on the detail query? I think it should be true. This resolves the parameters to real values before sending them to the server.

If it's easy to package project (+ data script!) I could give it a try when it's not the problem above. Just post it here or sent it by pm when the data is sensitive.

Mark

[patyi]

Hi !

Thanks for quick response !

Param check is True ... I made a simle project with sql script in attachment ... so try it !

[mdaems]

Hi,

When running your project I get an error here. Program stops functioning...
dsparams.inc : (fcl-db fpc source dir)

Code: Select all

Procedure TParam.AssignField(Field: TField);
begin
  if Assigned(Field) then
    begin
    // Need TField.Value
    AssignFieldValue(Field,Field.Value); <-----
    Name:=Field.FieldName;
    end
  else
    begin
    Clear;
    Name:='';
    end
end;                          

Unfortunately I have no experience debugging fpc programs...
I have the impression the try...finally block in TZAbstractRODataset.SetStatementParams doesn't work here very well. I get a runtime error instead of jumping to the finally clause. I suppose you also get this error resulting in not all parameters being replaced by the actual values.
So parameter processing seems to start because CheckParams is True, but apparently it doesn't succeed.

Is this an fpc bug? Did somebody else with fpc experience try this?

[patyi]

Yes, I have that error on Windows, on Linux just stop executing the program.

I'm read samthing on fpc documentation abaut diffrernces between Delphi
(Object Pascal) sintax and Free Pascal abaut assigning types or objects, but dont know what that mean in this context exactly. I will investigate it ...

[patyi]

Hi !

I made a simple test project on Win XP and Delphi, it works perfect !
The master/detail bug exists definetly on Lazarus, and I'm sure that is
a sintax difference between Delphi and FPC. Unforunately my knolage is not enough to do myself ... Please help to fix this bug ...

(test is done with Zeos Trunc317, Delphi 7, Lazarus 0.9.24, Lazarus 0.9.25 and MySQL 5.0.45 on Ubuntu 7.10 and allso Win XP)

[mdaems]

Did you already try to investigate the fpc source code? Maybe I'll find some time now. Just to make sure you didn't solve it yourself yet.

[patyi]

Yes, but without success

[patyi]

Yes, but without success

[mdaems]

For the interest of others, the mail I sent to patyi after debugging a little.

Hi Patyi,

I tracked the error to
Procedure TParam.SetDataType(AValue: TFieldType);

Var
VT : Integer;

begin
FDataType:=AValue;
VT:=FieldTypetoVariantMap[AValue];
If (VT=varError) then
clear
else
if not VarIsEmpty(FValue) then
begin
Try
FValue:=VarAsType(FValue,VT) <-- here FValue=void, VT = 3,
AValue=FTINTEGER
except
Clear;
end { try }
end;
end;

When passing this procedure for the other parameter (:tip) no error
occurs, but this doesn't mean the good value is set, of course.

Thus... I changed your detail query a little...
SELECT * FROM detail
WHERE tip = :tip /* AND dok = :dok*/
ORDER BY tip, dok, red

This will not give the wanted result, of course... BUT this works as it
should work with this query.
Looks like the problem is the integer field in the param link.
Let's change the database fields a little.
alter table master modify dok varchar(10);
alter table detail modify dok varchar(10);
and
undo the query change
-->It works!!

Conclusion 1 : don't use integers in your primary keys and this works.
Conclusion 2 : this is a bullshit solution. Every DBA freezes when you
show him this 'solution'.
Question 1 : what's wrong? Do we feed a bad integer variant?
Question 2 : how to read all properties of an object in Lazarus? Now I
used tricks to read one value at a time, but that requires lazarus
rebuilds every time.

Response from Patyi

Hi Mark,

Here is my workaround for master detail :

procedure TForm1.ZQuery1AfterScroll(DataSet: TDataSet);
begin
ZQuery2.Close;
ZQuery2.Params[0].Value := DataSet.FieldByName(ZQuery2.Params[0].Name).Value;
ZQuery2.Params[1].Value := DataSet.FieldByName(ZQuery2.Params[1].Name).Value;
ZQuery2.Open;
end;

I know it is not a solution for Zeos but in practice it is work perfectly.