You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Bob Jaster <bo...@yahoo.com> on 2006/09/26 05:39:34 UTC
autogenerated keys do not work with single column table
I have discovered that if I create a table with only one column, such as
CREATE TABLE mo.company (
id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
PRIMARY KEY (id)
);
then execute
String sql = "INSERT INTO mo.company VALUES (DEFAULT)";
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.executeUpdate();
ResultSet resultSet = preparedStatement.getGeneratedKeys();
System.out.println("resultSet.next()==" + resultSet.next());
Object object = resultSet.getObject(1);
if (object == null) {
System.out.println("object is null");
} else {
long id = resultSet.getLong(1);
System.out.println("id==" + id);
}
I always get null returned from resultSet.getObject(1), whereas if I add a second column to the table (a VARCHAR(100)) then I get the generated key value.
Is this a bug?
Bob Jaster
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
Re: autogenerated keys do not work with single column table
Posted by Deepa Remesh <dr...@gmail.com>.
On 9/25/06, Bob Jaster <bo...@yahoo.com> wrote:
> I have discovered that if I create a table with only one column, such as
>
> CREATE TABLE mo.company (
> id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
> INCREMENT BY 1),
> PRIMARY KEY (id)
> );
>
> then execute
>
> String sql = "INSERT INTO mo.company VALUES (DEFAULT)";
> PreparedStatement preparedStatement = connection.prepareStatement(sql,
> Statement.RETURN_GENERATED_KEYS);
> preparedStatement.executeUpdate();
> ResultSet resultSet =
> preparedStatement.getGeneratedKeys();
> System.out.println("resultSet.next()==" + resultSet.next());
> Object object = resultSet.getObject(1);
> if (object == null) {
> System.out.println("object is null");
> } else {
> long id = resultSet.getLong(1);
> System.out.println("id==" + id);
> }
>
> I always get null returned from resultSet.getObject(1), whereas if I add a
> second column to the table (a VARCHAR(100)) then I get the generated key
> value.
>
> Is this a bug?
I tried your repro and I get the same problem with latest official
release (10.1.3.1). However, when I tried with the latest 10.1
codeline and with 10.2 release candidate, I get correct results. So I
think there was a bug which has been fixed in the latest codelines.
It would be nice if you can try your application with the 10.2 release
candidate and confirm this is not a problem. Details of 10.2 release
candidate is available at
http://wiki.apache.org/db-derby/TenTwoRelease
Thanks,
Deepa