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

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.GEISINGER@external.thalesgroup.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
>
>