Recent

Author Topic: [solved]selectable stored procedure doesn't get updated !  (Read 564 times)

kito

  • New Member
  • *
  • Posts: 15
[solved]selectable stored procedure doesn't get updated !
« on: August 14, 2020, 04:33:08 pm »
Hello and good evening

I am working on database embeded project (lazarus 2.010 +firebired embeded 2.7)
I made a selectable stored procedure that returns a records set  from multi tables as follow :




Code: Pascal  [Select][+][-]
  1. *
  2. *
  3. *
  4. *
  5. begin
  6.  for select * from contract a inner join emp on emp.empID = a.empid inner join xbx on  a.gradid=xbx.gid  where extract (year from STD) = :yeard  and sTC ='OUI' and   extract (month from STD)<= :monthd and extract (month from EDD) >=:monthd and OPERID = :operidH
  7. into :
  8. *
  9. *
  10. *
  11. *


 
every thing is ok  when calling the stored procedure but when I made any update on any  table it doesn't update  the result of the procedure unless I restart the programe  I even tried ( close /open/refresh ) didn't help .
« Last Edit: August 18, 2020, 01:06:53 pm by kito »

Thaddy

  • Hero Member
  • *****
  • Posts: 10516
Re: selectable stored procedure doesn't get updated !
« Reply #1 on: August 14, 2020, 05:21:50 pm »
A stored procedure also needs a transaction and a commit: it is database code, NOT Pascal code.

kito

  • New Member
  • *
  • Posts: 15
Re: selectable stored procedure doesn't get updated !
« Reply #2 on: August 14, 2020, 05:50:16 pm »
Thank you Thaddy for your replay, as I said  its an selectable SP only displays  ds  the issue  that the updated made on any other table  doesn't effects on the  displayed data recalled by the SP not the opposite it always shows the old data
and I always  after any modification I successfully apply the updates and made transactions commit
 that's why  when I restart the program
And recall the PS  displayed the correct data
« Last Edit: August 14, 2020, 05:58:24 pm by kito »

egsuh

  • Sr. Member
  • ****
  • Posts: 465
Re: selectable stored procedure doesn't get updated !
« Reply #3 on: August 18, 2020, 11:35:51 am »
Please check my understanding is correct.

Step 1.   you run the stored procedure.  (select records from several underlying tables)
Step 2.   you make changes to the underlying tables.
Step 3.   you close, and reopen the stored procedure.  But this does not reflect the changes to the underlying tables done at step 2. 

If my understandings are correct, one possibility is that you may be using different transactions between (Step 1 and 3) and Step 2. Even though you are using one transaction (in Lazarus application), they are separate transactions if run on separate applications. 

Important thing is you have to commit all transactions after step 2, and then restart transaction before step 3.   

kito

  • New Member
  • *
  • Posts: 15
Re: selectable stored procedure doesn't get updated !
« Reply #4 on: August 18, 2020, 01:06:10 pm »
egsuh you made my day !
 indeed I was using two transactions ( I  would never guessed  it  ) , problem is solved and every thing act  as expectd , thank you  !

 

TinyPortal © 2005-2018