Forum OpenACS Q&A: Problem insert operators ' (example O'Riley)

1: Problem insert operators ' (example O'Riley)

Posted by Dayra Munoz on 05/10/02 04:43 AM

Hi friends: I have one problem.

I have installed Linux 7.2 with AOL 3.3.1 and Postgresql 7.1.3. My
problem is when i try the update or insert the name O'Riley in
database, OpenACS said the follow:

Database operation "dml" failed
while executing
"ns_pg_bind dml nsdb1 {update stri_scientist_information set
first_name=O'Riley ,last_name=Yahoo,gender='F' where visitor_id=17}"
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql"

Oracle8i accept it. How i do for that Postgresql accept this
opeartors.

Thanks so much.

2: Response to Problem insert operators ' (example O'Riley) (response to 1)

Posted by Walter Smith on 05/10/02 06:30 AM

Dayra,
when inserting strings into a database you need to enclose them in apostrophes, and the strings themselves need to be "double apostrophized," using DoubleApos, ns_dbquotevalue or equivalent, if there is any chance that they will contain one or more apostrophes, as in the name "O'Riley." This goes for Oracle as well as Postgres, even if the SQL statement appeared to work on Oracle this time.

There are a couple documents that are well worth reading if you haven't done so already. This topic and some other important issues are covered in the "Common Errors" page (although be aware this is an older document and some parts might be outdated):
https://openacs.org/doc/common-errors.html

SQL for Web Nerds is another document that is a quick-study guide with a lot of relevant information for using databases, particularly in the ACS context:
http://www.arsdigita.com/books/sql/

I hope that helps.

3: Response to Problem insert operators ' (example O'Riley) (response to 1)

Posted by C. R. Oldham on 05/10/02 04:31 PM

I thought using bind variables took care of quoting apostrophes and other "dangerous" characters.

4: Response to Problem insert operators ' (example O'Riley) (response to 1)

Posted by Don Baccus on 05/10/02 04:35 PM

Yes, using the bind syntax does take care of this. There's something else going on.

First of all, Oracle 8i won't accept unquoted strings so presumably either the apostrophe's are stripped from the example he posted or bind vars are really being used.

Anyway ... Dayra, we need the actual database error you got. Assuming you did really use the bind variable syntax or typed " 'O''Riley' ".

5: Response to Problem insert operators ' (example O'Riley) (response to 1)

Posted by Walter Smith on 05/10/02 05:47 PM

I guess I'm falling behind faster than I thought. 😟

In this case I probably should have noticed from the syntax that it was 4.x, but I think it would be helpful to indicate OACS version when posting. Maybe that could be considered SOP.