Home » Developer & Programmer » Forms » getting items value after commit (forms builder 6)
getting items value after commit [message #415135] Sat, 25 July 2009 03:48 Go to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
hi, i have a form where i have to do a commit, in which some of the values are controlled by a database trigger (status and date), and when i do the commit (commit_form;), it saves ok, but is there any way that i can automaticly display the values that the trigger controls? (kinda like a refresh of the same record after the commit, sorry for the lousy explanation my english isn't perfect xD) thanks!
Re: getting items value after commit [message #415149 is a reply to message #415135] Sat, 25 July 2009 06:34 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member

Could you tell me the code of your trigger. And if you could also confirm whether that block is a database block or not.

Ali
Re: getting items value after commit [message #415172 is a reply to message #415135] Sat, 25 July 2009 14:49 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
hi, thanks for the reply, yes, it is a database block, the trigger is not on the form tho, it's on the database (inserted through the oracle desing editor, on a pl/sql insertion trigger), and the block id is controlled by a sequence, so when i commit it does those things internally, and the form does not reflect the changes inmediatly, so my guess is i have to write a post-insert trigger on forms or something like that? i've tried that but no luck (and i have no trigger for refreshing the info on screen because i don't know which command i should use >_<)
Re: getting items value after commit [message #415173 is a reply to message #415172] Sat, 25 July 2009 15:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i have a form where i have to do a commit
OK then, this is handled by KEY-COMMIT trigger. Therefore, simply execute a query after committing. Something like this:
-- KEY-COMMIT trigger:

commit;
execute_query;

Eventually, you might need to set querying criteria. If so, check how to set it by using SET_BLOCK_PROPERTY built-in, its DEFAULT_WHERE (or ONETIME_WHERE) clause.
Re: getting items value after commit [message #415179 is a reply to message #415173] Sat, 25 July 2009 16:11 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
yeah, i already did the commit and the execute query (they are on the "Guardar" button trigger), the problem is that i have to show the same block i just saved (and if i do the execute query it just shows me everything, and since i have just 1 field to show.. well, you get the idea), with the records that got updated on the database, in the screenshot you can see what i mean, because the "estado" record poplist is in blank (but when you save, the database trigger sets it as "Iniciado", that's why i need to refresh, to reflect the changes made by the database trigger)

that is actually what i need, something like doing a refresh after commit, showing the exact same block i just saved
  • Attachment: screen.JPG
    (Size: 59.65KB, Downloaded 114 times)

[Updated on: Sat, 25 July 2009 16:12]

Report message to a moderator

Re: getting items value after commit [message #415180 is a reply to message #415179] Sat, 25 July 2009 16:30 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is why I have said that you might need to set DEFAULT_WHERE (or ONETIME_WHERE) block property in order to fetch exactly the same data as you had on the screen during COMMIT.
Re: getting items value after commit [message #415181 is a reply to message #415180] Sat, 25 July 2009 17:07 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
could you provide me with an example of that? never used that function, sorry for all the trouble, thanks
Re: getting items value after commit [message #415199 is a reply to message #415181] Sun, 26 July 2009 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Open Forms Help and search for SET_BLOCK_PROPERTY. It is all in there. Also, search this forum for the same built-in; there've been examples here as well.
Re: getting items value after commit [message #415246 is a reply to message #415135] Sun, 26 July 2009 14:40 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
k, thanks a lot for your help!, you saved me big time
Re: getting items value after commit [message #415264 is a reply to message #415246] Sun, 26 July 2009 23:47 Go to previous messageGo to next message
ali_sakar
Messages: 22
Registered: February 2009
Location: Mumbai
Junior Member

I got that you were talking about Database Trigger. I wanted the trigger code coz when I tried the same here I got the tables mutating error. Thats the reason I wanted to see the code. I created a form based on table TEMP and created an After Insert Trigger. I wrote an update on TEMP table in the trigger. But it did not work and gave me the error. So could you just tell me how you achieved it?

Ali.
Re: getting items value after commit [message #415323 is a reply to message #415135] Mon, 27 July 2009 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If a database trigger is modifying values set in your datablock then what you need to do is set the block property DML Returning Value to Yes.
No requerying should be necessary.
Re: getting items value after commit [message #415354 is a reply to message #415264] Mon, 27 July 2009 06:22 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
hi, actually i just used the command that Littlefoot suggested, it would be something like

commit_form;
SET_BLOCK_PROPERTY('PROYECTOS', DEFAULT_WHERE, ' WHERE nombre = ' || '''' || :proyectos.nombre || '''' );
go_block('proyectos');
execute_query(no_validate);
SET_BLOCK_PROPERTY('PROYECTOS', DEFAULT_WHERE, '' );

(i'm doing the commit with a button, so i just have it set on a when-button-pressed trigger), also that's asumming you have only one entry using the same name, else it may not return the one you want, you can do several on the same set_block_property tho, hope that helped
Re: getting items value after commit [message #415356 is a reply to message #415323] Mon, 27 July 2009 06:29 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
cookiemonster wrote on Mon, 27 July 2009 04:48
If a database trigger is modifying values set in your datablock then what you need to do is set the block property DML Returning Value to Yes.
No requerying should be necessary.


actually i asummed there should be a way to make forms update the values automaticly, even tho in this case it is inserting values, not just modifying (don't know if that makes the difference), just tried it and it didn't work (must have done something wrong, it's 8 am here and i haven't slept in like ... i forgot how much lol), i'll try it again after i get some sleep, the solution Littlefoot suggested works as well, but it's good to know several ways of doing things just in case, thanks for the help!
Re: getting items value after commit [message #415362 is a reply to message #415135] Mon, 27 July 2009 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
DML Return should work for this. It behaves the same for inserts and updates. It basically appends a RETURNING clause to the insert/update statement that the block auto-generates. Look up the syntax if you're not sure what it does.
Re: getting items value after commit [message #415437 is a reply to message #415362] Mon, 27 July 2009 19:00 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you considered having that 'default_where' as the standard 'where' clause for your block?

If you want to display the database trigger generated values then in the 'post-insert' and 'post-update' triggers retrieve those values. You COULD do an execute-query but repopulating the complete block is IMHO "over-kill".

David
Re: getting items value after commit [message #415533 is a reply to message #415362] Tue, 28 July 2009 03:30 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
cookiemonster wrote on Mon, 27 July 2009 06:50
DML Return should work for this. It behaves the same for inserts and updates. It basically appends a RETURNING clause to the insert/update statement that the block auto-generates. Look up the syntax if you're not sure what it does.


yeah, actually it works, i tried it earlier and it worked alright, must have been my loss of sleep messing with my head lol, thanks
Re: getting items value after commit [message #415539 is a reply to message #415437] Tue, 28 July 2009 03:37 Go to previous messageGo to next message
keiiferrer
Messages: 9
Registered: July 2009
Junior Member
djmartin wrote on Mon, 27 July 2009 19:00
Have you considered having that 'default_where' as the standard 'where' clause for your block?

If you want to display the database trigger generated values then in the 'post-insert' and 'post-update' triggers retrieve those values. You COULD do an execute-query but repopulating the complete block is IMHO "over-kill".

David


yup, i thought that as well, the good thing is that it only does it when it is needed (just on a new record insert, i have a condition checking that after commit), i already handed over the working project (and my teacher seemed to be ok with it, i had little time to correct that and some other errors on the project), but i'll keep it in mind when i have to do something similar again, thanks again for all the help! ^^
Re: getting items value after commit [message #415547 is a reply to message #415437] Tue, 28 July 2009 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
djmartin wrote on Tue, 28 July 2009 01:00

If you want to display the database trigger generated values then in the 'post-insert' and 'post-update' triggers retrieve those values.

Isn't that likely to lead to "record has been updated by another user" errors?
Re: getting items value after commit [message #415704 is a reply to message #415547] Tue, 28 July 2009 20:02 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Make the display items non-database. Also, populate them in the Post-Query.

David
Previous Topic: FRM-40514: Operation requires a database connection.
Next Topic: Insert data in one transaction.
Goto Forum:
  


Current Time: Sun Dec 04 14:34:06 CST 2016

Total time taken to generate the page: 0.09138 seconds