Re: BUG #6445: PreparedStatement.setObject(1,java.util.String)

On Wed, 8 Feb 2012, rajesh4(dot)t(at)tcs(dot)com wrote:

> The following bug has been logged on the website:
>
> Bug reference: 6445
> PostgreSQL version: 8.4.3
>
>
> I have a table in PostgreSQL database
>
> CREATE TABLE newtest
> (
> id numeric(6),
> name character varying(120),
> email character varying(120)
> )
>
> I am using PreparedStatement so that i can insert data into newtest table
>
> INSERT INTO NewTest (ID,NAME,EMAIL ) values (?,?,? )
>
> Actual query fired -INSERT INTO NewTest (ID,NAME,EMAIL ) values ('1','Rajesh
> ','rajesh4(dot)t(at)abs(dot)com' )
>
> Please note I am reading data from an excel sheet and
> datacell[eachCell].getContents() is of type String..prepareStatement
> setObject is complaining that I am sending String datatype which cannot be
> casted to bigint or numeric,but that is the job of setObject right to decide
> the target datatype and cast accordingly..please reply any
> solution..Parameter to setObject(can be anything) so I cannot cast the data
> to appropriate format before sending the data
>

It is not the job of setObject to determine the correct conversion to the
column type. The setObject javadoc says, "The JDBC specification
specifies a standard mapping from Java Object types to SQL types. The
given argument will be converted to the corresponding SQL type before
being sent to the database." So it is solely looking at the Java type of
the object passed to it and converting that to a SQL type. So you pass it
a String and it converts it to a varchar which is appropriate. If you
want setObject to do a conversion to a different type, that is the reason
for the additional setObject variant which takes a target sql type to
convert to, but that doesn't help your situation where you don't know what
the target type is.

The Postgresql JDBC driver offers three possible workarounds.

1) Use getParameterMetaData to try and determine the target types.

2) Use setObject(int, Object, Types.OTHER) to indicate that you don't know
that the Java type you are passing is correct.

3) Use the stringtype=unspecified URL parameter to indicate that all
String bindings may not really be strings.

Kris Jurka