CALCULATED FIELD lazarus / 16-Databases ---------------------------------------------------------------------- Medhome · 2022-08-14 17:50 Hi All, I  Designed a FB table as below: CREATE TABLE LINES (   PK bigint NOT NULL,   NDOC bigint,   NART bigint,   CLOT varchar(10),   DATE_PER date,   QTE numeric(18,2),   Price numeric(7,2),   PPA numeric(7,2),   CONSTRAINT PK_LIGNES_0 PRIMARY KEY (PK) ); ALTER TABLE LINES ADD MNT COMPUTED BY (qte*price); I Show the  data via a dbgrid    when I update qte or price the field MNT does not !!! REGARDS ---------------------------------------------------------------------- Nicole · 2022-08-17 11:47 Not sure, how this works in Lazarus. I come from Delphi. There it would be necessary to key in the fields into the query, which supplies the data of the DBGrid. What is not in this DBGrid-Query did not shop up. In Delphi I had an Editor which allowed to edit this supplied-grid- fields from the design-time DBGrid environment. I know this, because I once searched half a day an error like yours. It was needed to have this field listed / added in this very editor. Should this be different in Lazarus, pls just ignore my posting. ---------------------------------------------------------------------- MarkMLl · 2022-08-17 12:24 I'm rather uncomfortable commenting since I'm not 100% sure of my facts. However on PostgreSQL (and, I suspect, Firebird) I think it would involve having a trigger which sent an asynchronous notification to the app that it needed to repeat the selection which underlay its displayed data. MarkMLl ---------------------------------------------------------------------- rvk · 2022-08-17 12:25 Quote from: Medhome on August 14, 2022, 07:50:06 pmI Show the  data via a dbgrid    when I update qte or price the field MNT does not !!! Did you update/refresh the dbgrid dataset after the update? If not, then that record still has old values. What happens if you commit the transaction, close the db and reopen the db? Is the MNT changed then? BTW. I think this should be handled automatically by TDataSet.RefreshInternalCalcFields but it doesn't seem to be implemented yet: 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 TDataSet.RefreshInternalCalcFields(Buffer: TRecordBuffer);begin  //!! To be implementedend; ---------------------------------------------------------------------- dje · 2022-08-17 12:33 Looks like OP found their answer over on stackoverflow. https://stackoverflow.com/questions/73354332/the-database-calculated- field-not-updated-for-example-total-qteprice-when-qte ---------------------------------------------------------------------- MarkMLl · 2022-08-17 12:40 Quote from: derek.john.evans on August 17, 2022, 02:33:10 pmLooks like OP found his answer over on stackoverflow. https://stackoverflow.com/questions/73354332/the-database-calculated- field-not-updated-for-example-total-qteprice-when-qte StackOverflow being as it is these days, I'm surprised his question wasn't deleted since he didn't actually ask anything :-) OK, the answer to what he wasn't asking was "you should re-select". But if he wants to do that automatically he should use an async notification, which has the benefit that any subscribed system will see it and know that the app should reselect and redisplay. MarkMLl ---------------------------------------------------------------------- rvk · 2022-08-17 12:42 Quote from: MarkMLl on August 17, 2022, 02:40:04 pmOK, the answer to what he wasn't asking was "you should re-select". But if he wants to do that automatically he should use an async notification, which has the benefit that any subscribed system will see it and know that the app should reselect and redisplay. Isn't that what TDataSet.RefreshInternalCalcFields is supposed to do? In Delphi it seems to be implemented. In FPC not. ---------------------------------------------------------------------- MarkMLl · 2022-08-17 13:49 How does it know that the table (etc.) being presented needs to be refreshed? It either has to poll the table, listen for an async notification from the server, rely on local information if the same app instance updated the operand, or have a private notification mechanism between cooperating apps. MarkMLl ---------------------------------------------------------------------- rvk · 2022-08-17 14:18 Yes, the updated record would need to be reread/re-selected/polled from the database to know the actual values. This is exactly what the TSQLQuery.RefreshSQL is meant for: https://www.freepascal.org/docs- html/fcl/sqldb/tsqlquery.refreshsql.html QuoteRefreshSQL can be used to specify a SQL statement that is executed after an UPDATE or INSERT operation. The query will be executed, and the values of all fields in the result set will be copied to the dataset. This SQL statement is only executed during the ApplyUpdates operation, not during the Post call itself. It should only retrieve the updated record. Using a async notification is also possible but is highly depended on the database and would be somewhat harder to implement. ---------------------------------------------------------------------- MarkMLl · 2022-08-17 19:03 Quote from: rvk on August 17, 2022, 04:18:39 pmUsing a async notification is also possible but is highly depended on the database and would be somewhat harder to implement. My recollection is that there are components for Firebird (as per OP's question) and Postgres. However my involvement predates those, I had to do it the comparatively hard way... which wasn't particularly difficult. MarkMLl ---------------------------------------------------------------------- tonyw · 2022-08-21 14:13 Quote from: Medhome on August 14, 2022, 07:50:06 pmI Show the  data via a dbgrid    when I update qte or price the field MNT does not !!! Not sure which database access components that you are using. If you are using IBX then a row containing a computed column should be automatically refreshed after you post the row. If you let IBX generate/suggest the Update SQL then it will normally add a Computed By column to the returning clause of an Update statement e..g Update MyTable Set A= B where key = C returning MNT; That way IBX avoids having to separately refresh the row and the update statement automatically returns the updated computed by column value. ---------------------------------------------------------------------- rvk · 2022-08-21 16:37 Quote from: tonyw on August 21, 2022, 04:13:50 pmIf you let IBX generate/suggest the Update SQL then it will normally add a Computed By column to the returning clause of an Update statement e..g Update MyTable Set A= B where key = C returning MNT; That way IBX avoids having to separately refresh the row and the update statement automatically returns the updated computed by column value. Unless you have multiple COMPUTED fields in which case "returning" doesn't work  Does IBX call RefreshSQL automatically in this case or does it need to be called manually? (If IBX detects there are multiple COMPUTED fields it could be called automatically upon Post) ---------------------------------------------------------------------- tonyw · 2022-08-21 22:01 Quote from: rvk on August 21, 2022, 06:37:15 pmQuote from: tonyw on August 21, 2022, 04:13:50 pmIf you let IBX generate/suggest the Update SQL then it will normally add a Computed By column to the returning clause of an Update statement e..g Update MyTable Set A= B where key = C returning MNT; That way IBX avoids having to separately refresh the row and the update statement automatically returns the updated computed by column value. Unless you have multiple COMPUTED fields in which case "returning" doesn't work  Why? Update ... Returning can return multiple columns - as can Insert returning. Quote from: rvk on August 21, 2022, 06:37:15 pmDoes IBX call RefreshSQL automatically in this case or does it need to be called manually? (If IBX detects there are multiple COMPUTED fields it could be called automatically upon Post) TIBCustomDataSet.InternalPostRecord contains the logic for this. Just before posting a record, each computed by and each Blob and Array field is flagged as "fdRefreshRequired". Blobs and arrays need refreshing as the ISC_QUAD value can change if they are updated. After the query has been executed, the returned column values, if any, are used to update the current row values and the fdRefreshRequired flag set to false for each column for which a value is returned. If there are any fields left with a fdRefreshRequired flag set then the row is refreshed using the RefreshSQL. A row refresh is performed anyway if the ForcedRefresh property is true. ---------------------------------------------------------------------- rvk · 2022-08-22 08:31 Quote from: tonyw on August 22, 2022, 12:01:11 amQuote from: rvk on August 21, 2022, 06:37:15 pmUnless you have multiple COMPUTED fields in which case "returning" doesn't work  Why? Update ... Returning can return multiple columns - as can Insert returning.Aaaargh. I missed that one. I thought RETURNING was for one field but you can indeed return multiple values. I guess I missed it because my program was developed with Firebird 1.5 and 2.0 and it was introduced in 2.0/2.1. (and I'm not even sure IBX for Delphi in 10.2 supports it) So I couldn't rely on RETURNING working with the older database of my customers. (unfortunately the same goes for some other new features introduced after 2.0) Quote from: tonyw on August 22, 2022, 12:01:11 amQuote from: rvk on August 21, 2022, 06:37:15 pmDoes IBX call RefreshSQL automatically in this case or does it need to be called manually? (If IBX detects there are multiple COMPUTED fields it could be called automatically upon Post) TIBCustomDataSet.InternalPostRecord contains the logic for this. Good to know.