[solved] DBNavigator does not write into DB lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2022-08-22 19:06 I have an IBDynamicGrid and a DBNavigator They are linked to the same datasource. The data show up fine on  IBDataSet1.Active:=true; This Navigator seems active, but it does not allow me to change the data I can see in the grid. The cursor-promtpt goes to the cell, but does not react, if I key in something. What do I have to change? ---------------------------------------------------------------------- tonyw · 2022-08-23 08:21 Quote from: Nicole on August 22, 2022, 09:06:00 pmI have an IBDynamicGrid and a DBNavigator They are linked to the same datasource. The data show up fine on  IBDataSet1.Active:=true; This Navigator seems active, but it does not allow me to change the data I can see in the grid. The cursor-promtpt goes to the cell, but does not react, if I key in something. What do I have to change? All that a DBNavigator does is to change the current row, delete or add a row. Perhaps you need to set the focus to the grid after using DBNavigator to change row. You can test this out with the mouse. If you select a new row with DBNavigator, click on the cell and if it then  accepts the characters you key in then the problem is the focus. Note that DBNavigator is not usually used with a grid. It is more usually used with a form showing one or more data aware controls that show the current row. The DBNavigator then allows you to move between rows. ---------------------------------------------------------------------- Nicole · 2022-08-23 09:21 Thank you for your reply. Unfortunately, the grid does not react to any modification of the table. No insert, no edit. I just can use the arrow to move from one cell or row to the next. Is there something as a write-protection anywhere? What I want to do: I want to use the grid like an Excel-sheet. This very table is slim and shall be maintained visually / manually. In Delphi I did this by a TDBTable or TDBStringGrid, but under IBX it does not work the same. Not sure now, how. May the "IBDynamicGrid" make a difference? ---------------------------------------------------------------------- Handoko · 2022-08-23 09:40 Maybe you can try using a TDBGrid. ---------------------------------------------------------------------- rvk · 2022-08-23 09:54 Quote from: Nicole on August 22, 2022, 09:06:00 pmThis Navigator seems active, but it does not allow me to change the data I can see in the grid. What do you mean by "seems active"? Is the Edit, Insert and Delete button of TDBNavigator active or are they greyed out? If they are greyed out then you are dealing with a read-only dataset. You can try to put a IBQuery1.Edit under a button and see what kind of error you get. If your dataset is in read-only mode... did you add a UpdateObject with INSERT, UPDATE and DELETE statements? Otherwise your dataset will stay in read-only mode because it doesn't know how to add, update or delete anything. ---------------------------------------------------------------------- Nicole · 2022-08-23 10:39 It is, as you guessed (greyed out). What you say after the if..... pls again and more slowly for dummies. The code is from Delphi and FireDac. I just added a table there and connected it with a query and could edit it. The same was in FlameRobin: I committed a select-statement there and in the result I could edit my DB in the showing up editor. I had to confirm it with a click and then the change was in the db. So my knowledge at the moment is, "works like this, you do not have to do anything more". What is an "update object"? (sorry for this question) And: Do I need different statements for insert, update and delete in IBX? In my old code the only difference between those statements are the syntax in the SQL-string (oh yes, it was a hard time to find the code for each occasion). The code I put in ADquery1.SQL.text:='....'; followed by ADQuery1.Active:=true; But somehow this seems wrong in IBX. Never the less I do not regret my migration to Lazarus. It is that great to be in opensource. ---------------------------------------------------------------------- rvk · 2022-08-23 10:55 Quote from: Nicole on August 23, 2022, 12:39:06 pmWhat you say after the if..... pls again and more slowly for dummies. ... What is an "update object"? (sorry for this question)No problem. Yesterday we discussed the TIBUpdateSQL component. https://forum.lazarus.freepascal.org/index.php/topic,60316.msg450975.h tml#msg450975 With the TIBQuery you only provide the SELECT statement. When doing insert etc. you changed the SQL of TIBQuery to execute INSERT and UPDATE. But with a live DBGrid this isn't really convenient because the grid would loose its dataset records. And without INSERT and UPDATE the dataset (TIBQuery is a dataset of records) will be in read-only state and can't be switched to .edit status. So, there comes TIBUpdateSQL in. You attach it to the TIBQuery (in the Object Inspector all the way at the end) and right click it and choose UpdateSQL editor. Then choose the table and the Generate SQL button to let it generate the INSERT, UPDATE and DELETE SQL text. In FireDAC (and also in some Lazarus sqldb components) these SQL statements are generated automatically behind the scenes. I don't think IBX generates them automatically, at least it never did in the Delphi version, so you have to provide them manually (with help of that Generate SQL button). This is also because with complex SELECT SQL statements, there often can't be a UPDATE generated for it and this would need to be done manually. Now that the TIBQuery has access to the TIBUpdateSQL (and its SQL statements) it can put the dataset (set of records) in a read/write state and the buttons in the navigator are no longer greyed out, and the editing options in the TDBGrid and TIBDynamicGrid will work properly. I hopes this explanation makes it a bit more clear (otherwise please ask ). ---------------------------------------------------------------------- tonyw · 2022-08-23 11:10 Quote from: Nicole on August 23, 2022, 11:21:30 amThank you for your reply. Unfortunately, the grid does not react to any modification of the table. No insert, no edit. I just can use the arrow to move from one cell or row to the next. Is there something as a write-protection anywhere? What I want to do: I want to use the grid like an Excel-sheet. This very table is slim and shall be maintained visually / manually. In Delphi I did this by a TDBTable or TDBStringGrid, but under IBX it does not work the same. Not sure now, how. May the "IBDynamicGrid" make a difference? Your best strategy is to open the "employee" example that comes with IBX. This uses the Firebird example EMPLOYEE database. This is normally installed with Firebird together with its alias "employee". I quickly dropped a TDBNavigator on to the project's main form - just under the grid. All I then did was to set the DBNavigator's DataSource to "EmployeeSource" - available from the drop down list in the object inspector. I then compiled and ran the example project. DBNavigator seems to work work perfectly, scrolling up and down the grid. When you press DBNavigator's edit button the current row becomes editable, and the the first cell receives key presses. Perhaps you should compare this example with your project to see where you may be going wrong. ---------------------------------------------------------------------- Nicole · 2022-08-23 17:18 Thank you for the explanation. Unfortunately, I do not have it now: I made some buttons to fill the TDBDynamicGrid. On click eg. looks like 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 TFrame_Waren.SpeedButton_WarenMitZeitClick(Sender: TObject);begin  IBDataSet_kennzahlen.SelectSQL.Text:=' Select A.Comm, A.TRADE_AB, A.TRADE_BIS, A.PAUSE, A.BEGINN, A.ENDE, ' +  ' A.VOLLZEIT From TBKENNZAHLEN A';  IBDataSet_kennzahlen.Active:=true;end; You will notice the '_kennzahlen' which shows me, what works together. And there is an IBdataset_kennzahlen. Is this enough in components? It works with the default database1 and transaction1. Now I generated an "IBUpdateSQL_kennzahlen" and am clueless, because I do not see, where to hook in. ---------------------------------------------------------------------- tonyw · 2022-08-23 22:06 Quote from: Nicole on August 23, 2022, 07:18:33 pmNow I generated an "IBUpdateSQL_kennzahlen" and am clueless, because I do not see, where to hook in. Set the UpdateObject property of the TIBQuery to the name of the TIBUpdateSQL object. ---------------------------------------------------------------------- rvk · 2022-08-23 22:12 Quote from: Nicole on August 23, 2022, 07:18:33 pmNow I generated an "IBUpdateSQL_kennzahlen" and am clueless, because I do not see, where to hook in. The TIBUpdateSQL needs to be connected to the TIBQuery.UpdateObject. It's all the way at the buttom in the object inspector (left side) of TIBQuery. You might need to use the scroll-bar (I had to which is why I couldn't find it initially). Or you can set it at runtime with TIBQuery.UpdateObject. If you set the SelectSQL manually in code for TIBQuery, then you also need to do the same for the .InsertSQL, .UpdateSQL and .DeleteSQL in TIBUpdateSQL. Why do you need to set the SQL in code and not in design-time? If it's because you have multiple tables you want to show (and switch) in DBGrid, you could use multiple TIBQuery/TIBUpdateSQL. And switch then in runtime in TDataSource. Otherwise you would need to change the SQL's in TIBUpdateSQL too. I use a special defined function for that which generates those at runtime. I'm not sure you can access the auto "generate sql" which IBX uses at design time in runtime. Tony should be able to tell you if that's possible. (Something like TIBUpdateSQL.GenerateSQL(table: string)?) It should be possible for simple select statements. For complex ones with joins this is not possible. ---------------------------------------------------------------------- Nicole · 2022-08-24 09:24 @Tony said Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Set the UpdateObject property of the TIBQuery to the name of the TIBUpdateSQL object. This would be possible from the syntax.  I use an IBQuery1 for testing and it would offer me such a field to do so. But I have no TIBQuery_kennzahlen, only dataset_kennzahlen and datasource_kennzahlen. The Dataset_kennzahlen does my select-queries which are generated by the button-methods (see above one code example). The DynamicGrid is filled fine. So my questions are: 1) must I add an IBQuery_kennzahlen as well? 2) if yes, where to hook in? @rvk (same question if an IBQuery is needed and if yes, where) My SQL-joins are deep in the really advanced code which will keep me busy until Christmas ;-) At this time I just want to do this - see in my TDynamicGrid a cell, let us say of time: 15:00:00 - change it to 16:00:00 - hit commit-Icon in the navigator - have the change in my db ---------------------------------------------------------------------- egsuh · 2022-08-24 09:52 I tested and this works. I believe you have followings on a form    - TIBDatabase    - TIBTransaction    - TIBDataSet    - TDataSource    - TDBGrid (or something else similar to it)    - TDBNavigator I assume that you are trying to open and edit one table. With TIBDataSet, try followings. Set TIBDataSet.AllowAutoActivateTransaction to true (tick it in object inspector). With TIBDataSet, when you press  (...) button of SelectSQL in object inspector, a small window will open, and you may be able to choose table and fields there. And there is a button with caption "Generate SQL".  Press the button, and then press OK. Now do the same thing with DeleteSQL, ModifySQL, RefreshSQL and InsertSQL.  With these SQLs filled in, I could see more buttons of TDBNavigator turn green.  I ran the program and succeeded in inserting a record. ---------------------------------------------------------------------- Nicole · 2022-08-24 11:58 Thank you so much! This was that easy and it works. I never would have found this by myself. I am a HUGE step further, even several steps. My navigator world has turned from gray to colored... for my next questions I have to test a while. Here a warning for everybody who reads this one day: In my case the trouble is easy to see, but this need not be the case in your case. If you have a variety of SQL-queries which fill the TDBSomeGridOrSo, you will have to care, that the navigator is in sync with them. It is possible to write wrong data to the db in this way: You think, you change value a of query a, but in fact you write value b of the next query b over a. My solution is, to set the navigator to invisible, if not all the fields are part of the query. Thank you so much, rvk and Tony! ---------------------------------------------------------------------- egsuh · 2022-08-25 00:51 QuoteIf you have a variety of SQL-queries which fill the TDBSomeGridOrSo, you will have to care, that the navigator is in sync with them. It is possible to write wrong data to the db in this way: You think, you change value a of query a, but in fact you write value b of the next query b over a. To add some more comments, TDBGrid, etc. are just for visual display. They are not really important. The content is in TDataSet descendants (TIBDataSet, in this case).  It is important to grasp the concept of TDataSet, and its relation to SQL database (Firebird, MS SQL, Oracle, etc.). TDataSet descendants --- TSQLQuery, TIBQuery, TIBDataSet, etc. comprise a subset of total database. It is a separate copy of part of whole database. Whatever uses do with TDBGrid or TDBNavigator are operations on the TDataSet, not database itself. TDataset methods like TDataSet.insert, TDataSet.delete, TDataSet.prior, TDataSet.next, TDataSet.post, operate on TDataSet descendants.  In old days single tables (dBase, Paradox, etc) were connected directly to TDataSet descendants (TTable) using BDE of Delphi. Then the changes on the TDataSet were directly reflected on the physical database tables. But with SQL databases, as TDataSets are partial copy of the database, the changes on the TDataSets are not directly reflected on the physical (electronical? whatever) database. Those changes on the TDataSet must be transferred to database file --- which is done by ModifySQL, InsertSQL, DeleteSQL. ---------------------------------------------------------------------- Nicole · 2022-08-25 06:50 Your clarifications help a lot. Those "ModifySQL, InsertSQL, DeleteSQL", when and how are they triggered? They need a transaction, how does this work? ---------------------------------------------------------------------- egsuh · 2022-08-25 08:10 QuoteThose "ModifySQL, InsertSQL, DeleteSQL", when and how are they triggered? That is what TIBDataSet does. You can do that manually with TIBQuery (or other Tdataset descendants), using TIBQuery's event handlers like BeforePost, BeforeDelete, etc.  For example, when you want to delete a record, you may set beforedelete as following (just logic. Transaction2 and query2 are not current ones). 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     transaction2.starttransaction;     try        query2.SQL.Text := 'delete from tablename where ....';         query2.execSQL;         transaction2.commit;    except          transaction2.Rollback;         DataSet.Cancel;     end;     But TIBDataSet does that for you. QuoteThey need a transaction, how does this work? That's why TIBDataSet.AllowAutoActivateTransaction must be set to true. It is possible to set default action of Transaction. I control all transactions manually, so do not exactly know how to set it operate automatically, but it is possible.