Forum OpenACS Q&A: SQL statement return 0 row

Collapse
Posted by hau wan lin on
Can I know why the below SQL statement return 0 row instead of one row
with null value.

Assume there is no user with id = 3 in the table.

Select user_id, count(*) from users where user_id=3 group by use_id;

I face a problem in this type of query where query return 0 row.
Can anyone teach me how to fix it? Thanks for your help

Collapse
Posted by Don Baccus on
Well, no rows is SQL92 compliant (PG 6.5 used to always return an empty row, but that was a bug and indeed I filed it as such, and it got fixed for PG 7.0).

You should handle the empty case in Tcl...

Collapse
Posted by hau wan lin on
I find that proc database_to_tcl_string expects the SQL to be a select statement that returns exactly one row and returns that row as an ns_set.

if
{
    set selection [ns_db 1row $db $sql]
}
else
{
    set selection [ns_db 1row $db [db_sql_prep $sql]]
}

there is why when PG return 0 row, the proc can't handle it.

If I am wrong, please correct me.

I change proc database_to_tcl_string into this format:

if
{
    set selection [ns_db 0or1row $db $sql]
}
else
{
    set selection [ns_db 0or1row $db [db_sql_prep $sql]]
}

However, it comes out with another error:

Error: invalid set id: ""
invalid set id: ""
    while executing
"ns_set value $selection 0"
    invoked from within
"return [ns_set value $selection 0]..."

Can I know what is the problem

Collapse
Posted by Michael A. Cleverly on
Try database_to_tcl_string_or_null instead of database_to_tcl_string if it's possible for the SQL in question to return 0 rows.