Forum OpenACS Development: I got this, 0or1row failed (exception NSINT, Query returned more than one row

Collapse
1: I got this, 0or1row failed (exception NSINT, Query returned more than one row
Posted by Rod Cap on 10/02/03 08:12 PM
While doing a query like this:

select count(p.first_names)
        from persons p, parties pa, usuarios_micolegio_categoria umc where lower(p.first_names)  = lower(:first_names) and lower(p.last_name) = lower(:last_name) and umc.categoria = :categoria and umc.user_id = p.person_id and pa.party_id = p.person_id and pa.email like :likeDomin

What I was trying to do is to get the number of users who has the same first_names and last_name that a parameter I recive; in a domain and a category given

Collapse
2: Re: I got this, 0or1row failed (exception NSINT, Query returned more than one row (response to 1)
Posted by Rod Cap on 10/02/03 08:22 PM
An other thing, the way I use this query is like this:

set person_id [db_string IDpersona "select......

and then I:
  if {$personCount > 0} { ....

All the query I define in the .tcl, I write it into the .xql too. I dont realy know what for the .xql? I would like to know.  Maybe the db_string is not the best.

Thanks in advance for your replay, Rod

Collapse
3: Re: I got this, 0or1row failed (exception NSINT, Query returned more than one row (response to 1)
Posted by Alfred Werner on 10/02/03 08:27 PM
it needs a name .. count(first_names) is sorta anonymous at that point

try

select count(first_names) as first_name_count .. etc

and then $first_name_count should be available..

Collapse
4: Re: I got this, 0or1row failed (exception NSINT, Query returned more than one row (response to 3)
Posted by Rod Cap on 10/08/03 08:15 PM
Thnx Alfred, I'm sorry for the delay because I'am new at this.  So, I tried what you suggest but I did not get what I spect. I did this select count(first_names) as first_name_count, but first_name_count was not available because it throws this: can't read "person_count": no such variable
    while executing
"if {$person_count > 0} {
.....
So then I tried the query like this:
set personCount [db_string personExist "select count(p.person_id) as person_count
        from (select pe.person_id as person_id, pe.first_names as first_names, pe.last_name as last_name  from persons pe, parties pa where pe.person_id = pa.party_id and pa.email like :likeDomin ) p,  usuarios_micolegio_categoria umc where lower(p.first_names)  = lower(:first_names) and lower(p.last_name) = lower(:last_name) and umc.categoria = :categoria and umc.user_id = p.person_id"]

But it steel throws 0or1row fail Query was not a statement returning rows.  I'am not exactly sure if its valid to do that kind of like :likeDomin, where the variable likeDomin contain '%somedomain.something'.
What should I do??

Collapse
5: Re: I got this, 0or1row failed (exception NSINT, Query returned more than one row (response to 1)
Posted by Don Baccus on 10/09/03 02:48 AM
db_string doesn't require you give a name to the single column you return.  db_string foo "select count(*) ..." is just fine.

The reason for putting the query in an .xql file is to make it easier to support both Oracle and PostgreSQL.  Standard SQL queries supported by both RDBMS's go into a vanilla .xql file, custom Oracle or PostgreSQL queries into the appropriate custom .xql file with the appropriate XML tag to designate which RDBMS uses the query.

Now ... you posted in the main forum, too - please don't double post!  It just confuses things.  Check out my response in the other forum - please post your log file contents with SQL logging enabled.  Then we can see what is actually being executed.

Oh - if you accidently gave the query the same name as another query in the script I think the query processor will always return the first one rather than give an error ... hmmm ... this is a pretty good guess on my part I think!