Re: Bug in PreparedStatement and 'numeric' columns

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?
 >
 >