DBLookupComboBox lazarus / 16-Databases ---------------------------------------------------------------------- olegko · 2016-03-15 18:08 Hi, 1. I have a DBLookupComboBox and two tables (TSQLQuery), let's say Main and Lookup. Latter has fields ID and NAME. DBLookupComboBox has DataSource = Main, ListSource = Lookup, KeyField = ID, ListField = NAME. 2. I add a new record to Lookup table, call ApplyUpdates on it and commit the transaction. 3. Then I create a new record in the Main table. There's no default values, so all the fields are NULL. Before I change anything, DBLookupComboBox displays NAME of a record I created on the step 2, while other DBLookupComboBox'es display nothing, as expected for NULLs. 4. When I call Post on the Main table, the field of DBLookupComboBox has NULL value. Can someone advise, where's my fault? Thanks! Oleg ---------------------------------------------------------------------- mangakissa · 2016-03-16 08:04 It depents how your transactions are linked to the main and lookuptable. You  say commit. This updates the record to the database and close the dataset, also your lookuptable. Try to use commitreaining. ---------------------------------------------------------------------- olegko · 2016-03-16 10:23 Sorry for not being specific. Here's what I have for the lookup table: 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 TdmMain.qThemesAfterPost(DataSet: TDataSet);begin  qThemes.ApplyUpdates;  transMain.CommitRetaining;end;  There is the same handler for the main table as well. After that I open modal form containing the DBlookupcombobox: 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 TfmMain.pAddExecute(Sender: TObject);begin  dmMain.qProjects.Insert;  fmProject.ShowModal;end;  Closed dataset is not a problem, as I actually see lookup items in the combo box. ---------------------------------------------------------------------- olegko · 2016-03-16 12:44 2 more cents: I tried to create the form containing the combobox dynamically: 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 TfmMain.pAddExecute(Sender: TObject);begin  dmMain.qProjects.Insert;  fmProject:=TfmProject.Create(Nil);  fmProject.ShowModal;  FreeAndNil(fmProject);end;   In this case combo box works as expected. However, that's not an ultimate workaround, as I have some db lookup comboboxes on the main form as well. So any advice is appreciated. ---------------------------------------------------------------------- olegko · 2016-03-16 13:16 And, finally, my solution: after adding a new record to lookup table, I set DBLookupCombobox.LookupCache property to True in order to invoke private UpdateLookup method. 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 TdmMain.qThemesAfterPost(DataSet: TDataSet);begin  qThemes.ApplyUpdates;  transMain.CommitRetaining;  fmProject.cbTheme.LookupCache:=True;end; It works even when I create fmProject statically, i.e. in the LPR. P.S. Setting LookupCache via Object Inspector in design time does not work ---------------------------------------------------------------------- goodname · 2016-03-16 13:18 Quote from: mangakissa on March 16, 2016, 09:04:09 amIt depents how your transactions are linked to the main and lookuptable. You  say commit. This updates the record to the database and close the dataset, also your lookuptable. Try to use commitreaining. This was true for FPC 2.6 but is no longer true for FPC 3.0. In FPC 3.0 the commit command should commit the data, close the transaction, and leave the result set open. The commitretaining command in FPC 3.0 commits the data, leaves the transaction open, and leaves the result set open. The commit command now retains the dataset in 3.0 but but did not in 2.6. This was done to address the issue of long running open idle transactions which is usually considered to be a application bug that can eat away at database server resources. ---------------------------------------------------------------------- rvk · 2016-03-16 13:28 Quote from: goodname on March 16, 2016, 02:18:59 pmQuote from: mangakissa on March 16, 2016, 09:04:09 amIt depents how your transactions are linked to the main and lookuptable. You  say commit. This updates the record to the database and close the dataset, also your lookuptable. Try to use commitreaining. This was true for FPC 2.6 but is no longer true for FPC 3.0. In FPC 3.0 the commit command should commit the data, close the transaction, and leave the result set open. The commitretaining command in FPC 3.0 commits the data, leaves the transaction open, and leaves the result set open. The commit command now retains the dataset in 3.0 but but did not in 2.6. This was done to address the issue of long running open idle transactions which is usually considered to be a application bug that can eat away at database server resources. Are you absolutely sure this is the case in FPC 3.0.0 (the one installed with Lazarus 1.6) ? Because if I do a SQLite3Connection1.Transaction.Commit; here, the Dataset, which was open, is closed automatically afterwards. SQLite3Connection1.Transaction.CommitRetaining; leaves it open. This is also the case in trunk. If I look in the source I see this: 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 TSQLTransaction.Commit;begin  if Active  then    begin    CloseDataSets;    If LogEvent(detCommit) then      Log(detCommit,SCommitting);    if (stoUseImplicit in Options) or SQLConnection.AttemptCommit(FTrans) then      begin      CloseTrans;    FreeAndNil(FTrans);      end;    end;end; So I see the CloseDataSets but not the OpenDataSets. ---------------------------------------------------------------------- goodname · 2016-03-16 13:44 As far as I understand the SQLite3Connection does a lot of it's own thing. The change I'm talking about was in SQLdb. Yes I'm sure the change was done along with implicit transaction commit for PostgreSQL and MySQL. ---------------------------------------------------------------------- rvk · 2016-03-16 14:00 Quote from: goodname on March 16, 2016, 02:44:30 pmAs far as I understand the SQLite3Connection does a lot of it's own thing. The change I'm talking about was in SQLdb. Yes I'm sure the change was done along with implicit transaction commit for PostgreSQL and MySQL. TSQLConnection is part of SQLdb. But underlying connection-code can implement this differently. But to be sure, I just tested this with a TMySQL56Connection in trunk and a manual Commit really closes the dataset. I can't speak for PostgreSQL though. Strange that PostgreSQL would behave different from MySQL, SQLite3 and Firebird in this case. (Note: I'm talking about a manual Commit here, not an implicit one) But it's fine... it doesn't bother me. I'm used to it because it's been always the case that a Commit closes the dataset (also in Delphi). (Or the change you're thinking of is rolled back ) ---------------------------------------------------------------------- rvk · 2016-03-16 14:11 @goodname, I now see what you mean. There is a new Options-property for TSQLQuery. If you include sqoKeepOpenOnCommit in it, a Commit will not close the dataset. But this is not default included. So normally the dataset are closed. ---------------------------------------------------------------------- goodname · 2016-03-16 14:29 Quote from: rvk on March 16, 2016, 03:11:31 pmThere is a new Options- property for TSQLQuery. If you include sqoKeepOpenOnCommit in it, a Commit will not close the dataset. But this is not default included. So normally the dataset are closed. I thought that sqoKeepOpenOnCommit being true was the default behaviour. In my opinion it should be the default but I suppose it is not due to backward compatibility. The sqoKeepOpenOnCommit applies to all SQLdb connections and the database engine does not matter.