access to colum while is updating

Question

Post reply

access to colum while is updating

lakiri333

Grasshopper

Points: 19
More actions
March 7, 2019 at 12:58 am

#381629

hi everyone
i have a table in my DB which has column named position ,so i have a loop that it updating this column every 10s .now i want to ask if there is a possibility to access to this comlumn data and get a new value every 10s :(when i try to do that with select query ,it retruned to me always the same value which is the old value befort starting a loop).
sorry for my bad english,please help me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Without seeing the code you're using, we'd only be guessing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 2

lakiri333 - Thursday, March 7, 2019 12:58 AM
hi everyone
i have a table in my DB which has column named position ,so i have a loop that it updating this column every 10s .now i want to ask if there is a possibility to access to this comlumn data and get a new value every 10s :(when i try to do that with select query ,it retruned to me always the same value which is the old value befort starting a loop).
sorry for my bad english,please help me.

Need a few more details to be specific, but I'm guessing your loop is not committing the transaction that is applying the updates. So while the loop is running, the transaction is open and, if your isolation level is read committed, you will only get the old value because the new value has not been committed to the database.

If you can explain why you're trying to do this, along with some code, table schema and example data, you might get some more insight on this.

lakiri333 Grasshopper Points: 19 More actions · Answer 3

thnx for your response
actualy what I am traying to do is simulate a person walking
here is my code

#
DECLARE
counter INTEGER := 1 ;

BEGIN
(just example for updating position 5 times)

while counter<5

( this to get the next position along the road (chemin column))

LOOP
update person as t1 set position_geom=
( SELECT ST_Line_Interpolate_Point (chemin,

(counter *(select ST_Length(chemin:: geography) ))) from person as t2,
where t1.id=t2.id );
WAITFOR DELAY '00:00:10';

PERFORM dblink('connex1','COMMIT;');
                                 counter := counter + 1 ;



END LOOP ;
                                 PERFORM dblink_disconnect('connex1');
    END ;
#

the column position is updating every 10s. now when i try to get this new value (with select query ) befor the loop is finished I get always the same value which is the old value befor the loop start.
I guess the loop lock this column ,so I can't get the new value untill the loop is finish .
any idea to resolve that or other issue to simulate man walking .
thanks again

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 4

Decide when you want the person to start walking, calculate the difference between start time and current time, apply the distance function for that many time increments. it's not real, you don't actually have to update the record in realtime, you could record an ending time right when they start, because you'll know how long it took them to go that far. (assuming we're talking about no obstacles, no change in direction, "as the crow flies" kind of math)

basically, treat the whole thing like it already happened, and your results are just saying "where were they at any given point in time"

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 5

HandyD - Wednesday, March 13, 2019 7:48 PM
lakiri333 - Thursday, March 7, 2019 12:58 AM
hi everyone
i have a table in my DB which has column named position ,so i have a loop that it updating this column every 10s .now i want to ask if there is a possibility to access to this comlumn data and get a new value every 10s :(when i try to do that with select query ,it retruned to me always the same value which is the old value befort starting a loop).
sorry for my bad english,please help me.
Need a few more details to be specific, but I'm guessing your loop is not committing the transaction that is applying the updates. So while the loop is running, the transaction is open and, if your isolation level is read committed, you will only get the old value because the new value has not been committed to the database.
If you can explain why you're trying to do this, along with some code, table schema and example data, you might get some more insight on this.

If it's just the transaction hasn't been committed, you can see the uncommitted values using nolock
SELECT * FROM person WITH(NOLOCK)