Forum OpenACS Q&A: sql query problem

Collapse
Posted by Char Wang on
Hi, I got a problem when I tried to query my database:

===========================================================
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")

ERROR:  Attribute 'axt1113' not found

SQL: SELECT cm_index.articleid FROM cm_index WHERE cm_index.articleid=AXT1113
    while executing
"ns_pg_bind 0or1row nsdb0 {SELECT cm_index.articleid FROM cm_index WHERE cm_index.articleid=AXT1113}"
===========================================================

The following lines are my source code

===========================================================
ad_page_contract {
    @author char.wang
    @creation-date 2003-7-14
}

set issue 113
set query1 "SELECT book.issue FROM book WHERE book.issue = $issue"
set IfIssueExist [db_0or1row {dummy1} $query1]

set textID AXT1113
set query2 "SELECT text.textid FROM text WHERE text.textid = $textID"
set IfTextExist [db_0or1row {dummy2} $query2]

ad_return_template

===========================================================

I made two sql queries in this script, both of them were trying to figure out if this record is already exist in database.
The first query can work correctly without any problem, while the second query can't.
The query condition is the only difference between these two queries.
When I tried to replace the value of textID from "AXT1113" to "1113", somehow it works, how could this happen?
Does that mean I should only use numbers to do query?

Could any one help me with this problem?
I am using OpenACS 4.6.2 with aolserver 3.3 and postgresql 7.2.3

Collapse
2: Re: sql query problem (response to 1)
Posted by Char Wang on
sorry, I posted a wrong souce code, my source code should be the following lines:

===========================================================
ad_page_contract {
    @author char.wang
    @creation-date 2003-7-14
}

set issue 113
set query1 "SELECT cm_magazine.issue FROM cm_magazine WHERE cm_magazine.issue = $issue"
set IfIssueExist [db_0or1row {dummy1} $query1]

set artID AXT1113

set query2 "SELECT cm_index.articleid FROM cm_index WHERE cm_index.articleid=$artID"
set cm2 [db_0or1row {dummy2} $query2]

ad_return_template
===========================================================

Collapse
3: Re: sql query problem (response to 1)
Posted by Robert Locke on
Hi Char,

The query fails because it doesn't have single quotes around the string, like this: 'AXT1113'

However, with OpenACS's cool database API, you need not worry about that.  Just use bind variables like this:

SELECT book.issue FROM book WHERE book.issue = :issue
SELECT text.textid FROM text WHERE text.textid = :textID

OACS will take care of properly quoting the $issue and $textID variables, including escaping embedded quotes.  This is also more secure than embedding variables directly in your query string.

Check out:
    https://openacs.org/doc/openacs-4-6-3/db-api.html
    https://openacs.org/doc/openacs-4-6-3/db-api-detailed.html

Good luck...

Collapse
4: Re: sql query problem (response to 1)
Posted by Char Wang on
Thank you Robert, it works :)