Forum OpenACS Q&A: SQL statement return 0 row

Collapse
1: SQL statement return 0 row
Posted by hau wan lin on 11/06/00 03:18 PM
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
2: Response to SQL statement return 0 row (response to 1)
Posted by Don Baccus on 11/06/00 03:36 PM
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
3: Response to SQL statement return 0 row (response to 1)
Posted by hau wan lin on 11/06/00 04:15 PM
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
4: Response to SQL statement return 0 row (response to 1)
Posted by Michael A. Cleverly on 11/06/00 04:20 PM
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.