Hi,
Can you please help me explain this behaviour.
I had written the following script
<$cnt = 0>
<sql ROW "select id , forms_text, case_id from tre">
<$forms_text = ( $forms_text + " " + $case_id )>
<sql "update tre set forms_text = $forms_text where id = $id ">
</sql>
<$cnt = (1 + $cnt)>
<!--<WRITE $file_case1>$cnt</WRITE>-->
Record processed for case with case_id : $case_id
$cnt records processed
</sql>
where id is counter, formstext is varchar(4000) and case_id is long.
Now the table tre contains the following 2 records
id forms_text case_id
------------+------------+------------+
4a0155b40 abc 527174
4a0155b60 pqr 527174
.
If i run the above script, instead of showing 2 records processed, it is going into an infinite loop. Can you explain the behaviour of it.
thanks
It's not particularly safe to change the table you're serching within the search. In this case you're scanning the table begin to end. For each record you're expanding the storage required so it moves to the end and eventually gets selected again.
Perhaps you want something more like
<sql novars "update tre set forms_text=forms_text+' '+case_id"></sql>
$loop records processed
I did not get what you are trying to say.
This updation works fine if all the case_ids are unique. But it goes haywire once it receives duplicate entries
You were "lucky" I guess. I'd expect it go haywire regardless. You should not nest an update of a table inside a select from the same table unless the update will only affect fixed size data like ints and floats etc.
Oh is there a logical explaination to it. I mean, i did not get the reasoning behind this happening. How is the same data being fetched again when it has already been processed. I saw it this way. The outer sql loop acts as a for loop . It picks a data and updates the same data but then theres i++, a new data is being fetched. How can it fetch the same data again ??
The outer loop doesn't prefetch everything. It fetches one row processes the loop and moves to the next row and there's no index involved in your query so it's a linear scan over the table. Imagine a compact table with 2 records a and b and empty space x.
a
b
select first row
-> a
b
update expanding a so it won't fit there any more.
thats a superb explaination ... What do you mean by expanding a. Does that mean, a with the newly updated data and how would that move 2 records of a below ?
By expanding I mean adding to the text length of the record. In this example "a" became "aa". I guess I wasn't entirely clear. "aa" is the modified record "a" not 2 "a" records. In the example above each row is 1 record.
