Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[JDBC] bug in jdbc

2 views
Skip to first unread message

lu...@plaintext.sk

unread,
Sep 3, 2011, 1:16:30 PM9/3/11
to
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?

--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

dmp

unread,
Sep 4, 2011, 12:31:00 PM9/4/11
to
lu...@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.

dmp

unread,
Sep 4, 2011, 12:41:46 PM9/4/11
to
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));

Oliver Jowett

unread,
Sep 4, 2011, 5:25:14 PM9/4/11
to
On 4 September 2011 05:16, <lu...@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

0 new messages