Postgres Pro
Downloads
Home   >   mailing lists

Thread: bug in jdbc

bug in jdbc

From
luvar@plaintext.sk
Date:
Hi, I have executed some update query and I have requested to return generated id...

statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
ResultSet ids  = statement.getGeneratedKeys();
ids.next();
ids.getInt(1);

It will fail with this exception:

19:03:50,300         WARN ObjectBrowser:254 - Bad value for type int : /home/luvar/output.svg
org.postgresql.util.PSQLException: Bad value for type int : /home/luvar/output.svg
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)
.....


I am using maven. Used jdbc version:

    <dependency>
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.0-801.jdbc4</version>
    </dependency>

I have table constructed in such way, that id (bigserial typed) column is NOT first column. It is second and first
columnof my table is "url" column to which I try insert "/home/luvar/output.svg" value. 

Can you have a look and confirm this bug to me, or there is no bug and something I am doing bad?

Re: bug in jdbc

From
dmp
Date:
luvar@plaintext.sk wrote:
> Hi, I have executed some update query and I have requested to return generated id...
>
> statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
> ResultSet ids  = statement.getGeneratedKeys();
> ids.next();
> ids.getInt(1);
>
> It will fail with this exception:
>
> 19:03:50,300         WARN ObjectBrowser:254 - Bad value for type int : /home/luvar/output.svg
> org.postgresql.util.PSQLException: Bad value for type int : /home/luvar/output.svg
>     at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)
>     at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)
> .....
>
>
> I am using maven. Used jdbc version:
>
>     <dependency>
>     <groupId>postgresql</groupId>
>     <artifactId>postgresql</artifactId>
>     <version>9.0-801.jdbc4</version>
>     </dependency>
>
> I have table constructed in such way, that id (bigserial typed) column is NOT first column. It is second and first
columnof my table is "url" column to which I try insert "/home/luvar/output.svg" value. 
>
> Can you have a look and confirm this bug to me, or there is no bug and something I am doing bad?
>

I have confirmed the behavior you have indicated in a test case that
is not using Maven, but rather the JDBC directly.

Though I have not reviewed the behavior of statement.executeUpdate(query,
Statement.RETURN_GENERATED_KEYS) thoroughly I suggest you try the
modifications that I made to your example code that helped me to understand
more fully what is happening. I can not address if this is a bug or not.

sqlStatement.executeUpdate(sqlStatementString, Statement.RETURN_GENERATED_KEYS);
resultSet = sqlStatement.getGeneratedKeys();
tableMetaData = resultSet.getMetaData();
while (resultSet.next())
{
    for (int i = 1; i < tableMetaData.getColumnCount() + 1; i++)
    {
       System.out.println(i + " " + tableMetaData.getColumnName(i));
       System.out.println(resultSet.getString(colNameString));
    }
}

Output:
Connection Created
INSERT INTO "public"."key_table21" ("text","id") VALUES ('aaa',33)
1 text
aaa
2 id
33
Connection Closed

danap.

Re: bug in jdbc

From
dmp
Date:
dmp wrote:
> luvar@plaintext.sk wrote:
>> Hi, I have executed some update query and I have requested to return
>> generated id...
>>
>> statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
>> ResultSet ids = statement.getGeneratedKeys();
>> ids.next();
>> ids.getInt(1);
>>
>> It will fail with this exception:
>>
>> 19:03:50,300 WARN ObjectBrowser:254 - Bad value for type int :
>> /home/luvar/output.svg
>> org.postgresql.util.PSQLException: Bad value for type int :
>> /home/luvar/output.svg
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)
>>
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)
>>
>> .....
>>
>>
>> I am using maven. Used jdbc version:
>>
>> <dependency>
>> <groupId>postgresql</groupId>
>> <artifactId>postgresql</artifactId>
>> <version>9.0-801.jdbc4</version>
>> </dependency>
>>
>> I have table constructed in such way, that id (bigserial typed) column
>> is NOT first column. It is second and first column of my table is
>> "url" column to which I try insert "/home/luvar/output.svg" value.
>>
>> Can you have a look and confirm this bug to me, or there is no bug and
>> something I am doing bad?
>>
>
> I have confirmed the behavior you have indicated in a test case that
> is not using Maven, but rather the JDBC directly.
>
> Though I have not reviewed the behavior of statement.executeUpdate(query,
> Statement.RETURN_GENERATED_KEYS) thoroughly I suggest you try the
> modifications that I made to your example code that helped me to understand
> more fully what is happening. I can not address if this is a bug or not.
>
> sqlStatement.executeUpdate(sqlStatementString,
> Statement.RETURN_GENERATED_KEYS);
> resultSet = sqlStatement.getGeneratedKeys();
> tableMetaData = resultSet.getMetaData();
> while (resultSet.next())
> {
> for (int i = 1; i < tableMetaData.getColumnCount() + 1; i++)
> {
> System.out.println(i + " " + tableMetaData.getColumnName(i));
> System.out.println(resultSet.getString(colNameString));
> }
> }
>
> Output:
> Connection Created
> INSERT INTO "public"."key_table21" ("text","id") VALUES ('aaa',33)
> 1 text
> aaa
> 2 id
> 33
> Connection Closed
>
> danap.
>
Sorry I made a simplification of the example code in the email that
obscured it.

while (resultSet.next())
{
    for (int i = 1; i < tableMetaData.getColumnCount() + 1; i++)
    {
       String colNameString = tableMetaData.getColumnName(i);
       System.out.println(i + " " + colNameString);
       System.out.println(resultSet.getString(colNameString));
    }
}

danap.

Re: bug in jdbc

From
Oliver Jowett
Date:
On 4 September 2011 05:16,  <luvar@plaintext.sk> wrote:
> Hi, I have executed some update query and I have requested to return generated id...
>
> statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
> ResultSet ids  = statement.getGeneratedKeys();
> ids.next();
> ids.getInt(1);
>
> It will fail with this exception:
>
> 19:03:50,300         WARN ObjectBrowser:254 - Bad value for type int : /home/luvar/output.svg
> org.postgresql.util.PSQLException: Bad value for type int : /home/luvar/output.svg
>        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)
>        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)

Javadoc for getGeneratedKeys says:

Note:If the columns which represent the auto-generated keys were not
specified, the JDBC driver implementation will determine the columns
which best represent the auto-generated keys.

So you shouldn't expect a particular set of returned columns unless
you explicitly specify which columns to return. (In this particular
case the driver is playing it safe and returning *all* columns as it
doesn't know which ones could be affected by triggers etc)
You could look up the column you want by name rather than by index, or
use the overloaded variant of executeUpdate() that takes a list of
column names.

Oliver

Re: bug in jdbc

From
luvar@plaintext.sk
Date:
----- "Oliver Jowett" <oliver@opencloud.com> wrote:

> On 4 September 2011 05:16,  <luvar@plaintext.sk> wrote:
> > Hi, I have executed some update query and I have requested to return
> generated id...
> >
> > statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
> > ResultSet ids  = statement.getGeneratedKeys();
> > ids.next();
> > ids.getInt(1);
> >
> > It will fail with this exception:
> >
> > 19:03:50,300         WARN ObjectBrowser:254 - Bad value for type int
> : /home/luvar/output.svg
> > org.postgresql.util.PSQLException: Bad value for type int :
> /home/luvar/output.svg
> >        at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2759)
> >        at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2003)
>
> Javadoc for getGeneratedKeys says:
>
> Note:If the columns which represent the auto-generated keys were not
> specified, the JDBC driver implementation will determine the columns
> which best represent the auto-generated keys.
>
> So you shouldn't expect a particular set of returned columns unless
> you explicitly specify which columns to return. (In this particular
> case the driver is playing it safe and returning *all* columns as it
> doesn't know which ones could be affected by triggers etc)
> You could look up the column you want by name rather than by index,
> or
> use the overloaded variant of executeUpdate() that takes a list of
> column names.

Many thanks for clarification. So this behavior is in line with specification.

To be accurate, I should call something like:
statement.executeUpdate(query, new String[] {"id"});
instead of:
statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
to get only autogenerated (in my case "id") column. Please correct me, If I am wrong.

>
> Oliver