Thread View: borland.public.interbase.sql
13 messages
13 total messages
Started by Isaac
Thu, 22 May 2008 20:37
how I can make the stored procedure see its changes during running
Author: Isaac
Date: Thu, 22 May 2008 20:37
Date: Thu, 22 May 2008 20:37
8 lines
1085 bytes
1085 bytes
Hello, In my stored procedure there two updates, the last update sum a field changed by the first update, but because data is not committed the last update see an old value, is there another way than using a variable Thanks
Re: how I can make the stored procedure see its changes during runningContent-Type: text/plain; charset=ISO-8859-1; format=flowe...
Author: Isaac
Date: Thu, 22 May 2008 21:43
Date: Thu, 22 May 2008 21:43
1 lines
1116 bytes
1116 bytes
never mind I forget to do something !!
Re: how I can make the stored procedure see its changes during running
Author: Bill Todd [TeamB]
Date: Fri, 23 May 2008 00:31
Date: Fri, 23 May 2008 00:31
17 lines
1791 bytes
1791 bytes
Isaac wrote: > In my stored procedure there two updates, the last update sum a field > changed by the first update, but because data is not committed the > last update see an old value, If you start a transaction, update a table, then read the values in the updated rows you will see the new values. A transaction can always see uncommitted updates that it has made. if you cannot see the updated values the only possible reason is that you are not using the transaction that made the updates. This is why it is very important to explicitly start and end every transaction in code. Do not let your data access components start and end transactions automatically because you can never be sure when transactions are started and ended. -- Bill Todd (TeamB)
Re: how I can make the stored procedure see its changes during running
Author: Isaac
Date: Fri, 23 May 2008 14:09
Date: Fri, 23 May 2008 14:09
5 lines
1221 bytes
1221 bytes
Yes this explain what happened with me when I explicitly start a transaction I can see the the update, If you dont reply I would be still wondering why it happened, Many thanks Mr. Bill Todd I admire your expert level.
Re: how I can make the stored procedure see its changes during running
Author: Bill Todd [TeamB]
Date: Fri, 23 May 2008 14:18
Date: Fri, 23 May 2008 14:18
16 lines
1542 bytes
1542 bytes
Isaac wrote: > Yes this explain what happened with me when I explicitly start a > transaction I can see the the update, Which is why you should _always_ explicitly start and stop transactions in code. > > If you dont reply I would be still wondering why it happened, > Many thanks Mr. Bill Todd I admire your expert level. See http://www.dbginc.com/tech_pprs/transactions.htm -- Bill Todd (TeamB)
Re: how I can make the stored procedure see its changes during running
Author: Isaac
Date: Fri, 23 May 2008 14:25
Date: Fri, 23 May 2008 14:25
18 lines
1680 bytes
1680 bytes
Bill Todd [TeamB] wrote: > Isaac wrote: > >> Yes this explain what happened with me when I explicitly start a >> transaction I can see the the update, > > Which is why you should _always_ explicitly start and stop transactions > in code. > >> If you dont reply I would be still wondering why it happened, >> Many thanks Mr. Bill Todd I admire your expert level. > > See http://www.dbginc.com/tech_pprs/transactions.htm > Bookmarked !! great article I also read your article about understanding transactions in the codegear.com but this one has more details. Thanks alot
Re: how I can make the stored procedure see its changes during running
Author: Isaac
Date: Fri, 23 May 2008 18:45
Date: Fri, 23 May 2008 18:45
7 lines
1378 bytes
1378 bytes
sorry but I still have a problem: the stored proc that run from triggers can see the changes inside itself correctly but I run a second stored proc from my application , although I still did not commit changes or rolled it back the second stored proc can not see the first one changes , what is your opinion ? Thanks
Re: how I can make the stored procedure see its changes during running
Author: Bill Todd [TeamB]
Date: Fri, 23 May 2008 22:49
Date: Fri, 23 May 2008 22:49
6 lines
1335 bytes
1335 bytes
Any transaction can see its own changes. If you cannot see the changes you are not in the same transaction. -- Bill Todd (TeamB)
Re: how I can make the stored procedure see its changes during running
Author: Isaac
Date: Fri, 23 May 2008 23:20
Date: Fri, 23 May 2008 23:20
13 lines
1723 bytes
1723 bytes
Bill Todd [TeamB] wrote: > Any transaction can see its own changes. If you cannot see the changes > you are not in the same transaction. > I think this mean procedures called from triggers run in a transaction different from the ones that run from TIBStoredProc, right ? I really made sure to set auto start transaction to false in all data sets also to make sure I used commitretaining before starting new transaction this way any "unknown" started transaction would end but still cant see latest changes. Thanks
Re: how I can make the stored procedure see its changes during running
Author: Isaac
Date: Fri, 23 May 2008 23:42
Date: Fri, 23 May 2008 23:42
9 lines
1536 bytes
1536 bytes
ok after many test I found that if cached updates is enabled I cant get latest values until the database is updated but now I have to apply updates first then run the second stored proc so it is able to read latest values - but I was hoping to apply updates only after all updates is done so if something goes wrong I can roll back every thing, what do you think ?. Thanks
Re: how I can make the stored procedure see its changes during running
Author: Bill Todd [TeamB]
Date: Sat, 24 May 2008 02:50
Date: Sat, 24 May 2008 02:50
14 lines
1761 bytes
1761 bytes
Isaac wrote: > I think this mean procedures called from triggers run in a > transaction different from the ones that run from TIBStoredProc, > right ? Wrong. Any changes made by a stored procedure that is called from a trigger will be within the transaction that caused the trigger to fire. When a trigger calls a stored procedure it is impossible for the trigger and stored procedure to execute in different transactions. -- Bill Todd (TeamB)
Re: how I can make the stored procedure see its changes during running
Author: Bill Todd [TeamB]
Date: Sat, 24 May 2008 02:52
Date: Sat, 24 May 2008 02:52
4 lines
1406 bytes
1406 bytes
Don't use cached updates. See http://dn.codegear.com/article/20567 -- Bill Todd (TeamB)
Re: how I can make the stored procedure see its changes during running
Author: Isaac
Date: Sat, 24 May 2008 10:57
Date: Sat, 24 May 2008 10:57
14 lines
1739 bytes
1739 bytes
Bill Todd [TeamB] wrote: > Isaac wrote: > >> I think this mean procedures called from triggers run in a >> transaction different from the ones that run from TIBStoredProc, >> right ? > > Wrong. Any changes made by a stored procedure that is called from a > trigger will be within the transaction that caused the trigger to fire. > When a trigger calls a stored procedure it is impossible for the > trigger and stored procedure to execute in different transactions. > > Many thanks I understand now
Thread Navigation
This is a paginated view of messages in the thread with full content displayed inline.
Messages are displayed in chronological order, with the original post highlighted in green.
Use pagination controls to navigate through all messages in large threads.
Back to All Threads