SQLite database locked with a Query using 2 tables... lazarus / 16-Databases ---------------------------------------------------------------------- gelinp · 2016-04-04 17:16 Hi, I've got a simple Master / Detail query system into my only form, it's running well. The detail query is : 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 PEDAGOLesSeances.*FROM PEDAGOLesSeancesWHERE FKsequence = :PKsequence :PKSequence is a parameter to connect to PEDAGOLesSequences table. This query need a TDBLookupCombobox to manage a state field nometat. So I modified the detail query with : 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 PEDAGOLesSeances.*, wkfLesEtats.nometat as NomEtatFROM PEDAGOLesSeances, wkfLesEtatsWHERE FKsequence = :PKsequence AND wkfLesEtats.PKetat = FKetat It's ok to manage NomEtat field, but the detail query is locked, so I can't edit it ! I read few week into a web site it ll come from second table into a query... So what can I do ? Do I need to code all my database management and forget object inspector ? Do I use a JOIN into my SQL query ? Thank you for your help ! ---------------------------------------------------------------------- gelinp · 2016-04-05 05:50 I'm responding to myself, I find myself the solution into the wiki  The text to read is : QuoteIn order to speed development, SQLQuery can try and deduce the required SQL statements. If the SQL property exists and the ParseSQL property is true (which it is by default), SQLQuery will try to generate these statements by parsing the SQL property. SQLDB stores these statements in the InsertSQL, UpdateSQL and DeleteSQL properties. However, sometimes the generated statements will not work (e.g. when inserting in tables with auto-increment/autonumber primary keys) or will be very slow. If needed, you can manually assign the statements. So ,the problem comes from autoincrement that SQLite manage with PKseance field key into  PEDAGOLesSeance table. Because Lazarus calculate automaticaly UPDATE and INSERT queries, but including the update of the key PKseance field (the autoincremented field of the table). So SQLite except a readonly error (but not a conflict with autoincrement)... So I have to write myself the UPDATE (and later INSERT) without updating autoincrement key. This is laborious work  : The key field PKseance is not updated but used as WHERE clause. Into insert SQLite will add itself the new value if PKSeance key field  is not assigned... This is the UPDATE SQL query : 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 UPDATE PEDAGOLesSeances SET nomseance = :nomseance, maxeleves = :maxeleves, groupe = :groupe, partenariat = :partenariat, outils = :outils, ressources = :ressources, deroulement = :deroulement, cote = :cote, productions = :productions, evaluation = :evaluation, FKetat = :FKetat, FKmodele = :FKmodele, ordresequence = :ordresequence, resteafaire = :resteafaire, niveaux = :niveaux,objectifsdocumentaire = :objectifsdocumentaire, objectifsdisciplinaire = :objectifsdisciplinaire, prerequisdocumentaire = :prerequisdocumentaire, prerequisdisciplinaire = :prerequisdisciplinaire WHERE PKseance = :OLD_PKseance Finaly Lazarus is not so much a RAD in this use case. I ask myself if I do prefer to not use autoincrement fileds with SQLite and autoincrment myself all integer keys into the application ! Do you know a way to calculat autoincrment using or not SQLite servivces ? Thank you for your help... ---------------------------------------------------------------------- balazsszekely · 2016-04-05 06:18 The easiest solution is to use a second query for editing "PEDAGOLesSeances" table. Let's say you have qPEDAGOLesSeance which is linked to the master query, as you described above. Add a nother TSQLQuery(for example qPEDAGOLesSeanceEdit), with the following SQL: QuoteSELECT PEDAGOLesSeances.* FROM PEDAGOLesSeances WHERE ID = :PID Then on qPEDAGOLesSeances afterscroll event: 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.qPEDAGOLesSeanceAfterScroll(DataSet: TDataSet);begin  qPEDAGOLesSeanceEdit.Close;  qPEDAGOLesSeanceEdit.ParamByName('PID').AsInteger := qPEDAGOLesSeance.FieldByName('ID').AsInteger;  qPEDAGOLesSeanceEdit.Open;end; Now your qPEDAGOLesSeance and qPEDAGOLesSeanceEdit is in sync. After this you can always edit "PEDAGOLesSeance" table with qPEDAGOLesSeanceEdit.Edit, qPEDAGOLesSeanceEdit.Insert, qPEDAGOLesSeanceEdit.Delete methods. Don't forget to post/applyupdate/commitretaining to see the changes. PS1: I suppose you have a uniqiue primary key(ID), if not you should create one. PS2: http://www.sqlite.org/faq.html#q1 ---------------------------------------------------------------------- gelinp · 2016-04-05 07:52 OK, but there is still a question : So, you recomand to use a forst SELECT Query SQLQueryLesSeances as dataset for DATASourceLesSeances wich is  connect to the TDBGrid of my form. So this TDBGrid now to displayresult a specific only SELECT Query. Ok ? But if I use an EDIT other EDIT query, what Datasource to use ? The same as previous one, it's not possible ! Si it will not be connected to the TDBGrid of the interface because there is already the a datasource with SELECT dataset...  OK I can use applyupdate and commit into source code, but only after user insert the new TDBGrid row, but this same TDBGrid will not add a new insert row because it is not anymore connected to TDBNavigation (and not connected to EDIT dataset to commit) !... So I understand it is not only the primary key to synchronize, but all fields inserted or updated from TDBGrid ! Not really Rapid Application Develpment... ---------------------------------------------------------------------- balazsszekely · 2016-04-05 07:55 After you done Editing/Inserting/Deleting with qPEDAGOLesSeanceEdit you refresh qPEDAGOLesSeance. That's it! Even better, if you do it correctly a refresh might not be needed at all. It all depends on the circumstances. ---------------------------------------------------------------------- gelinp · 2016-04-05 08:14 Ok, good solution to update SELECT query. But what about the reverse ? : when I insert à new row in TDBGrid and I would like to insert this new TDBGid values to fields of the EDIT dataset... By the way, to insert a new row into the TDBGrid I have to connect a TDBNavigation to the same Datsource of TDBGrid, which is not I want to do because Datasource of the TDBgrid is already connected to specifi SELECT Dataset, not EDIT Dataset... ---------------------------------------------------------------------- balazsszekely · 2016-04-05 08:32 First of all editing a TDBGrid directly is never a good idea. Some people may come and argue that is perfectly fine to do it, but is not. For example you insert a new row, then the user clicks somewhere else(by accident or not), what should you do with the incomplete data? Try to post it? Ask the user? It leads to confusion. Besides you should always separate, as much as possible, the interface from the business logic(see MVC). When you want to insert a new record/row just create a dialog, do not edit the grid directly. As for your example I cannot tell you more that I have already stated. Use a query->datasource->grid for browsing and a separate query/script for modifying the data. ---------------------------------------------------------------------- gelinp · 2016-04-05 11:15 OK. I know MVC, I understand what you mean. May be DBGrid edition is not a good think, I'm agree with you. .. I do not know a lot about MVC applied to Lazarus component... Do you know a good book about this subject  ? ---------------------------------------------------------------------- balazsszekely · 2016-04-05 11:28 QuoteDo you know a good book about this subject  ?No, unfortunately I don't!  But you don't need a book to solve this particular problem. Just try to implement what you already know. I can help if you like. ---------------------------------------------------------------------- gelinp · 2016-04-07 13:16 Finaly, to end this topic there is a last question ... : After I register my updated record with the Edit Dataset, the first one (the display Dataset used to scroll records and display records into the TDBGrid)  return to the first record of the TDBGrid... So I would like to save my current position into TDBGrid to avoid this reset to first record ! I was looking yesturday a long time for TDBGrid function to register bookmark and restor it, but I don't understand really this bookmark API and I get alwas an overbound error... May be a problem with a create funciton I don't call or something like that.... So, do you know a good exemple to save and restor a bookmar, or only save and restor index of the current selected line ? Thank you for your help ! ---------------------------------------------------------------------- balazsszekely · 2016-04-07 13:47 I would use the locate function: 1. Save position    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 CurID := qPEDAGOLesSeance.FieldByName('ID').AsInteger;2. Restore position    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 qPEDAGOLesSeance.Locate('ID', CurID, []); ---------------------------------------------------------------------- gelinp · 2016-04-07 14:18 OK, good idea ! Thank you  Ican't test now, but do you know if there is a risk to flash the TDBgrid control this method ? ---------------------------------------------------------------------- balazsszekely · 2016-04-07 15:24 Quote@gelimp Do you know if there is a risk to flash the TDBgrid control this method ?With the following function, there is no risk at all: 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 function LocateRecord(const AQuery: TSQLQuery; const AID: Integer): Boolean;begin  if not AQuery.DataBase.Connected then    Exit;  if not AQuery.Active then    Exit;  AQuery.DisableControls;  try    Result := AQuery.Locate('ID', AID, []);  finally    AQuery.EnableControls;  end;end; function SaveRecord(const AQuery: TSQLQuery): Integer;begin  Result := -1;  if not AQuery.DataBase.Connected then    Exit;  if not AQuery.Active then    Exit;  if AQuery.RecordCount = 0 then    Exit;  Result := AQuery.FieldByName('ID').AsInteger;end;