TIBTable for beginners lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2023-03-08 17:00 My Flamerobin allows me to do a select statement of my choice. If I want to edit it, I just do it and the table values are changed. My software shall do something similar without FlameRobin. I studied the IBX manual and found interesting elements about TIBTable. I tried to do as described. I added a TIBTable as TDataSource linked it to a TIBDynamicGrid and the function is exactly what I want. The very table has 4 rows, this is nice to work with. up to here: Worked fine. In German we say: If a donkey feels too well, he will go to dance on ice. So next: not-a-select * from tbMyTable, -  but a query with where. Not a complex 'where', but a where. I ended up, that all my rows were overwritten by the very one I had edited. Not too happy about, because the database was shot by this action. I would prefer to work with the TDBNavigatorBar (or similar). But I have no idea, what happens in the background / which update- query is generated where. How to make sure, that only the values are changed, I see in my TDynamicGrid as changed even if I use a "where"-condition? ---------------------------------------------------------------------- Zvoni · 2023-03-08 17:13 Show your query that shoots all records, and show expected result ---------------------------------------------------------------------- Nicole · 2023-03-08 19:27 There are queries for all purposes in this systematic: SelectSQL, UpdateSQL .... modify, refresh.. My select query itself will not update anything. It is just select * from ... where... WHAT did the update, - this I cannot post, - because this is my question. I have no idea, how the Update-Queries are generated. This is my question as well. The only answer I know up to now: "wrong" in my case, ---------------------------------------------------------------------- korba812 · 2023-03-08 19:47 the "WHERE" condition should be simple for update query. In most cases, this is simply the record's primary key: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i UPDATE MYTABLE SET FIELD1 = :FIELD1 ...WHERE ID = :ID ---------------------------------------------------------------------- Nicole · 2023-03-09 09:38 Thank you for the answer. The question is not about SQL-Syntax, but about: How does IBX create its various SQL properties together with the DBNavigation bar? Yes, the above statement would work, if it would be "there", - where? This is the next part of my question: Where and when do I have to key in SQL in which property? What is created automatically? ---------------------------------------------------------------------- korba812 · 2023-03-09 09:49 If I remember correctly update SQL is created automatically for TIBTable. Use TIBDataSet if you want to use your own SQL queries for update, insert, modify and refresh. ---------------------------------------------------------------------- Nicole · 2023-03-09 09:51 Sure, yes. And what about the overwritten rows? ---------------------------------------------------------------------- korba812 · 2023-03-09 09:59 This may be the result of an invalid sql query for update. Can you show what query you are using for update? ---------------------------------------------------------------------- Zvoni · 2023-03-09 11:26 Quote from: korba812 on March 09, 2023, 10:59:43 amThis may be the result of an invalid sql query for update. Can you show what query you are using for update? What i asked for in my first reply....... ---------------------------------------------------------------------- Nicole · 2023-03-09 11:57 We are coming together very slowly, but we do. This query - where is it to find? And not the query at design time, but the query which is built at runtime by the components. I am not a beginner in SQL and queries, but in IBX and its properties and methods. You have to know, how the components IBX and DBNavigator work internally to answer my question. How do they?! I suspect, that it may have something to do with the fact, that the damaged table does not have a primary key. May even be, IBX has a bug here. ---------------------------------------------------------------------- rvk · 2023-03-09 12:25 Uh, just one question @Nicole. How many records do your tables have? If it's a lot, then FORGET about TIBTable. It has very poor performance and you loose the flexibility of SQL statements. What was wrong with a simple TDBGrid? In your openingspost you mention FlameRobin. I can assure you FlameRobin doesn't work with a TIBTable. If you look, you see that it creates a SELECT ALLFIELDS FROM TABLENAME in a Memo-component. So that memo component contains the SQL statement. You can edit it to your liking. After that you click run-button and you get the result in a normal TDBGrid. If not all records are visible, they are also not retrieved. If you want to retrieve all records you need to do "fetch all" from the menu. (This is equivalent to a TIBQuery.Last) So, if you have a table with upto a hundred records and don't need to use JOIN etc. you can use TIBTable. But for more complex report/list-generation don't use TIBTable. I have a list-generator in my program. Users can select a table and a simple SELECT * FROM appears. They can also select more complex SQL-statements with a range of different use-cases. And they can also enter their own SELECT-SQL statement to create their own list (often with help from me). -- Then about the writing of your INSERT and UPDATE statements (and your overwrite problem). You mostly need to do this yourself. With TIBQuery you can generate them at design time but you need to check them. An UPDATE and DELETE should always be done with a WHERE ID=:ID. Otherwise you UPDATE or DELETE records you don't want. Those are simply just the SQL rules. If you post the SELECT we can show you what the UPDATE and DELETE should be. My suggestion would be to just stick with TIBDatabase + TIBTransaction -> TIBQuery -> TDatasource -> TDBGrid with a TDBNavigator. And if you want to edit, you can use the TDBGrid directory or add some TDBEdit fields (but I usually do that in a separate form). When selecting a table (of function in your program), set the TIBQuery.SQL correctly. Also set the UPDATE and DELETE in the UpdateObject property (a TIBUpdateSQL object) if the edit fields are on the same form. Make sure the UPDATE and DELETE are correctly set. You can rightclick the TIBUpdateSQL component and choose UpdateSQL Editor to get a helper form to create your UPDATE and DELETE SQL statements. AND YES, Always create a primary key on all your tables. That's just common sense in database design. ---------------------------------------------------------------------- korba812 · 2023-03-09 12:29 Quote from: Nicole on March 09, 2023, 12:57:48 pmThis query - where is it to find? And not the query at design time, but the query which is built at runtime by the components. You can't read UpdateSQL in TIBTable because it's not public. Just use TIBDataSet and enter your own queries for update, insert, delete and refresh. Quote from: Nicole on March 09, 2023, 12:57:48 pmI am not a beginner in SQL and queries, but in IBX and its properties and methods. You have to know, how the components IBX and DBNavigator work internally to answer my question. How do they?! Just like other database components - TDatSet -> TDataSource -> TDataLink or TFieldDataLink. Rather not directly related to IBX but to the DataSet architecture. Quote from: Nicole on March 09, 2023, 12:57:48 pmI suspect, that it may have something to do with the fact, that the damaged table does not have a primary key. May even be, IBX has a bug here. Your table should have a primary key so that each record can be identified (this can be a multi-field key) ---------------------------------------------------------------------- Nicole · 2023-03-09 15:18 Thank you so much for the answers. Now I begin to understand. The task is more complex than I wrote it here. There are a handful of tables, not only one. I try to sum up table-group by table-group 1) I have one frame-interface for a bundle of tables. For some I need the date, for others not. The user can choose various radio-groups of criteria he wants to change. I just modify the display of the interface by setting components visible and not and  - - now it comes - changing the selectSQL-text taken from the user's radio-group- selections. This makes, that the displayed selection is fine, but the update-attempts result into an error, because the table does not fit. One table has a certain field, the next not. SelectSQL is adjustet, ModifySQL not. For this above group 1 I use a TDataset and IBDatasource So have I understood it correctly, that I have to - change not only the SelectSQL, but the ModifySQL as well? Anything else? - best way to do this, is to use the build-in generation editor and change tables for every "wanted" one? - these SQL-textes I assign to ModifySQL, at the click the user decides to edit a certain table (added where *)? 2) The desaster-table, overwritten It has not many rows, but only - 4. Very important figures. And no primary key. So I assume, that the value was valid for every single row and killed all of them. In the meanwhile I just split this very table from the datacomponents, gave it an own panel and an own datasource and display it by TIBTable and TIBDynamicGrid. This works fine. As there are only 4 rows, it is ok to see them all at once. Why I like TIBDynamicGrid that much: It allows sorting. Even 4 rows sometimes are nicely to be sorted. Is it bad style to work without primary key? It would be easy to make one field a primary key. I did not even set anything to unique. May be this is bad style as well. The table looks that small that I thought I - LOL - I would not make a mistake here. What do you recommend? ---------------------------------------------------------------------- rvk · 2023-03-09 16:04 Quote from: Nicole on March 09, 2023, 04:18:46 pm1) This makes, that the displayed selection is fine, but the update-attempts result into an error, because the table does not fit. One table has a certain field, the next not. SelectSQL is adjustet, ModifySQL not.You shouldn't define the columns in a TDBGrid. They will be filled in automatically (if empty). So any SQL you set, the TDBGrid will follow automatically. You might want to loop over the columns, after opening the TIBQuery, to set any special requirements (like color, width etc) but besides that, don't define them beforehand. Quote from: Nicole on March 09, 2023, 04:18:46 pmFor this above group 1 I use a TDataset and IBDatasourceYou probably mean TDataSource and TIBDataset  Yes, if you use TIBDataset (or TIBQuery + TIBUpdateSQL combo) you need to set the UPDATE and DELETE yourself. You can do this in designtime (right click and choose DataSet Editor). But if you want to change the SQL at runtime, you also need to set the ModifySQL, InsertSQL and DeleteSQL. But you could use the DataSet editor in designtime to find out how those should be. But empty them before running your program as you are going to fill those dynamically in runtime. Quote from: Nicole on March 09, 2023, 04:18:46 pm2) The desaster- table, overwritten It has not many rows, but only - 4. Very important figures. And no primary key. So I assume, that the value was valid for every single row and killed all of them.Yes, that's why you have a primary key. You could do it with WHERE FIELD1=:FIELD1 and FIELD2=:FIELD2 etc. But the chance always exists that you catch multiple records. That's why it's important to ALWAYS use a primary key (ID, Linknummer, IDNR or something else but make it consistent throughout your database design, so same ID-name for all tables). That's also they way you connect tables together. TABLE CLIENT ID BIGINT TABLE ORDERS ID BIGINT ORDERNR BIGINT CLIENT_ID (foreign key linking to CLIENT.ID) etc. Quote from: Nicole on March 09, 2023, 04:18:46 pmIn the meanwhile I just split this very table from the datacomponents, gave it an own panel and an own datasource and display it by TIBTable and TIBDynamicGrid. This works fine. As there are only 4 rows, it is ok to see them all at once.With just 4 rows it's fine. But for more rows it's best to stick to TDBGrid (I don't know TIBDynamicGrid but if it's a TDBGrid descendant it should be fine). Quote from: Nicole on March 09, 2023, 04:18:46 pmIs it bad style to work without primary key?Yes, defenitly. You have experienced this yourself (by loosing important records). If those records had an ID as primary key, the UPDATE and DELETE would have included WHERE ID=:ID and those records would have been fine. Quote from: Nicole on March 09, 2023, 04:18:46 pmIt would be easy to make one field a primary key. I did not even set anything to unique. May be this is bad style as well.A primary key is always unique. Quote from: Nicole on March 09, 2023, 04:18:46 pmThe table looks that small that I thought I - LOL - I would not make a mistake here. It's also best for small tables. What kind of data is in that table? Are those records not used anywhere else in your program, or linked to other tables? ---------------------------------------------------------------------- Nicole · 2023-03-09 16:51 Quote You shouldn't define the columns in a TDBGrid. This is not done. The user may click e.g. 'Table "Eventlist" ' and "start from date "1.1.2023" ' And I generate the query which displays this choice in the DBGrid. The trick is do be done if the chooses one of those choices e.g.: e.g. 'Table "Newslist" ' and "start from date "1.2.2023" ' (different table!) Then the Select-statement has to be changed. This is done nicely, the DBGrid is rewritten. Never the less, the user-changes do only work, if the first table is chosen. The first table is the one, which helped me to generate the SQL-statements at desing-time. As I adjust the select-statement at runtime to point at the different table and the other SQL statements not,   - they remain pointing at the first table and do nothing more as they have learnt at design-time. This is not very surprising. The point is, - how to change this best? Another way would be, not to change the SQL-statements but the Dataset. (forgive me, I keep mixing up datasource, dataset nothing helps) Quote Yes, if you use TIBDataset (or TIBQuery + TIBUpdateSQL combo) you need to set the UPDATE and DELETE yourself. This "combo" I found in the manual and was excited about. Not sure, if I need a DELETE. Let me write the UPDATE first. I plan to to this: Where the "select"-statement is adjusted for the user-needs, I would adjust the UPDATE as well. May I just copy the suggested SQL-code from the editor for the appropriate table? Or will I have to add the user choice with the where-clause  as well? And is it correct, only to change the ModifySQLß? Quote But the chance always exists that you catch multiple records. OMG! This is the worst case, that my data become wrong. Will the definition of a primary key help against this? I am not good ad database connections and so everything is in a DB- Modul, where there is a transaction1, which is allowed to autocommit. So I do nothing with databases and transactions any more. The datasensitive component just points as the db-modul and I do not care any more. Quote That's also they way you connect tables together. TABLE CLIENT ID BIGINT TABLE ORDERS ID BIGINT ORDERNR BIGINT CLIENT_ID (foreign key linking to CLIENT.ID) Not sure, if this is done and replaces your text above. I do not understand it really. To make a primary key, I usually go to FlameRobin and click cluelessly around for half an hour until there is one by good luck. Quote I don't know TIBDynamicGrid "Our" Tony wrote it! Give it a try. Quote you also need to set the ModifySQL, InsertSQL and DeleteSQL. ModifySQL and Update are the same? Not sure, if I will provide an insert and a delete as well. Do you think, it is the better performance to change all of these or to have several IBDataSets which I let point at their table at designtime? So one idea is table1 with ibDataSet1 / table2 with ibDataset2 / table 3 with ibDataset 3 the second idea is IBdataset1 with Queryies for table 1 / IBdataset1 with Queryies for table 2 / IBdataset1 with Queryies for table 3 Quote Are those records not used anywhere else in your program, or linked to other tables? Everything was fine by good luck and there shall not be any problem. I am long enough in programming, that I know, how quickly problems occur, that "shall not" occur. I will go ahead and click half an hour in FlameRobin to make one field primary key. ---------------------------------------------------------------------- rvk · 2023-03-09 17:18 Quote from: Nicole on March 09, 2023, 05:51:28 pmThe trick is do be done if the chooses one of those choices e.g.: e.g. 'Table "Newslist" ' and "start from date "1.2.2023" ' (different table!) Then the Select-statement has to be changed. This is done nicely, the DBGrid is rewritten. Never the less, the user-changes do only work, if the first table is chosen.You also need to change the ModifySQL, InsertSQL and the DeleteSQL statements (in code manually). So you need to change all 4 (or 5 if you include a refreshsql) statements. If the queries are simple (just select) you can do that with a function. I have a function CreateUpdateQueries() which I feed a tIbQuery, a tablename and a key). The function creates the modifysql, insertsql and deletesql for me. It does this by opening up a separate tIbQuery with open command and looping though all the fields and building the correct statements. But you can set them hard in code too yourself (it's just a bit more work). Quote from: Nicole on March 09, 2023, 05:51:28 pmQuote Yes, if you use TIBDataset (or TIBQuery + TIBUpdateSQL combo) you need to set the UPDATE and DELETE yourself. This "combo" I found in the manual and was excited about.This combo of TIBQuery + TIBUpdateSQL is essentially the same as TIBDataset (which has the ModifySQL, InsertSQL and DeleteSQL already in it). I'm not sure about other differences. Quote from: Nicole on March 09, 2023, 05:51:28 pmWhere the "select"-statement is adjusted for the user-needs, I would adjust the UPDATE as well. May I just copy the suggested SQL-code from the editor for the appropriate table? Or will I have to add the user choice with the where-clause  as well? And is it correct, only to change the ModifySQLß?Yes, that's how you do it. And you need to change ModifySQL, InsertSQL and DeleteSQL as well. Quote from: Nicole on March 09, 2023, 05:51:28 pmQuote But the chance always exists that you catch multiple records. OMG! This is the worst case, that my data become wrong. Will the definition of a primary key help against this? Yes, the primary key in combination with the correctly written ModifySQL and DeleteSQL. Because if you supply the primary key in the Dataset editor, it will automatically suggest you add the WHERE ID=:ID or WHERE ID=:OLD_ID to your SQL (which prevents any record but the one with that ID to be changed). Quote from: Nicole on March 09, 2023, 05:51:28 pmNot sure, if this is done and replaces your text above. I do not understand it really. To make a primary key, I usually go to FlameRobin and click cluelessly around for half an hour until there is one by good luck.You can. But it should be part of the database DDL eventually. (The database DDL is the definition of your tables: https://en.wikipedia.org/wiki/Data_definition_language) So you would have something like this: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE CUSTOMERS (  ID   BIGINT NOT NULL PRIMARY KEY,  CODE VARCHAR(20), /* short search code */  NAME VARCHAR(50),  ...)A primary key would also need a generator (or sequence in newer versions). Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE SEQUENCE GEN_CUSTOMERS;and a trigger to automatically increase a generator when 0 or NULL is given: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TRIGGER TR_CUSTOMERS FOR CUSTOMERS ACTIVE BEFORE INSERT POSITION 0 ASBEGIN  IF ((NEW.ID IS NULL) OR (NEW.ID = 0)) THEN      NEW.ID = GEN_ID(GEN_CUSTOMERS, 1);END Quote from: Nicole on March 09, 2023, 05:51:28 pmQuote you also need to set the ModifySQL, InsertSQL and DeleteSQL. ModifySQL and Update are the same?Yes. Quote from: Nicole on March 09, 2023, 05:51:28 pmDo you think, it is the better performance to change all of these or to have several IBDataSets which I let point at their table at designtime?That depends on your preference. If the number of tables is set and the options for the user limited, you can use multiple TIBDataset. If the number (and complexity) becomes greater, you might want to use one, and change the SQL's dynamically. Quote from: Nicole on March 09, 2023, 05:51:28 pmQuote Are those records not used anywhere else in your program, or linked to other tables? Everything was fine by good luck and there shall not be any problem. I am long enough in programming, that I know, how quickly problems occur, that "shall not" occur. I will go ahead and click half an hour in FlameRobin to make one field primary key. Do you not connect the tables together? Are all the tables independent? They have nothing to do with each other? If not, and you have customer, orders, countries etc, then you need to link them together via a foreign key. The upside of that is that you can't delete a customer if it still has orders. Or you can't delete a country if that country is used with a customer. You can even create relationships that if you delete a order, that all underlying order_detail records are deleted automatically. Otherwise, when deleting an order, those records would remain orphaned in their table. I hope you understand everything above a little bit ---------------------------------------------------------------------- rvk · 2023-03-09 17:50 O, BTW, on what version of Firebird are you currently? If you use Firebird 4 or higher you could use GENERATED ALWAYS AS IDENTITY. In that case you don't need to use GENERATOR and TRIGGER. Much easier (but not available on Firebird 3 or lower). Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE T1 (   ID INTEGER  GENERATED ALWAYS AS IDENTITY PRIMARY KEY) https://stackoverflow.com/a/34555507/1037511 But if you also have Firebird 2.5 or 3 I would stick to GENERATOR and TRIGGER. ---------------------------------------------------------------------- egsuh · 2023-03-10 04:45 Check whether 1) primary key is defined for the table or other unique key and 2) TIBTable.DefaultIndex is set to true or TIbTable.IndexName is defined or find fields that may compose unique index, and enter them in the TibTable.IndexfieldNames in the format of f1;f2;f3. And then try again.