Обсуждение: Problem escaping, nonstandard use of \\ in a string literal
I am having a problem with escaping characters after upgrading to 8.3. I have changed the postgresql.conf file to contain: standard_conforming_strings = on I do not get the error: nonstandard use of \\ in a string literal anymore but now I am having a problem escaping certain characters like the apostrophe \'. I am now getting the following error: syntax error at or near "S" at character 282 that is referring to an apostrophe in an insert statement. I am not escaping this character in my code. I am assuming that the driver is escaping it. I am using the postgresql-8.3-603.jdbc3.jar as the driver. I see that you can escape by doing E'Joe\'s', but how do I get this to work without going through my code and shouldn't this be done by the driver. How do I get this all to work? -- Thanks, Warren Bell 909-645-8864 warren@clarksnutrition.com
Warren Bell wrote: > I am having a problem with escaping characters after upgrading to 8.3. I > have changed the postgresql.conf file to contain: > > standard_conforming_strings = on > > I do not get the error: > > nonstandard use of \\ in a string literal > > anymore but now I am having a problem escaping certain characters like > the apostrophe \'. I am now getting the following error: > > syntax error at or near "S" at character 282 > > that is referring to an apostrophe in an insert statement. > > I am not escaping this character in my code. I am assuming that the > driver is escaping it. I am using the postgresql-8.3-603.jdbc3.jar as > the driver. > > I see that you can escape by doing E'Joe\'s', but how do I get this to > work without going through my code and shouldn't this be done by the > driver. Could you send a short code sample for your problem? It would make it easier for me to look. Yours, Laurenz Albe
Albe Laurenz wrote: > Warren Bell wrote: > >> I am having a problem with escaping characters after upgrading to >> 8.3. I have changed the postgresql.conf file to contain: >> >> standard_conforming_strings = on >> >> I do not get the error: >> >> nonstandard use of \\ in a string literal >> >> anymore but now I am having a problem escaping certain characters >> like the apostrophe \'. I am now getting the following error: >> >> syntax error at or near "S" at character 282 >> >> that is referring to an apostrophe in an insert statement. >> >> I am not escaping this character in my code. I am assuming that the >> driver is escaping it. I am using the postgresql-8.3-603.jdbc3.jar as >> the driver. >> >> I see that you can escape by doing E'Joe\'s', but how do I get this >> to work without going through my code and shouldn't this be done by >> the driver. >> > > Could you send a short code sample for your problem? > It would make it easier for me to look. > > Yours, > Laurenz Albe > > I ended up going back to PostgreSQL version 8.1. I did not have the time to work everything out. I am using Ibatis and the prepared statement that was being used was something like: INSERT INTO tabel (somestringfield) VALUES (?) the parameter that was passed was 'Joe\'s'. I also tried E'Joe\'s'. It did not like the escaped apostrophe. I guess Postgres 8.3 is escaping special characters differently. It seems like this would be something the driver would handle. How are you suppose to make this work? I will also check with the Ibatis group on this. -- Thanks, Warren Bell
Warren Bell wrote: > I ended up going back to PostgreSQL version 8.1. I did not have the time > to work everything out. > > I am using Ibatis and the prepared statement that was being used was > something like: > > INSERT INTO tabel (somestringfield) VALUES (?) > > the parameter that was passed was 'Joe\'s'. I also tried E'Joe\'s'. It > did not like the escaped apostrophe. I guess Postgres 8.3 is escaping > special characters differently. It seems like this would be something > the driver would handle. How are you suppose to make this work? I will > also check with the Ibatis group on this. This should "just work" with no quoting and no escaping of the string that you pass to setString(). i.e. you should just be doing: ps.setString(1, "Joe's"); If you've managed to get escaping/quoting errors somehow I'd really like to know how .. the parameter is passed out-of-line from the query string, not interpolated into it, so I'm really puzzled how this could ever happen. A testcase would be great. -O
Warren Bell wrote:
>>> I am having a problem with escaping characters after upgrading to
>>> 8.3. I have changed the postgresql.conf file to contain:
>>>
>>> standard_conforming_strings = on
>>>
>>> I am now getting the following error:
>>>
>>> syntax error at or near "S" at character 282
>>>
>>> I am not escaping this character in my code. I am assuming that the
>>> driver is escaping it. I am using the postgresql-8.3-603.jdbc3.jar as
>>> the driver.
>>
>> Could you send a short code sample for your problem?
>> It would make it easier for me to look.
>
> I am using Ibatis and the prepared statement that was being used was
> something like:
>
> INSERT INTO tabel (somestringfield) VALUES (?)
>
> the parameter that was passed was 'Joe\'s'. I also tried E'Joe\'s'. It
> did not like the escaped apostrophe.
The following code runs without problems:
public class WarrenBell {
public static void main(String[] args) {
try {
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:postgresql:......");
conn.createStatement().executeUpdate("SET standard_conforming_strings=on");
java.sql.PreparedStatement stmt = conn.prepareStatement("SELECT * FROM (VALUES(?)) tab(value)");
stmt.setString(1, "Joe's");
if (stmt.execute()) {
java.sql.ResultSet rs = stmt.getResultSet();
rs.next();
System.out.println(rs.getString(1));
rs.close();
}
stmt.close();
conn.close();
} catch (java.sql.SQLException e) {
System.err.println("Error " + e.getErrorCode() + " (SQLSTATE "
+ e.getSQLState() + "): " + e.getMessage());
e.printStackTrace();
}
}
}
It prints the following on standard output:
Joe's
Yours,
Laurenz Albe