ZEOS tzupdatesql lazarus / 16-Databases ---------------------------------------------------------------------- marcov · 2024-10-22 08:53 After half a decade only doing minor stuff (like adding a field) in our in house CRM (Zeos7 - postgresql 15, but this is a more conceptual question), I needed to do something a little more involved, and I failed miserably. I have a simple form with a dbgrid that shows a tzquery with a query containing a join. To make mutations, this is connected to a tzupdatesql object with insert and modify SQLs added and multiselect enabled. This is working, but the change involves some conditional parameterization, so I added some params to the tzupdatesql object, and attempted to set them in various handlers (tzupdatesql.beforeinsertsql, tzupdatesql.beforeinsertsqlstatement and even the tzquery.newrecord event) All seems to fail, and I see question marks for the parameters in the sqlmonitor. I do see the other values (a NOW for datetime and inserting a value from a sequence using currval of a sequence) Any tips ? ---------------------------------------------------------------------- Zvoni · 2024-10-22 09:05 Quote from: marcov on October 22, 2024, 10:53:18 am*snip* I have a simple form with a dbgrid that shows a tzquery with a query containing a join. *snip* Any tips ? Have you thought instead of a Query with a Join, to make a View out if it, and then use an INSTEAD OF-Trigger? https://www.postgresqltutorial.com/postgresql-triggers/postgresql- instead-of-triggers/index.html From your client-side it would still be like an INSERT/UPDATE/Whatever on a single "table" with its involved Parameters ---------------------------------------------------------------------- marcov · 2024-10-22 10:30 Zvoni: It's a fancy solution and I certainly keep it in mind, but adding two defaults to a 15 year old application shouldn't require rearchitecting everything :-) ---------------------------------------------------------------------- Zvoni · 2024-10-22 10:55 Quote from: marcov on October 22, 2024, 12:30:49 pmZvoni: It's a fancy solution and I certainly keep it in mind, but adding two defaults to a 15 year old application shouldn't require rearchitecting everything :-) True. OTOH, i have no experience whatsoever with ZEOS and/or PostGres. Nevermind, that i don't use DB-Bound Controls, so i doubt i'll be of much help. What do the SQL's resp. the code involved look like? ---------------------------------------------------------------------- rvk · 2024-10-22 11:19 Quote from: marcov on October 22, 2024, 10:53:18 amThis is working, but the change involves some conditional parameterization, so I added some params to the tzupdatesql object, and attempted to set them in various handlers (tzupdatesql.beforeinsertsql, tzupdatesql.beforeinsertsqlstatement and even the tzquery.newrecord event) All seems to fail, and I see question marks for the parameters in the sqlmonitor. I do see the other values (a NOW for datetime and inserting a value from a sequence using currval of a sequence) Any tips ? How does it 'fail'? What are the parameter values at BeforePost? (beforeinsert could be too soon depending on what is done after that) ---------------------------------------------------------------------- marcov · 2024-10-22 13:20 QuoteQuote from: rvk on October 22, 2024, 01:19:09 pmAll seems to fail, and I see question marks for the parameters in the sqlmonitor. I do see the other values (a NOW for datetime and inserting a value from a sequence using currval of a sequence) How does it 'fail'? I press the + button on the dbnavigator, and I expect to see the fields pre filled. But they are empty, and when I press the tick mark to store, the zeos sqlmonitor shows question marks on the place where the values ought to be, and the grid also shows the values empty. The new record has been made though, and the connection between the tables (using a sequence) is correct. I played some more this morning, and basically I have a few avenues: - set parameters to a query. - preload the field components (the linked tdb* fields) with the values - preload the dataset fields with the values. And then the variations increase because there is a couple of different events to do this. ---------------------------------------------------------------------- rvk · 2024-10-22 14:31 Quote from: marcov on October 22, 2024, 03:20:55 pmI press the + button on the dbnavigator, and I expect to see the fields pre filled.Setting the Parameters in BeforePost or even BeforeInsert will NOT make them appear in the fields. They will only be send to the server when doing the insert itself. So if you want default values you also need to set the field itself... or you need to do an INSERT and SELECT to get those 'default' values. Quote from: marcov on October 22, 2024, 03:20:55 pmBut they are empty, and when I press the tick mark to store, the zeos sqlmonitor shows question marks on the place where the values ought to be, and the grid also shows the values empty.How does the changed SQL look like? Did you add question mark for parameters or did you use the correct :parameter name? ---------------------------------------------------------------------- marcov · 2024-10-23 18:32 It turned out to be setting the dataset fields in the onnewrecord handler of the primary dataset. ---------------------------------------------------------------------- Sieben · 2024-10-23 18:57 OnNewRecord is indeed the right place to supply default values because unlike OnAppendRecord this will not set the Modified flag of the dataset, it will only be set if the user really enters own values - so that eg moving to another record in a DBGrid will  make that new record to just disappear whereas with values entered in OnAppendRecord it will be posted.