postgres PreparedStatement commented ? problem PSQLException: No value specified for parameter ... - Mailing list pgsql-jdbc

I came across a bug in the Postgres JDBC driver with
regards to PreparedStatements and comments that contain ?
tags.

I was using hibernate with the hibernate.use_sql_comments
property set to true in order to see the HQL embedded in
the SQL as comments.  All worked fine with MySQL, and then
I switched to Postgres, and started seeing errors like
this:

org.postgresql.util.PSQLException: No value specified for
parameter 2.
    at
org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:134)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:179)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:346)

I traced it down to PreparedStatements with ? in the
comments, which expect additional parameters.  /* */ is an
acceptable form for SQL comments, and other JDBC drivers
seem to handle this ok, and hibernate uses it embedded with
the query in order to show the HQL preconversion.  The
postgres drivers probably just need to ignore the commented
areas before searching for ? in the query string.

Here's some simple code to recreate the problem:

import java.sql.*;

public class PostgresCommentBug {

    public static void main(String[] args) {

        String url = "jdbc:postgresql://localhost:5432/somedb";
        //String url = "jdbc:mysql://localhost:3306/somedb";
        String user = "";
        String pass = "";

        try {
            Class.forName("org.postgresql.Driver");
            //Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
        }
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, pass);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }

        try {
            PreparedStatement ps =
                conn.prepareStatement("/* A comment with a ? in it */
select * from foo where bar = ?");
            ps.setString(1, "xyz");
            // Postgres JDBC drivers expect 2 sets, where as other
            // drivers ignore the commented area, and expect only
one.
            ps.execute();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
        System.out.println("Success");
    }
}

Currently, I'm using driver version
postgresql-8.1-405.jdbc3.jar, although I tried it with the
jdbc2 drivers, and older versions as well, including 8.0
drivers.

I'm using DB version "PostgreSQL 8.1.3 on i686-pc-mingw32,
compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"

There are several work arounds, such as not using the sql
comments or using hibernates string tags instead of ?.  But
this would be a good fix, probably very simple too.  Just
sharing the info in case others have had this problem.  I
found very little help on this issue, and others forums
that displayed a similar error did not have a resolution or
track it down to the details.

thanks.