PostgreSQL JDBC - PreparedStatement.setString

Author

PreparedStatement.setString

Bert Hiri

2005-04-22, 3:24 am

Hi,

Suppose I have a table like:

CREATE TABLE test
( id DECIMAL(10)
);

When executing the following statement from the psql command prompt:

INSERT INTO test(id) VALUES ('12345');

That will succeed.

When executing the following from JDBC:

PreparedStatement stmt =
con.prepareStatement("INSERT INTO test(id) VALUES (?)");
stmt.setString("12345");
stmt.executeUpdate();

This will fail. With the message:

Cause: ERROR: column "id" is of type numeric but expression is of type
character varying

I think this shouldn't fail. This exact same piece of Java code does work
with all other databases that I work with. And psql doesn't seem to mind
taking a string value either, so why should the PreparedStatement?

Regards,
Bert

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Oliver Jowett

2005-04-22, 3:24 am

Bert Hiri wrote:

> PreparedStatement stmt =
> con.prepareStatement("INSERT INTO test(id) VALUES (?)");
> stmt.setString("12345");
> stmt.executeUpdate();
>
> This will fail. With the message:
>
> Cause: ERROR: column "id" is of type numeric but expression is of type
> character varying
>
> I think this shouldn't fail. This exact same piece of Java code does work
> with all other databases that I work with. And psql doesn't seem to mind
> taking a string value either, so why should the PreparedStatement?

See http://archives.postgresql.org/pgsq...03/msg00060.php for
past discussion of this.

In summary: it might be a common idiom, but the JDBC spec doesn't
require drivers to support it, and there are protocol-level reasons why
it is not easy to support.

-O

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links