Обсуждение: BUG #6445: PreparedStatement.setObject(1,java.util.String)

The following bug has been logged on the website:

Bug reference:      6445
Logged by:          Rajesh
Email address:      rajesh4.t@tcs.com
PostgreSQL version: 8.4.3
Operating system:   Windows XP
Description:=20=20=20=20=20=20=20=20


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.t@abs.com' )

code for prepared statement

try{
                            pstatement=3Dconn.prepareStatement(insertQuery);
                            for(int k=3D1;k<rows;k++){
                                pstatement.clearParameters();
                                Cell datacell[]=3Dsheets[i].getRow(k);
                                for(int
eachCell=3D0;eachCell<columnNames.size();eachCell++){
                                    pstatement.setObject(eachCell+1,
datacell[eachCell].getContents());
                                }
                                pstatement.addBatch();
                            }
                            pstatement.executeBatch();
                            pstatement.close();
                        }catch(Exception er){
                            LOG.error("Failed to set Row data for tableName:
"+tableName,er);
                            throw new DbComparisonException(er);
                        }=20

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

On Wed, 8 Feb 2012, rajesh4.t@tcs.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.t@abs.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