Re: Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting?

Marc,

Can you show me where in the spec it says you can use dollar sign
quoting like that ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Thu, Sep 20, 2012 at 7:25 AM, GEISINGER Marc - Contractor
<Marc(dot)GEISINGER(at)external(dot)thalesgroup(dot)com> wrote:
> Hi,
>
> I think there is a bug in AbstracJdbc2Statement.replaceProcessing. When I
> am trying to execute the following sql command with a java.sql.Statement the
> data written to the database is not what it should be:
>
>
>
> INSERT INTO dev.stringtest (id, val) VALUES (1, $a$ {a}{f} {b} {f}$a$)
>
>
>
> The replaceProcessing method removes {f} and cuts the string. If i execute
> this string using pgAdmin it works perfectly ok. Also inserting this string
> using a prepared statement is ok. But i also have an occasion where strings
> like this will be inserted as statement. And then the result is not what is
> expected (see below).
>
>
>
> I am using PostgreSQL 9.1.5 build 1500 32-bit Windows as local test server,
> the jdbc driver version is postgresql-9.1-902.jdbc4.jar.
>
>
>
> For the example code i use one table:
>
>
>
> CREATE TABLE dev.stringtest
>
> (
>
> val character varying(50),
>
> id integer NOT NULL,
>
> CONSTRAINT stringsest_pkey PRIMARY KEY (id )
>
> )
>
>
>
> And a litte test to see the bug:
>
>
>
> public class PostgresTest {
>
>
>
> /** the string to insert. */
>
> public static final String formatString = "{a} {f} {b} {f}";
>
>
>
> /**
>
> *
>
> * @param args
>
> */
>
> public static void main(final String[] args) {
>
> final PostgresTest pgTest = new PostgresTest();
>
> try {
>
> pgTest.doInsert();
>
> pgTest.doSelect();
>
> } catch (final SQLException e) {
>
> e.printStackTrace();
>
> }
>
> System.out.println("Finished");
>
> }
>
>
>
> /**
>
> *
>
> * Constructor.
>
> *
>
> */
>
> private PostgresTest() {
>
> try {
>
> initDB();
>
> } catch (final SQLException e) {
>
> e.printStackTrace();
>
> }
>
> }
>
>
>
> /**
>
> * Clear db and insert test data.
>
> *
>
> * @throws SQLException
>
> * in case of a db error
>
> */
>
> public void doInsert() throws SQLException {
>
> final String param = "$a$" + formatString + "$a$";
>
> final String deleteSql = "TRUNCATE dev.stringtest";
>
> final String insertSql = "INSERT INTO dev.stringtest (id, val)
> VALUES (1, "
>
> + param + ")";
>
> final Statement stmt = connection.createStatement();
>
> stmt.execute(deleteSql);
>
> stmt.execute(insertSql);
>
> }
>
>
>
> /**
>
> * Load inserted test data and check it.
>
> *
>
> * @throws SQLException
>
> * in case of a db error
>
> */
>
> public void doSelect() throws SQLException {
>
> final String selectSql = "SELECT val FROM dev.stringtest WHERE id =
> 1";
>
> final Statement stmt = connection.createStatement();
>
> final ResultSet resultSet = stmt.executeQuery(selectSql);
>
> if (resultSet.next()) {
>
> final String val = resultSet.getString(1);
>
> System.out.println("Expected: " + formatString);
>
> System.out.println("Loaded: " + val);
>
> }
>
> }
>
>
>
> /**
>
> * Init the DB connection.
>
> *
>
> * @throws SQLException
>
> * in case of a db error
>
> */
>
> private void initDB() throws SQLException {
>
> connection = DriverManager.getConnection(
>
> "jdbc:postgresql://localhost:5432/dev", "user",
> "password");
>
> }
>
>
>
> /** the connection. */
>
> private Connection connection;
>
>
>
> }
>
>
>
>
>
> When running this code i get the result:
>
>
>
> Expected: {a} {f} {b} {f}
>
> Loaded: {a} {b
>
> Finished
>
>
>
>
>
> I could trace the problem to the method
> org.postgresql.jdbc2.AbstractJdbc2Statement.replaceProcessing(String). Seems
> dollar quoted strings are not supported here. And additionally the function
> parseSql should only replace {fn xxx} but it is replacing {f} and even more
> characters until the final '}'.
>
>
>
> If i use {d} in the string it gets replaced by DATE.
>
>
>
> Expected: {a} {d} {b} {f}
>
> Loaded: {a} DATE {b}
>
> Finished
>
>
>
> which i think can be seen as correct, but still this is not what i expected
> here. I did not try any more letters because f is the maximum I insert.
>
>
>
> regards
>
> Marc
>
>