Recent

Author Topic: CALCULATED FIELD  (Read 1613 times)

Medhome

  • New Member
  • *
  • Posts: 20
CALCULATED FIELD
« on: August 14, 2022, 07:50:06 pm »
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

  • Hero Member
  • *****
  • Posts: 970
Re: CALCULATED FIELD
« Reply #1 on: August 17, 2022, 01:47:19 pm »
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

  • Hero Member
  • *****
  • Posts: 6646
Re: CALCULATED FIELD
« Reply #2 on: August 17, 2022, 02:24:04 pm »
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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: CALCULATED FIELD
« Reply #3 on: August 17, 2022, 02:25:38 pm »
I 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][+][-]
  1. procedure TDataSet.RefreshInternalCalcFields(Buffer: TRecordBuffer);
  2. begin
  3.   //!! To be implemented
  4. end;

dje

  • Full Member
  • ***
  • Posts: 134
Re: CALCULATED FIELD
« Reply #4 on: August 17, 2022, 02:33:10 pm »
« Last Edit: August 17, 2022, 02:52:37 pm by derek.john.evans »

MarkMLl

  • Hero Member
  • *****
  • Posts: 6646
Re: CALCULATED FIELD
« Reply #5 on: August 17, 2022, 02:40:04 pm »
Looks 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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: CALCULATED FIELD
« Reply #6 on: August 17, 2022, 02:42:50 pm »
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.
Isn't that what TDataSet.RefreshInternalCalcFields is supposed to do?
In Delphi it seems to be implemented.
In FPC not.

MarkMLl

  • Hero Member
  • *****
  • Posts: 6646
Re: CALCULATED FIELD
« Reply #7 on: August 17, 2022, 03:49:12 pm »
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
« Last Edit: August 17, 2022, 03:56:02 pm by MarkMLl »
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: CALCULATED FIELD
« Reply #8 on: August 17, 2022, 04:18:39 pm »
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
Quote
RefreshSQL 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

  • Hero Member
  • *****
  • Posts: 6646
Re: CALCULATED FIELD
« Reply #9 on: August 17, 2022, 09:03:28 pm »
Using 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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: CALCULATED FIELD
« Reply #10 on: August 21, 2022, 04:13:50 pm »
I 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

  • Hero Member
  • *****
  • Posts: 6056
Re: CALCULATED FIELD
« Reply #11 on: August 21, 2022, 06:37:15 pm »
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.
Unless you have multiple COMPUTED fields in which case "returning" doesn't work  :D

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

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: CALCULATED FIELD
« Reply #12 on: August 22, 2022, 12:01:11 am »
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.
Unless you have multiple COMPUTED fields in which case "returning" doesn't work  :D
Why? Update ... Returning can return multiple columns - as can Insert returning.
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)
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

  • Hero Member
  • *****
  • Posts: 6056
Re: CALCULATED FIELD
« Reply #13 on: August 22, 2022, 10:31:16 am »
Unless you have multiple COMPUTED fields in which case "returning" doesn't work  :D
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)

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)
TIBCustomDataSet.InternalPostRecord contains the logic for this.
Good to know.
« Last Edit: August 22, 2022, 10:33:40 am by rvk »

 

TinyPortal © 2005-2018