Forum OpenACS Q&A: query works in psql but not with ns_db?

This is really strange. When I run this query in psql, I get "UPDATE 1" and it works. When I run it with ns_db, I get no errors, but my row doesn't get updated, either. Here is the query:
update users set money = money - coalesce((select cost from weapons w 
where id = $w_id and w.user_id is null), 0) where user_id = 
$user_id
(I've logged the actual sql to make sure the tcl variables are working correctly, and they are.)

In desperation I tried wrapping the coalesce in another subselect. It also works in psql, but not in ns_db:
update users set money = money - (select coalesce((select cost from 
weapons w where id = $w_id and w.user_id is null), 0) from dual) 
where user_id = $user_id
Any idea what could be happening? What is stranger is that it this is part of a transaction; the first statement in the transaction gets executed fine. Here is that code:

set update_store "update weapons set user_id = $user_id where id = 
$w_id"
set update_money "update users set money = money - coalesce((select 
cost from weapons w where id = $w_id and w.user_id is null), 0) where 
user_id = $user_id"

set db [ns_db gethandle]
with_transaction $db {
    ns_db dml $db $update_store
    ns_db dml $db $update_money
} { ... }
(removing the transaction has no effect.)

What am I missing here?

Collapse
Posted by Don Baccus on
Turn "Verbose" on in the appropriate pool, restart AOLserver and verify that the query is actually being sent to PG.  If it is getting there, it's getting executed...
Collapse
3: Duh (response to 1)
Posted by Jonathan Ellis on
Problem was that the two statements in my transaction were reversed.  The effects of the first prevented the second from doing anything.  Oops! :(

Thanks,