Bug in AbstracJdbc2Statement.replaceProcessing when using dollar quoting? - Mailing list pgsql-jdbc

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