Master-Detail in SQLite lazarus / 16-Databases ---------------------------------------------------------------------- folkeu08 · 2021-04-08 13:37 Hi, I am creating an application with SQLite tables. I am using components from Lazarus 'SQLdb' library I have a master table (Cell) and a detail table (Contract) whose common link field is "cell_id". This is declared ainbsi in the base. I am not using a TDBNavigator component to manage tables. I write scripts in SQL for a tSQLQuery component associated with a TDataSpource component for each function (Insert, edit, delete) on tables. This procedure allows the insertion of fields in the detail table (Contract) without declaring in the SQLQuery the DataSource of the SQLQuery of the "Cell" table of the DataSource property. 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 TNewContrat_form.BtnEnregistrerclick (Sender : TObject);  begin    Cellule_DataModule.Contrat_SQLQuery.SQL.Clear;   Cellule_Datamodule.Contrat_SQLQuery.SQL.Text := 'insert into Cellule_Contrat (id_cellule, statut, grade, categorie, date_debut, date_fin, duree, fin_contrat, date_avantfin) values (:id_cellule, :statut, :grade, :categorie, :date_debut, :date_fin, :duree, :fin_contrat, :date_avantfin);';    Cellule_DataModule.Contrat_SQLQuery.Prepare;      Try        Cellule_D atamodule.Contrat_SQLQuery.Params.ParamByName('id_cellule').AsInteger := Var_Agent;        Cellule_Datamodule.Contrat_SQLQuery.Params.ParamB yName('statut').AsString := Statut_ComboBox.Caption;        Cellule_Da tamodule.Contrat_SQLQuery.Params.ParamByName('grade').AsString := Grade_ComboBox.Caption;        Cellule_Datamodule.Contrat_SQLQuery.Par ams.ParamByName('categorie').AsString := Categorie_ComboBox.Caption;      Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('date_debu t').AsDate := DateDebut_DateEdit.Date;        Cellule_Datamodule.Contr at_SQLQuery.Params.ParamByName('date_fin').AsDate := DateFin_DateEdit.Date;        Cellule_Datamodule.Contrat_SQLQuery.Para ms.ParamByName('duree').AsInteger := Duree_SpinEdit.Value;        Cell ule_Datamodule.Contrat_SQLQuery.Params.ParamByName('fin_contrat').AsBo olean := AvantTerme_CheckBox.Checked;        Cellule_Datamodule.Contra t_SQLQuery.Params.ParamByName('date_avantfin').AsDate := DateAvantFin_DateEdit.Date;         Cellule_Datamodule.Contrat_SQLQuery.ExecSQL;        Cellule_Datamodule.Contrat_SQLQuery.SQLTransaction.Commit;   except      on e: Exception do          begin            e.Message  := e.Message + 'Insertion annulée.';            Cellule_Datamodule.Contrat_SQLQuery.SQLTRansaction.Rollback;          raise e;          end;      end;   //Refesh de la grille  Cellule_Datamodule.Contrat_SQLQuery.SQL.Clear;  Cellule_Datamodule.Contrat_SQLQuery.SQL.Text := 'SELECT * from cellule_contrat WHERE id_cellule=:id_cellule;';  Cellule_Datamodule.Contrat_SQLQuery.Prepare;  Close;  end;                                         If I use this same procedure by associating the Datasource of the SQLQuery of the table Cell (Master) in the Datasource property of the SQLQuery of the table "contract" (detail), an error occurs. Do you have any idea of ​​the cause? Thanks ---------------------------------------------------------------------- egsuh · 2021-04-09 05:58 In normal cases SQLQuery does not need Datasource.  Datasources are for data controls' datasource property, and you assign datasets (e.g. TSQLQuery) to Datasource.dataset property. If you are using remote access you may need datasource for datasets. ---------------------------------------------------------------------- devEric69 · 2021-04-09 07:39 If you want to use the TDatalink design pattern, and the automatic " master-details " articulation pattern (and thus, effectively use the TDatasource component, whether or not you need visual TControls to visually browse and CRUD the data-records), then the connection between the properties of the components must be set like this:                            ┌-------------------------┐                             | TDataset master (1) |                            └-------------------------┘                                          ↑                            ┌-------------------------┐                             | TDatasource master |                 ( ⇦ needed, in order to bridge the 2 datasets)                            └-------------------------┘                                  ↗                               ↗                            ↗      ┌-------------------------┐      | TDataset details (+) |     └-------------------------┘ Note @egsuh: here, there is no TControl - for the relation's master side - connected to the needed TDatasource master (since, there is no need to browse and CRUD the master table). But, there is effectively no TDatasource details (since here, having no need to browse and CRUD the details table, no TControl - for the relation's details side - will be dropped on a TForm). ---------------------------------------------------------------------- egsuh · 2021-04-09 08:56 @devEric69 Does TSQLQuery provide Master-detail relationship? I remember old Delphi TTables, which defined master-detail relationship. I think that TSQLite3DataSet may provide such functionality, but not sure about TSQLQuery. Sorry, I have to check it myself, but right now I'm too tired because I'm tied up with other things^^  I might need to implement such relationship later. ---------------------------------------------------------------------- devEric69 · 2021-04-09 09:52 Hello @egsuh, Quote from: egsuh on April 09, 2021, 10:56:46 amDoes TSQLQuery provide Master-detail relationship? I remember old Delphi TTables, which defined master-detail relationship. I think that TSQLite3DataSet may provide such functionality, but not sure about TSQLQuery. Yes, it can, it's exactly the same thing . For your information, this is what the documentation for tsqlquery.datasource  says: "When Open or ExecSQL is called, and the Datasource property is not Nil then for each parameter for which no value was explicitly set (its Bound property is False), the value will be retrieved from the dataset connected to the datasource." Said differently, the values of the parameter(s) in the SQL clause "WHERE details.master_record_no = :master_record_no" in the query's details are retrieved automatically from the current value of the pointed TDataset's "master.master_record_no" field, through this TDataset.Datasource property. In addition, TSQLQuery allows a deeper filtering with its SQL "WHERE" SQL clause, which is finer than a simple TTable that does a SQL "fetch all" (not necessarily ad'hoc for the network, when there are large amounts of data). Quote from: egsuh on April 09, 2021, 10:56:46 amI might need to implement such relationship later. I've tested SQLite with Zeos components: it works very well (having said that, I have only queried and used small tables).