SQLite to ZMSql

[scons]

Hi,

I want to convert a SQLite db I have to a ZMSql db, it should have a very simple master-relationship. The purpose is only for viewing not altering the data itself.

I can not seem to find the right settings, when I set (see attached example) ZMQueryDataSet2: MasterSource to DataSource1 I get an error:  ZMQueryDataSet2: Field not found: "".

Can someone enlighten me what I do wrong ?

Thanks

[mangakissa]

Why 'upload' your dataset to ZMsql? It's a great tool voor using textfiles. But as your TSQLQuery it related to TBufdataset and works in your memory.

The purpose is only for viewing not altering the data itself.

That's a developping fault. Go to the property options of TDBGrid and select option dgRowSelect (http://forum.lazarus.freepascal.org/index.php?topic=2794.0). If your dataset is set to readonly, modifications can not be made.

[scons]

Thanks for you reply, unfortunately it has no effect to the issue.

This form is just a part of the application which is for viewing (and checking) on user side only, other parts do calculations on the db itself and do make changes in the db.

The idea is to get rid of third party application (in this case SQLite) and keep everything in 1 install. I was trying to follow the master-detail example included in the ZMSql demos but no succes so far. So I am missing something but can't figure out what exactly.

[hmprof]

I can not seem to find the right settings, when I set (see attached example) ZMQueryDataSet2: MasterSource to DataSource1 I get an error:  ZMQueryDataSet2: Field not found: "".

I ran the test. Indeed, I had the same error.  But this error only occurs in "Debug" mode ! in "Release" mode, everything works well, no errors. That's strange !


Test environment :
Lazarus 1.8.0 r56594 FPC 3.0.4 i386-win32-win32/win64
TZMSQL-0.1.20.1.zip   2017-12-30  (https://sourceforge.net/projects/lazarus-ccr/files/zmsql/)

[GetMem]

Hi scons,

Use parameterized queries. Change the SQL for ZMQueryDataSet2 to:

Code: MySQL  [Select]

1. SELECT
2.    PHASE AS Phase,
3.    PNR AS Pnr,
4.    PROF AS Prof,
5.    ASSDIM AS Assdim,
6.    ASSWEIGHT AS Assweight,
7.    LOAD AS Load
8. FROM
9.    ldb3
10. where LOAD = :pLOADNR

Then on ZMQueryDataSet1 afterscroll:

Code: Pascal  [Select]

1. procedure TForm1.ZMQueryDataSet1AfterScroll(DataSet: TDataSet);
2. begin
3.   ZMQueryDataSet2.Parameters.ParamByName('pLOADNR').AsString :=
4.      ZMQueryDataSet1.FieldByName('LOAD').AsString;
5.   ZMQueryDataSet2.QueryExecute;
6. end;  
7.  

Please note I did not study your database structure, maybe you should pass something totally different as parameter, but the idea is same.

[scons]

I ran the test. Indeed, I had the same error.  But this error only occurs in "Debug" mode ! in "Release" mode, everything works well, no errors. That's strange !


Test environment :
Lazarus 1.8.0 r56594 FPC 3.0.4 i386-win32-win32/win64
TZMSQL-0.1.20.1.zip   2017-12-30  (https://sourceforge.net/projects/lazarus-ccr/files/zmsql/)

Yes, you are correct, in release mode it works as it should ! So there is a bug somewhere ?

[scons]

Hi scons,

Use parameterized queries. Change the SQL for ZMQueryDataSet2 to:

Your solution works too, even in debug mode.

Thanks

[wp]

The crash happens in procedure TZMQueryDataSet.DoFilterRecord. in the if statement of the for loop:

Code: Pascal  [Select]

1.     for i:=0 to MasterFields.Count-1 do begin
2.       try
3.        //If Name=Value (Detail field=Master field) pair is provided
4.        If ((FieldByName(MasterFields.Names[i]).Value=MasterSource.DataSet.FieldByName(MasterFields.ValueFromIndex[i]).Value)
5.             or (FieldByName(MasterFields.Names[i]).AsString=MasterSource.DataSet.FieldByName(MasterFields.ValueFromIndex[i]).AsString))
6.           then Inc(vCount);
7.       except
8.         ...

If I understand correctly the author separates cases in which the simple stringlist MasterFields contains "Name=Value" pairs or not where "Name" is the name of the Detail data field and "Value" the name of the Master data field. If such "Name=Value" pairs are not used but a simple "Name" representing both fields he uses an exception to distinguish both cases. A bad idea in my eyes because an exception always interupts the program if run in the debugger - I try to restrict exceptions to the unforeseen cases. Its better to check for the presence of the "Name=Value" pairs explicitly and avoid the exception. Replacing the DoFilterRecord by the following routine makes the master-detail tables work for me both with "Name=Value" pairs and without:

Code: Pascal  [Select]

1. uses
2.   variants;
3.  
4. procedure TZMQueryDataSet.DoFilterRecord({var} out Acceptable: Boolean);
5. var
6.    i, vCount:Integer;
7.    namDetail, namMaster: String;
8.    DetailField, MasterField: TField;
9. begin
10.   //inherited behavior
11.   inherited DoFilterRecord(Acceptable);
12.   //New behavior
13.   if not Acceptable then exit;
14.   //Filter detail dataset if all conditions are met.
15.   if (not FBulkInsert)
16.     and (DisableMasterDetailFiltration=False)
17.     and (Assigned(MasterFields))
18.     and (Assigned(MasterSource))
19.     and (FMasterDetailFiltration)
20.     and (Active)
21.     and (MasterSource.DataSet.Active) then begin
22.     vCount:=0;
23.     Filtered:=True; //Ensure dataset is filtered
24.     for i:=0 to MasterFields.Count-1 do begin
25.       //try
26.        namDetail := MasterFields.Names[i];
27.        if namDetail <> '' then begin
28.          // if Name=Value (Detail field=Master field) pair is provided...
29.          namMaster := MasterFields.ValueFromIndex[i];
30.        end else begin
31.          // if single name is provided for both detail and master field
32.          namMaster := FMasterFields[i];
33.          namDetail := namMaster;
34.        end;
35.        DetailField := FindField(namDetail);
36.        MasterField := MasterSource.Dataset.FindField(namMaster);
37.  
38.        if Assigned(DetailField) and Assigned(MasterField) and
39.          VarSameValue(Detailfield.Value, Masterfield.Value)
40.        then
41.          inc(vCount);
42.     end;
43.     if vCount=MasterFields.Count then Acceptable:=True
44.       else Acceptable:=False;
45.     //Refresh slave datasets
46.     if not ControlsDisabled then  //// edgarrod71@gmail.com
47.         DoAfterScroll;
48.   end;
49. end;
50.  

[EDIT]
Hmmm... Or maybe better to use FieldByName instead of FindField because it raises an exception if a fieldname specifield in the MasterFields list does not exist?

[hmprof]

@wp
Thank you wp for your very detailed explanations. Can you propose your solution to the author of the component to integrate these patches in the sources of the component ?
(see also this discussion : http://forum.lazarus.freepascal.org/index.php/topic,38705.0.html
Thank you very much.

[wp]

I added a folder for the development version ("trunk") of zmsql to CCR: https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/zmsql/. Use this to get the latest version, new releases will only be published from time to time.

[hmprof]

I added a folder for the development version ("trunk") of zmsql to CCR: https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/zmsql/. Use this to get the latest version, new releases will only be published from time to time.

Thank you very much. That's a very good idea.