* * *

Author Topic: SQLite to ZMSql  (Read 929 times)

scons

  • Full Member
  • ***
  • Posts: 117
SQLite to ZMSql
« on: January 05, 2018, 04:26:07 pm »
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
Windows 10-64bit Lazarus 1.8 + FPC 3.0.4

mangakissa

  • Hero Member
  • *****
  • Posts: 764
Re: SQLite to ZMSql
« Reply #1 on: January 06, 2018, 09:02:01 am »
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.
Quote
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.
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

scons

  • Full Member
  • ***
  • Posts: 117
Re: SQLite to ZMSql
« Reply #2 on: January 06, 2018, 10:10:25 am »
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.
Windows 10-64bit Lazarus 1.8 + FPC 3.0.4

hmprof

  • New member
  • *
  • Posts: 5
Re: SQLite to ZMSql
« Reply #3 on: January 06, 2018, 10:45:29 am »
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

  • Hero Member
  • *****
  • Posts: 2618
Re: SQLite to ZMSql
« Reply #4 on: January 06, 2018, 10:47:18 am »
Hi scons,

Use parameterized queries. Change the SQL for ZMQueryDataSet2 to:
Code: MySQL  [Select]
  1.    PHASE AS Phase,
  2.    PNR AS Pnr,
  3.    PROF AS Prof,
  4.    ASSDIM AS Assdim,
  5.    ASSWEIGHT AS Assweight,
  6.    LOAD AS Load
  7.    ldb3
  8. 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

  • Full Member
  • ***
  • Posts: 117
Re: SQLite to ZMSql
« Reply #5 on: January 06, 2018, 10:50:06 am »

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 ?
Windows 10-64bit Lazarus 1.8 + FPC 3.0.4

scons

  • Full Member
  • ***
  • Posts: 117
Re: SQLite to ZMSql
« Reply #6 on: January 06, 2018, 11:00:36 am »
Hi scons,

Use parameterized queries. Change the SQL for ZMQueryDataSet2 to:


Your solution works too, even in debug mode.

Thanks
Windows 10-64bit Lazarus 1.8 + FPC 3.0.4

wp

  • Hero Member
  • *****
  • Posts: 4074
Re: SQLite to ZMSql
« Reply #7 on: January 06, 2018, 11:32:58 am »
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?
« Last Edit: January 06, 2018, 02:46:01 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

hmprof

  • New member
  • *
  • Posts: 5
Re: SQLite to ZMSql
« Reply #8 on: January 07, 2018, 10:51:16 am »
@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

  • Hero Member
  • *****
  • Posts: 4074
Re: SQLite to ZMSql
« Reply #9 on: January 09, 2018, 12:03:57 pm »
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

hmprof

  • New member
  • *
  • Posts: 5
Re: SQLite to ZMSql
« Reply #10 on: January 13, 2018, 08:51:50 am »
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.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus