SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


access to colum while is updating


access to colum while is updating

Author
Message
lakiri333
lakiri333
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 13
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 Sadwhen 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975132 Visits: 49307
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
HandyD
HandyD
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 233
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 Sadwhen 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
lakiri333
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 13
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
jonathan.crawford
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5914 Visits: 1635
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
Jonathan AC Roberts
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15410 Visits: 6388
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 Sadwhen 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)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search