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