SQLite to ZMSql lazarus / 16-Databases ---------------------------------------------------------------------- scons · 2018-01-05 15:26 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 · 2018-01-06 08:02 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. QuoteThe 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 · 2018-01-06 09:10 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 · 2018-01-06 09:45 Quote from: scons on January 05, 2018, 04:26:07 pmI 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/) ---------------------------------------------------------------------- balazsszekely · 2018-01-06 09:47 Hi scons, Use parameterized queries. Change the SQL for ZMQueryDataSet2 to: Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT   PHASE AS Phase,   PNR AS Pnr,   PROF AS Prof,   ASSDIM AS Assdim,   ASSWEIGHT AS Assweight,   LOAD AS LoadFROM   ldb3where LOAD = :pLOADNR Then on ZMQueryDataSet1 afterscroll: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.ZMQueryDataSet1AfterScroll(DataSet: TDataSet);begin  ZMQueryDataSet2.Parameters.ParamByName('pLOADNR').AsString :=     ZMQueryDataSet1.FieldByName('LOAD').AsString;  ZMQueryDataSet2.QueryExecute;end;   Please note I did not study your database structure, maybe you should pass something totally different as parameter, but the idea is same. ---------------------------------------------------------------------- scons · 2018-01-06 09:50 Quote from: hmprof on January 06, 2018, 10:45:29 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 ? ---------------------------------------------------------------------- scons · 2018-01-06 10:00 Quote from: GetMem on January 06, 2018, 10:47:18 amHi scons, Use parameterized queries. Change the SQL for ZMQueryDataSet2 to: Your solution works too, even in debug mode. Thanks ---------------------------------------------------------------------- wp · 2018-01-06 10:32 The crash happens in procedure TZMQueryDataSet.DoFilterRecord. in the if statement of the for loop: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     for i:=0 to MasterFields.Count-1 do begin      try       //If Name=Value (Detail field=Master field) pair is provided       If ((FieldByName(Ma sterFields.Names[i]).Value=MasterSource.DataSet.FieldByName(MasterFiel ds.ValueFromIndex[i]).Value)            or (FieldByName(MasterFields.N ames[i]).AsString=MasterSource.DataSet.FieldByName(MasterFields.ValueF romIndex[i]).AsString))          then Inc(vCount);      except        ... 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses  variants; procedure TZMQueryDataSet.DoFilterRecord({var} out Acceptable: Boolean);var   i, vCount:Integer;   namDetail, namMaster: String;   DetailField, MasterField: TField;begin  //inherited behavior  inherited DoFilterRecord(Acceptable);  //New behavior  if not Acceptable then exit;  //Filter detail dataset if all conditions are met.  if (not FBulkInsert)    and (DisableMasterDetailFiltration=False)    and (Assigned(MasterFields))    and (Assigned(MasterSource))    and (FMasterDetailFiltration)    and (Active)    and (MasterSource.DataSet.Active) then begin    vCount:=0;    Filtered:=True; //Ensure dataset is filtered    for i:=0 to MasterFields.Count-1 do begin      //try       namDetail := MasterFields.Names[i];       if namDetail <> '' then begin         // if Name=Value (Detail field=Master field) pair is provided...         namMaster := MasterFields.ValueFromIndex[i];       end else begin       // if single name is provided for both detail and master field       namMaster := FMasterFields[i];         namDetail := namMaster;     end;       DetailField := FindField(namDetail);       MasterField := MasterSource.Dataset.FindField(namMaster);        if Assigned(DetailField) and Assigned(MasterField) and         VarSameValue(Detailfield.Value, Masterfield.Value)       then         inc(vCount);    end;    if vCount=MasterFields.Count then Acceptable:=True      else Acceptable:=False;    //Refresh slave datasets    if not ControlsDisabled then  //// edgarrod71@gmail.com      DoAfterScroll;  end;end;  [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 · 2018-01-07 09:51 @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 · 2018-01-09 11:03 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 · 2018-01-13 07:51 Quote from: wp on January 09, 2018, 12:03:57 pmI 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.