Forum OpenACS Q&A: 0or1 row Fails with Zero Rows. Why?

Collapse
1: 0or1 row Fails with Zero Rows. Why?
Posted by Bob OConnor on 07/17/01 03:02 PM

This morning I'm blind to what the problem is with this simple query. Please Help!

I've used this many times before without trouble in the past... I simplified it to this which should run on any openacs 3.x system: 

#
set db [ns_db gethandle]
set user_id 5
ns_log Notice "dallas... user_id: $user_id"

set selection [ns_db 0or1row $db "select
  first_names,
  last_name
from users
where user_id=$user_id"]
set_variables_after_query

if [empty_string_p $selection] {
   ad_return_error "Account Unavailable" "user_id $user_id not found"
   ns_log Notice "selection EMPTY"
   return
} else {
  ns_log Notice "dallas... selection: $selection"
  ns_log Notice "dallas... $last_name, $first_names"
}
ns_db releasehandle $db
ns_returnredirect "/"

This works fine for the case where the user id "5" exists as a row in the user table
BUT
if I change it to a non-existing user ID, In other words ZERO rows returned I get this error message in the server log and the ad_return_error proc never executes. 

...[-conn953-] Notice: dallas... id: 55
...[-conn953-] Error: nsd.tcl: invalid set id: ""
invalid set id: ""
    while executing
"ns_set size $selection"
    invoked from within
"set set_variables_after_query_limit [ns_set size $selection]..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {
            set set_variables_after_query_i 0
            set set_variables_after_query_limit [ns_set size 
$selection]
            while {$set_variables_after_qu ..."
    (procedure "set_variables_after_query" line 2)
    invoked from within
"set_variables_after_query
    (file "dallas_data.tcl" line 14)
    invoked from within
"source $script"
    invoked from within
"if ![file exists $script] {
                ns_returnnotfound $conn
        } else {
                source $script
        }"
    (procedure "ns_sourceproc" line 3)
    invoked from within
"ns_sourceproc cns39185 {}"

So, what am I missing? I tried this on a second, newer OpenACS 3.2.5 and get the same error. There is no problem running the select in psql.

THANKS in Advance

-Bob

Collapse
2: Response to 0or1 row Fails with Zero Rows. Why? (response to 1)
Posted by Pascal Scheffers on 07/17/01 03:14 PM
I am afraid it is supposed to do that. From the AOLServer ns_db API doc:
ns_db 0or1row expects the SQL to be a select statement that returns exactly zero or one row. On zero rows, a null string is returned. On one row, a newly allocated ns_set is returned.
You need to check to see if the string is not empty before doing set_variables_after_query. 
set selection [ns_db 0or1row $db "select
  first_names,
  last_name
from users
where user_id=$user_id"]
set_variables_after_query

if [empty_string_p $selection] {
   ad_return_error "Account Unavailable" "user_id $user_id not found"
   ns_log Notice "selection EMPTY"
   return
} else {
  set_variables_after_query
  ns_log Notice "dallas... selection: $selection"
  ns_log Notice "dallas... $last_name, $first_names"
}
ns_db releasehandle $db

Also, you can use ad_get_user_info to set the variables you are trying to obtain, but make sure user_id exists before you execute it (use ad_verify_and_get_user_id or something, they return 0 if the user doesn't exist)

Collapse
3: Response to 0or1 row Fails with Zero Rows. Why? (response to 1)
Posted by Pascal Scheffers on 07/17/01 03:17 PM
Hmm. You really need to read my previous post in your web browser, otherwise the example above makes no sense...
Collapse
4: Response to 0or1 row Fails with Zero Rows. Why? (response to 1)
Posted by Bob OConnor on 07/17/01 04:11 PM
Duh Bob!
Sometimes the obvious and simple stuff eludes me.

Thank you Pascal!