in reply to Why does DBI prepare break my loop?

Here, in a nutshell, is what I think you should be doing.   Fortunately, it is an easy change:

  1. You will need to use two statement-handles:   one for the SELECT statement, the second for the UPDATE.   As it is, you are destroying(!) the SELECT statement while you’re fetching from it, and that’s why the loop ends.
  2. The UPDATE statement should use placeholders, not literal text, and so it should be PREPAREd once, outside of the loop, then executed repeatedly with a different set of substitutions for those placeholders each time.

For example, in relevant parts ... (extemporaneous coding, might have syntax errors ...)

# LET'S GIVE MEANINGFUL VARIABLE-NAMES TO THE TWO STATEMENT HANDLES .. +. $sth_select = $dbh->prepare("SELECT volumeletter,peernodes from VolDat +a order + by volumeid"); $sth_select->execute(); $sth_update = $dbh->prepare("UPDATE VolData SET volstatus = ? WHERE vo +lumeletter = ?"); # NOTICE THAT THE '?'S ARE NOT ENCLOSED IN QUOTES!
... and ...
$sth_update->execute( [$vol_current_state, $vol] );

The question-marks in the prepared statement (which are not enclosed in quotes) act as “placeholders” for values which are provided each time the prepared statement is executed.

NOTE:   In the case of an SQLite database, you should also enclose the loop in a transaction.   When an UPDATE statement is executed outside of a transaction, SQLite will physically verify (re-read ...) the data after it has written, more than doubling the I/O activity and bringing things to a crawl.   When a transaction is active, SQLite performs the expected “lazy writes.”   This is not-so important for other database engines, but it is crucial for SQLite.

Replies are listed 'Best First'.
Re^2: Why does DBI prepare break my loop?
by roboticus (Chancellor) on Jul 08, 2014 at 01:56 UTC

    sundialsvc4:

    You'll need to change that last line of code to:

    $sth_update->execute( $vol_current_state, $vol );

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Re^2: Why does DBI prepare break my loop?
by Anonymous Monk on Jul 07, 2014 at 22:39 UTC

    Despite the advice to use transactions not being bad, like last time I call BS on the "physically verify" part. (Got any references?)

    Also like last time, you're misrepresenting what "outside a transaction" means, since there is no such thing. There's only implicit and explicit transactions, and unlike what you say, SQLite performs the same actions at the end of either. Explicit transactions just help becase they delay those actions until the user choses to commit the transaction.