Perl DBI - updating data within a select

I have some code in a Perl DBI script very similar to this which takes a British phone number and splits it up into two parts using the function split_phone_no: $sql = "SELECT id, phone FROM tablename WHERE phone IS NOT NULL"; $sth = $dbh->prepare($sql); $sql2 = "UPDATE tablename SET areacode=?, phone=? WHERE id=?"; $sth2 = $dbh->prepare($sql2); $sth->execute(); $sth->bind_columns(undef, \\$id, \\$phone); while ($sth->fetch) { my ($areacode, $number) = &split_phone_no($phone); $sth2->execute($areacode, $number, $id); } $sth->finish(); $sth2->finish(); This table is large and the code works for the first 8000 rows or so and then it starts to behave strangely and returns rows that appear to have been already updated. I have put a lot of debug into this code and can't trace the fault. My question is, can running the second SQL statement within the first change the result set that the first SQL statement returns and therefore is this method of coding good practice? I want the first SQL statement to return a result set that does not take account of any updates performed by the second SQL statement. Ben.

Ben Thompson wrote: > I have some code in a Perl DBI script very similar to this which takes a > British phone number and splits it up into two parts using the function > split_phone_no: > > $sql = "SELECT id, phone FROM tablename WHERE phone IS NOT NULL"; > $sth = $dbh->prepare($sql); > > $sql2 = "UPDATE tablename SET areacode=?, phone=? WHERE id=?"; > $sth2 = $dbh->prepare($sql2); > > $sth->execute(); > $sth->bind_columns(undef, \\$id, \\$phone); > while ($sth->fetch) { > my ($areacode, $number) = &split_phone_no($phone); > $sth2->execute($areacode, $number, $id); > } > > $sth->finish(); > $sth2->finish(); > > This table is large and the code works for the first 8000 rows or so and > then it starts to behave strangely and returns rows that appear to have > been already updated. I have put a lot of debug into this code and can't > trace the fault. > > My question is, can running the second SQL statement within the first > change the result set that the first SQL statement returns and therefore > is this method of coding good practice? I want the first SQL statement > to return a result set that does not take account of any updates > performed by the second SQL statement. Perl, DBI and DBD::Informix (and, indeed, ESQL/C) are all largely coincidental to this. What logging mode is your database? And what isolation level are you running at? I'd guess 'unlogged' and hence 'dirty read', but I might be wrong. Is there a major reason not to use a SELECT ... FOR UPDATE and then UPDATE ... WHERE CURRENT OF statement pair? OK - I would suspect that you are indeed double updating some rows, because the update causes the changed row to appear in the index on phone. Not very nice - it shouldn't happen. Did you consider: ALTER TABLE tablename ADD phone_wo_areacode CHAR(6); UPDATE tablename SET phone_wo_areacode = ?, areacode = ? WHERE id = ?;-- or WHERE CURRENT OF $sth->{CursorName} ALTER TABLE tablename DROP phone;RENAME tablename.phone_wo_areacode AS phone; -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > Perl, DBI and DBD::Informix (and, indeed, ESQL/C) are all largely > coincidental to this. > > What logging mode is your database? And what isolation level are you > running at? I'd guess 'unlogged' and hence 'dirty read', but I might be > wrong. Unbuf, using the default isolation level which I believe is committed read. > Is there a major reason not to use a SELECT ... FOR UPDATE and then > UPDATE ... WHERE CURRENT OF statement pair? I am not familiar with this syntax. I'll look it up. Thanks. > OK - I would suspect that you are indeed double updating some rows, > because the update causes the changed row to appear in the index on > phone. Not very nice - it shouldn't happen. Did you consider: > > ALTER TABLE tablename ADD phone_wo_areacode CHAR(6); > UPDATE tablename SET phone_wo_areacode = ?, areacode = ? WHERE id = ?;> -- or WHERE CURRENT OF $sth->{CursorName} > ALTER TABLE tablename DROP phone;> RENAME tablename.phone_wo_areacode AS phone; I think the above is a safer way of doing it as I am not 100% satisfied with the workaround I have put in. With this update there will be no users logged in at the time so I can drop and rename columns. I will need to check triggers that depend on these columns though. Ben.