Update doesn't work and break sqlite connexion... lazarus / 16-Databases ---------------------------------------------------------------------- gelinp · 2016-03-30 05:09 Hi, I've got a SQLite dabase with master / Detail SQL. Updating the detail query (and also the master) doesn' work and I can see the update dosn't work and my interface empty data ! It's like a break sqlite connexion.... This is the update function, the code comes from Sqldb tutorial n°2 (from Lazarus wiki): (The TEST code is here to reconnect database automatically, and I can see the update doesn't work...) 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 TFormMain.EnregistrerLesChangements;begin  try     // Seulement si je suis dans une transaction activée, sinon j'obtiens le    // message 'Operation cannot be performed on an inactive dataset'    if  SQLTransaction1.Active then    begin       SQLMasterSequences.ApplyUpdates;       SQLDetailSeances.ApplyUpdates;       SQLTransaction1.Commit;       // SQLTransaction1.Active est maintenant faux.        // TEST       SQLite3Connection1.Connected := true;       SQLTransaction1.Active := true; // Démarre une nouvelle transaction       SQLMasterSequences.Open;       SQLDetailSeances.Open;    end;     except    on E: EDatabaseError do    begin      SQLTransaction1.Rollback;      MessageDlg('Erreur',          'Une erreure de la base de donnée est arrivée. Le message technique est : ' +          E.message, mtError, [mbOK], 0);    end; // on E: ...  end;  // tryend; I think the problem is not into this part of code, but into components parameters.... So I add .lfm file, I hope it help to display components paramters... (I must replace .lfm extension with .txt one to upload... I use sqldb, data access, lazarus report components) Thank you for your help ! ---------------------------------------------------------------------- nadop · 2016-03-30 05:32 I do not quite understand the problem. You disappear data after the procedure EnregistrerLesChangements? ---------------------------------------------------------------------- gelinp · 2016-03-30 06:17 Yes, data disappear after calling the procedure EnregistrerLesChangements ! ---------------------------------------------------------------------- Thaddy · 2016-03-30 07:04 A lot of work has been done regarding this. I suggest to upgrade to Laz 1.6/ FPC 3.0.0 first and then report back if it still fails. FPC 2.6.4 is old and unsupported. ---------------------------------------------------------------------- mangakissa · 2016-03-30 07:24 Why is FPC 2.6.4 unsupported? Many lazarus configurations still running this version. If Windows or Linux distributors are releasing a new version, the old version is not depreciated. ---------------------------------------------------------------------- gelinp · 2016-03-30 07:39 Sorry, I forgot to up date my footer... It's Lazarus 1.4.4. with FPC 3.0.0 I'm using yet already ! ---------------------------------------------------------------------- nadop · 2016-03-30 07:43 I can help you find an error. But I need a code or a piece of code in your project. ---------------------------------------------------------------------- nadop · 2016-03-30 07:44 For debugging ---------------------------------------------------------------------- Thaddy · 2016-03-30 08:35 Quote from: mangakissa on March 30, 2016, 09:24:26 amWhy is FPC 2.6.4 unsupported? Many lazarus configurations still running this version. If Windows or Linux distributors are releasing a new version, the old version is not depreciated. FPC 2.6.4 is no longer maintained, won't receive bug fixes anymore. That is:I believe  unless under very, very, very special circumstances, maybe, such as when users are at a high risk or in the case of reproducable hardware failures through its normal use. Highly unlikely. The current stable release is FPC 3.0.0. This is well documented. It is also normal practice for any software over its sell-by date (XP comes to mind )to retire maintenance. There is under normal circumstances no reason not to upgrade to the latest release. ---------------------------------------------------------------------- Leledumbo · 2016-03-30 09:36 Quote from: mangakissa on March 30, 2016, 09:24:26 amWhy is FPC 2.6.4 unsupported? Many lazarus configurations still running this version. If Windows or Linux distributors are releasing a new version, the old version is not depreciated. FPC doesn't use multiple version maintenance as some other softwares (notably the linux kernel which has 10 supported versions). I don't think there's enough resources for that. That's why FPC 2.6.4 for Raspbian has backported patches by the Raspbian maintainer, not FPC team. ---------------------------------------------------------------------- gelinp · 2016-03-30 12:19 OK, you can download my test project, in order someone could help me to debug... I tested this project with same bug (Linux and win32 targets). The unit1 (default unit) was replaced with mainform. It's a light part of my initial project in order to focus about the problem. To compil you need to get lazreport component compiled with Lazarus 1.4.4. environement and FPC 3.0.0.. To test the procedure is : - push nav button to edit a record (yellow pen) - modify the record with the '*' caracter (at right top grid box) - push nav button to post (green chek ) - push the bouton 'Register...' at the bottom... (applyupdate and commit) This test will show youi data desappears... P.S.: there is two target compiled : test (linux executable) and test.exe (win32) ---------------------------------------------------------------------- nadop · 2016-03-30 13:02 In version Lazarus 1.6 is the same problem I do not have time to see today. Tomorrow will decide the problem. ---------------------------------------------------------------------- nadop · 2016-03-30 13:09 check for code 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        SQLDetailSeances.ApplyUpdates;       SQLMasterSequences.ApplyUpdates;       SQLTransaction1.Commit;       // SQLTransaction1.Active est maintenant faux.       SQLite3Connection1.Connected := false;       SQLite3Connection1.Connected := true;       SQLTransaction1.Active := true; // Démarre une nouvelle transaction       SQLMasterSequences.Open;       SQLDetailSeances.Open; ---------------------------------------------------------------------- nadop · 2016-03-30 13:12 in the procedure FormCreate try to change 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 DllHandle := LoadLibrary('./sqlite3.dll');  if DllHandle = 0 then    ShowMessage(SysErrorMessage(GetLastOSError)) else   ShowMessage('Successfully loaded'); On 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 SQLiteDefaultLibrary:='./libsqlite3.so'; uses unit - sqlite3dyn ---------------------------------------------------------------------- gelinp · 2016-03-30 18:20 Ok, I loaded the /usr/lib/sqlite3.so library for linux and it's better, data stay in screan. Thank you ! But I understand I need the sqlite3.dll for windows, so I have to look for tested wich system is my binarie installed on... Stay the second one problem, the update doesn't work. I added code from wiki tutorial in order to get SQLlogs. This is the result, there is no UPDATE statement ! 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 Prepare:  TQuerySQLStatement : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceExecute:  TQuerySQLStatement : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceCommit:   SQLTransaction1 : Committing transactionPrepare:  TQuerySQLStatement : SELECT * FROM PEDAGOLesSequencesExecute:  TQuerySQLStatement : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesFetch:    SQLMasterSequences : SELECT * FROM PEDAGOLesSequencesPrepare:  TQuerySQLStatement : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceExecute:  TQuerySQLStatement : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequenceFetch:    SQLDetailSeances : SELECT * from PEDAGOLesSeances WHERE FKsequence = :PKsequence I understand there is a problem with the build of the UPDATE. I was reading Working with TSQLQuery paper on the wiki and I can't find providerFlags property of the fileds into object inspector of my project... This the test project updated with SQL Logs. Same procedure to test, Register you have to go to SQL Logs page and push Update buton to get las SQL Log. Thank you agin for your help ! ---------------------------------------------------------------------- nadop · 2016-03-31 13:06 Also you can set properties In SQLQuery Options.sqoAutoApplyUpdate in True In SQLTransaction Action in caCommit And set property you DataSource AutoEdit in True And comment EnregistrerLesChangements in Quitter. ---------------------------------------------------------------------- gelinp · 2016-04-02 05:01 I reply to myself, I succedeed to correct my code and data parameters. I attached screen casts of data parameters for components Connexion, Transaction, SQLs and DataSource. And also source code and .lfm of main  form. What was wrong ? Some parameters like : TSQLQuery : sqoAutoApplyUpdates and sqoAutoCommit (not activated was wrong). Also AutoCalcFields (not activated was wrong). UpdateMethode = upWhereKeyOnlyTDataSource : AutoEdit (not activated was wrong)Transaction : Action could be caNone because I read in wiki that it is not used... But I need to activate Option stoUseImplicit. In main source code I added a procedure to connect dynamically. Finally I Idded log events, it display UPDATE SQLquery and it's fine. Look for main.pas file into zip file to read all code... : 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 TFormMain.DynamicConnect;var  dir : shortstring;begin  // Retrouve le dossier courrant (0 indique le dossier courant)  getdir(0,dir);   // bibliothèque Sqlite...  {$IFDEF Windows}  SQLiteDefaultLibrary:= dir + '\sqlite3.dll';  {$ELSE}  SQLiteDefaultLibrary:='/usr/lib/libsqlite3.so';  {$ENDIF}   // Connect  SQLMasterSequences.Close;  SQLDetailSeances.Close;   if not SQLite3Connection1.Connected then  begin       {$IFDEF Windows}       SQLite3Connection1.DatabaseName:=dir + '\data.sqlite';       {$ELSE}       SQLite3Connection1.DatabaseName:=dir + '/data.sqlite';       {$ENDIF}  end;   SQLite3Connection1.Connected := true;  SQLTransaction1.Active := true;  SQLMasterSequences.Open;  SQLDetailSeances.Open;   // We do need to tell our TSQLConnection what to log:  SQLite3Connection1.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]  // ... and to which procedure the connection should send the events:  SQLite3Connection1.OnLog:=@Self.GetLogEvent;end;     Thank you very much for your help ! Thank you again to helped me :-)