Forum OpenACS Q&A: db_foreach in transactions

Hi folks, I've been having fun debugging a problem with a program I'm writing (it's an Oracle-only app on OpenACS 4.5b) which I've managed to simplify down to the following. Create a test table x with one varchar2 column called test. Put the following in to a tcl page and call it.

set output ""

db_transaction {

  db_dml i {insert into x values ('testing123')}

  db_foreach time {select sysdate from dual} {

    set test [db_string checkit "select test from x" -default "No 
rows found"]
    append output "Inside db_foreach: $sysdate $test
"

  }

}

set test [db_string checkit "select test from x" -default "No rows 
found"]
append output "Outside db_foreach: $test"

db_dml d {delete x}

ns_return 200 text/html $output

(apologies for the wrap-around. don't know how to stop that)

This produces the output:

Inside db_foreach: 2002-09-24 No rows found
Outside db_foreach: testing123

As you can see, although the insert is done in the same transaction as the db_foreach, I can't see the inserted data inside the db_foreach.

I could workaround this by compiling a list first and using foreach instead of db_foreach, but I shouldn't have to!

This is probably related to this thread, but it doesn't really answer my question.

Thanks,

Michael

This is known problem with the db api.  The problem is that you have two db handles active, one for the db_foreach and one for the db_string call.  The db_string handle is not included in the transaction (even thought it appears that it should be), so your select insided the db_foreach loop doesn't return anything, because the transaction which includes the db_dml's hasn't completed yet.  In your example, you could replace the db_foreach with a db_string to get the date, and it would work okay.

There really is no work around other than restructuring the code.  As you suggested, you could build a list and then use foreach.  It's just not possible to have multiple handles active within a transaction - at least within TCL.  The problem arises from how the db drivers are implemented (both oracle and postgresql have this limitation), and my understanding is that it's not something that is easy to fix.

Another possibility is to move the code to pl/sql, as there are no limitations on the number of active handles when using the db procedural languages.

The limitation is an artifact of the way the AOLserver ns_db command works, not of Tcl per-say. The Oratcl extension, for example, does not have the same limitation of one statement-handle per connection-handle.

Hmmm, perhaps the DB api handler could have a little bit of intelligence and catch the most obvious cases of misuse and warn the developer.