[solved]selectable stored procedure doesn't get updated !
lazarus / 16-Databases

----------------------------------------------------------------------
kito · 2020-08-14 14:33

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
****begin 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 = :operidHinto :****


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 .

----------------------------------------------------------------------
Thaddy · 2020-08-14 15:21

A stored procedure also needs a transaction and a commit: it is
database code, NOT Pascal code.

----------------------------------------------------------------------
kito · 2020-08-14 15:50

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

----------------------------------------------------------------------
egsuh · 2020-08-18 09:35

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 · 2020-08-18 11:06

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  !