Обсуждение: Bug in PreparedStatement and 'numeric' columns

If I understand correctly, using a PreparedStatement I shouldn't have to
"cast" anything in the SQL string, the driver should take care of that.

However, I'm having problem using a column of type 'numeric'. The
PreparedStatement.setBigDecimal(int, BigDecimal) javadoc states that
"The driver converts this to an SQL NUMERIC value when it sends it to
the database.". But version 7.2 of the PostgreSQL JDBC driver doesn't do
this.

Try the following:

CREATE TABLE test (value numeric);
INSERT INTO test VALUES (12.34);

And in the Java code:

PreparedStatement pStmt = connection.prepareStatement("SELECT * FROM
test WHERE value = ?");
pStmt.setBigDecimal(1, new BigDecimal("12.34"));
ResultSet rs  = pStmt.executeQuery();

Throws the following SQLException:
"ERROR:  Unable to identify an operator '=' for types 'numeric' and
'double precision'
    You will have to retype this query using an explicit cast"

Looking at the server's log, I see it received "SELECT * FROM test WHERE
value = 12.34" instead of the "SELECT * FROM test WHERE value =
12.34::numeric" it should have received if setBigDecimal() was
implemented the way the Javadoc says.

So I think I have found a bug in the PostgreSQL JDBC driver, right?


--
[]'s
Daniel Serodio

Daniel,

You should not have this problem if you use the latest version of the
driver (i.e. 7.3beta1 or the latest dev build) from jdbc.postgresql.org.

thanks,
--Barry

Daniel Serodio wrote:
 > If I understand correctly, using a PreparedStatement I shouldn't have to
 > "cast" anything in the SQL string, the driver should take care of that.
 >
 > However, I'm having problem using a column of type 'numeric'. The
 > PreparedStatement.setBigDecimal(int, BigDecimal) javadoc states that
 > "The driver converts this to an SQL NUMERIC value when it sends it to
 > the database.". But version 7.2 of the PostgreSQL JDBC driver doesn't do
 > this.
 >
 > Try the following:
 >
 > CREATE TABLE test (value numeric);
 > INSERT INTO test VALUES (12.34);
 >
 > And in the Java code:
 >
 > PreparedStatement pStmt = connection.prepareStatement("SELECT * FROM
 > test WHERE value = ?");
 > pStmt.setBigDecimal(1, new BigDecimal("12.34"));
 > ResultSet rs  = pStmt.executeQuery();
 >
 > Throws the following SQLException:
 > "ERROR:  Unable to identify an operator '=' for types 'numeric' and
 > 'double precision'
 >     You will have to retype this query using an explicit cast"
 >
 > Looking at the server's log, I see it received "SELECT * FROM test WHERE
 > value = 12.34" instead of the "SELECT * FROM test WHERE value =
 > 12.34::numeric" it should have received if setBigDecimal() was
 > implemented the way the Javadoc says.
 >
 > So I think I have found a bug in the PostgreSQL JDBC driver, right?
 >
 >