By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,712 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,712 IT Pros & Developers. It's quick & easy.

inserting an apostrophe

P: n/a
i am trying to add a record of a name. the name has an apostrophe (O'Neil).

i am doing this through a servlet and wrote the code to use
PreparedStatement.

when i do:

....
PrepareStatement pstmt = con.prepareStatement("INSERT INTO directory
(name, phone) VALUES (?,?)");
pstmt.setString(1, name);
pstmt.setString(2, phone);
pstmt.executeUpdate();
....

i get the following error:

java.sql.SQLException: ERROR: parser: parse error at or near "NEIL" at character 47

at org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:94)
at org.postgresql.Connection.ExecSQL(Connection.java: 398)
at org.postgresql.jdbc2.Statement.execute(Statement.j ava:130)
at org.postgresql.jdbc2.Statement.executeQuery(Statem ent.java:54)

i thought that if i use the setString() method of the prepared
statement, it would automatically escape the apostrophe.

it only seems to be doing it on INSERT. with UPDATE it seems to behave
as it should (escape the character).

if i enter the record from psql, it works fine.

i am using postgres 7.2 on a freebsd 4.3 platform and jdk1.3.1

Peter Choe

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Ron
Peter Choe wrote:
i am trying to add a record of a name. the name has an apostrophe
(O'Neil).

i am doing this through a servlet and wrote the code to use
PreparedStatement.

when i do:

...
PrepareStatement pstmt = con.prepareStatement("INSERT INTO directory
(name, phone) VALUES (?,?)");
pstmt.setString(1, name);
pstmt.setString(2, phone);
pstmt.executeUpdate();
...
We do this all the time (pg 7.3, java 1.4) without any problems. The
only problem I see with this snippet is the lack of a terminating
semicolon on your insert statement (after (?,?)).

i get the following error:

java.sql.SQLException: ERROR: parser: parse error at or near "NEIL" at
character 47

at org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:94)
at org.postgresql.Connection.ExecSQL(Connection.java: 398)
at org.postgresql.jdbc2.Statement.execute(Statement.j ava:130)
at org.postgresql.jdbc2.Statement.executeQuery(Statem ent.java:54)

i thought that if i use the setString() method of the prepared
statement, it would automatically escape the apostrophe.

it only seems to be doing it on INSERT. with UPDATE it seems to behave
as it should (escape the character).

if i enter the record from psql, it works fine.

i am using postgres 7.2 on a freebsd 4.3 platform and jdk1.3.1

Peter Choe

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

P: n/a

On Tuesday, Sep 9, 2003, at 10:28 America/Chicago, Ron wrote:
Peter Choe wrote:
i am trying to add a record of a name. the name has an apostrophe
(O'Neil).
i am doing this through a servlet and wrote the code to use
PreparedStatement.
when i do:
...
PrepareStatement pstmt = con.prepareStatement("INSERT INTO directory
(name, phone) VALUES (?,?)");
pstmt.setString(1, name);
pstmt.setString(2, phone);
pstmt.executeUpdate();
...

We do this all the time (pg 7.3, java 1.4) without any problems. The
only problem I see with this snippet is the lack of a terminating
semicolon on your insert statement (after (?,?)).


None is necessary; that's a statement separator and it's only expecting
one statement. (In fact, some databases' JDBC and/or ODBC drivers fail
if you _do_ put in a semicolon.)

Peter, I see nothing wrong with that code at all. And I've also
inserted many records containing apostrophes without problems. What
version of the JDBC drivers are you using? In particular, the ones
shipped with the database aren't necessarily the best available, even
at the time that version of the database was released. Try downloading
the freshest ones from http://jdbc.postgresql.org/.

Scott
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #3

P: n/a

On Tuesday, Sep 9, 2003, at 10:28 America/Chicago, Ron wrote:
Peter Choe wrote:
i am trying to add a record of a name. the name has an apostrophe
(O'Neil).
i am doing this through a servlet and wrote the code to use
PreparedStatement.
when i do:
...
PrepareStatement pstmt = con.prepareStatement("INSERT INTO directory
(name, phone) VALUES (?,?)");
pstmt.setString(1, name);
pstmt.setString(2, phone);
pstmt.executeUpdate();
...

We do this all the time (pg 7.3, java 1.4) without any problems. The
only problem I see with this snippet is the lack of a terminating
semicolon on your insert statement (after (?,?)).


None is necessary; that's a statement separator and it's only expecting
one statement. (In fact, some databases' JDBC and/or ODBC drivers fail
if you _do_ put in a semicolon.)

Peter, I see nothing wrong with that code at all. And I've also
inserted many records containing apostrophes without problems. What
version of the JDBC drivers are you using? In particular, the ones
shipped with the database aren't necessarily the best available, even
at the time that version of the database was released. Try downloading
the freshest ones from http://jdbc.postgresql.org/.

Scott
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.