You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Stefan Marr <st...@web.de> on 2005/04/04 09:52:21 UTC
Statment.getGeneratedKeys not implemented? or how to use it?
Hi,
I'm trying to get a little practice using derby with JDBC.
So I've begin to do some "standard" SQL stuff.
Unfortunately I get in trouble with my real first INSERT-statement.
I'd like to have an auto-increment column for ids and get the id from the
done insert.
http://incubator.apache.org/derby/manuals/reference/sqlj229.html
or
http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/com.ibm.c
loudscape.doc/sqlj229.htm
told me, this should be possible.
My test database is set up with the following lines:
stmt.execute("CREATE TABLE personen (person_id INTEGER NOT NULL GENERATED
ALWAYS AS IDENTITY, person_name VARCHAR(100), person_ort VARCHAR(32),
PRIMARY KEY (person_id))");
stmt.execute("CREATE TABLE daten (datum_id INTEGER NOT NULL GENERATED ALWAYS
AS IDENTITY, person_id INTEGER, datum_value TIMESTAMP, datum_typ SMALLINT,
PRIMARY KEY (datum_id))");
Now I'm trying to insert:
stmt.execute("insert into personen (person_name, person_ort) values
('peter', 'london')", Statement.RETURN_GENERATED_KEYS);
And get the Id:
genIds = stmt.getGeneratedKeys();
modifiedId = genIds.getInt(0);
On the last line I'm getting an error instead of the value :(
I've also tried genIds.getInt("person_id");
In the documentation is written an array with name or column index should
work to, so I'd try the following:
String[] colIndexes = new String[1];
colIndexes[0] = "person_id";
or
int[ ] colIndexes = new int[1];
colIndexes[0] = 1; //or 0
stmt.execute("insert into personen (person_name, person_ort) values
('peter', 'london')", colIndexes);
But now I'm getting a "not implemented".
Any suggestions?
May be the tables aren't set up right?
Kind Regards
Stefan Marr
Re: Statment.getGeneratedKeys not implemented? or how to use it?
Posted by Mamta Satoor <ms...@gmail.com>.
On Apr 5, 2005 3:52 PM, Mamta Satoor <ms...@gmail.com> wrote:
> On Apr 3, 2005 11:52 PM, Stefan Marr <st...@web.de> wrote:
> > Hi,
> >
> > I'm trying to get a little practice using derby with JDBC.
> > So I've begin to do some "standard" SQL stuff.
> >
> > Unfortunately I get in trouble with my real first INSERT-statement.
> >
> > I'd like to have an auto-increment column for ids and get the id from the
> > done insert.
> >
> > http://incubator.apache.org/derby/manuals/reference/sqlj229.html
> > or
> > http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/com.ibm.c
> > loudscape.doc/sqlj229.htm
> >
> > told me, this should be possible.
> >
> > My test database is set up with the following lines:
> >
> > stmt.execute("CREATE TABLE personen (person_id INTEGER NOT NULL GENERATED
> > ALWAYS AS IDENTITY, person_name VARCHAR(100), person_ort VARCHAR(32),
> > PRIMARY KEY (person_id))");
> >
> > stmt.execute("CREATE TABLE daten (datum_id INTEGER NOT NULL GENERATED ALWAYS
> > AS IDENTITY, person_id INTEGER, datum_value TIMESTAMP, datum_typ SMALLINT,
> > PRIMARY KEY (datum_id))");
> >
> > Now I'm trying to insert:
> >
> > stmt.execute("insert into personen (person_name, person_ort) values
> > ('peter', 'london')", Statement.RETURN_GENERATED_KEYS);
> >
> > And get the Id:
> >
> > genIds = stmt.getGeneratedKeys();
> > modifiedId = genIds.getInt(0);
> >
> > On the last line I'm getting an error instead of the value :(
> > I've also tried genIds.getInt("person_id");
> >
> > In the documentation is written an array with name or column index should
> > work to, so I'd try the following:
> >
> > String[] colIndexes = new String[1];
> > colIndexes[0] = "person_id";
> >
> > or
> >
> > int[ ] colIndexes = new int[1];
> > colIndexes[0] = 1; //or 0
> >
> > stmt.execute("insert into personen (person_name, person_ort) values
> > ('peter', 'london')", colIndexes);
> >
> > But now I'm getting a "not implemented".
>
> Hi Criag,
>
> As for passing array of column names or column indexes, the
> documentation is incorrect. I will file a Jira entry for it. Derby
> does not support passing array of column names or column indexes in
> conjuction with Statement.getGeneratedKeys.
>
> Mamta
>
I have made a JIRA entry Derby-199 for the documentation of getGeneratedKeys.
thanks,
Mamta
Re: Statment.getGeneratedKeys not implemented? or how to use it?
Posted by Mamta Satoor <ms...@gmail.com>.
On Apr 3, 2005 11:52 PM, Stefan Marr <st...@web.de> wrote:
> Hi,
>
> I'm trying to get a little practice using derby with JDBC.
> So I've begin to do some "standard" SQL stuff.
>
> Unfortunately I get in trouble with my real first INSERT-statement.
>
> I'd like to have an auto-increment column for ids and get the id from the
> done insert.
>
> http://incubator.apache.org/derby/manuals/reference/sqlj229.html
> or
> http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/com.ibm.c
> loudscape.doc/sqlj229.htm
>
> told me, this should be possible.
>
> My test database is set up with the following lines:
>
> stmt.execute("CREATE TABLE personen (person_id INTEGER NOT NULL GENERATED
> ALWAYS AS IDENTITY, person_name VARCHAR(100), person_ort VARCHAR(32),
> PRIMARY KEY (person_id))");
>
> stmt.execute("CREATE TABLE daten (datum_id INTEGER NOT NULL GENERATED ALWAYS
> AS IDENTITY, person_id INTEGER, datum_value TIMESTAMP, datum_typ SMALLINT,
> PRIMARY KEY (datum_id))");
>
> Now I'm trying to insert:
>
> stmt.execute("insert into personen (person_name, person_ort) values
> ('peter', 'london')", Statement.RETURN_GENERATED_KEYS);
>
> And get the Id:
>
> genIds = stmt.getGeneratedKeys();
> modifiedId = genIds.getInt(0);
>
> On the last line I'm getting an error instead of the value :(
> I've also tried genIds.getInt("person_id");
>
> In the documentation is written an array with name or column index should
> work to, so I'd try the following:
>
> String[] colIndexes = new String[1];
> colIndexes[0] = "person_id";
>
> or
>
> int[ ] colIndexes = new int[1];
> colIndexes[0] = 1; //or 0
>
> stmt.execute("insert into personen (person_name, person_ort) values
> ('peter', 'london')", colIndexes);
>
> But now I'm getting a "not implemented".
Hi Criag,
As for passing array of column names or column indexes, the
documentation is incorrect. I will file a Jira entry for it. Derby
does not support passing array of column names or column indexes in
conjuction with Statement.getGeneratedKeys.
Mamta
RE: Statment.getGeneratedKeys not implemented? or how to use it?
Posted by Stefan Marr <st...@web.de>.
Hi Craig,
thx a lot. Your hint leads me in the right direction.
A genIds.next() was missing.
Now everything works like a charm.
Kind Regards
Stefan
> -----Ursprüngliche Nachricht-----
> Von: derby-user-return-1114-stefan.marr=web.de@db.apache.org
> [mailto:derby-user-return-1114-stefan.marr=web.de@db.apache.org] Im
> Auftrag von Craig Russell
> Gesendet: Montag, 4. April 2005 17:37
> An: Derby Discussion
> Betreff: Re: Statment.getGeneratedKeys not implemented? or how to use it?
>
> Hi Stefan,
>
> After researching the javadoc on Statement, I think I've found the
> culprit. It's nothing to do with the Statement, it's with the
> ResultSet.
>
> ResultSet columns are 1-origin not the normal Java 0-origin. Try:
>
> > genIds = stmt.getGeneratedKeys();
> > modifiedId = genIds.getInt(1); // 1-origin
>
> Craig
Re: Statment.getGeneratedKeys not implemented? or how to use it?
Posted by Craig Russell <Cr...@Sun.COM>.
Hi Stefan,
After researching the javadoc on Statement, I think I've found the
culprit. It's nothing to do with the Statement, it's with the
ResultSet.
ResultSet columns are 1-origin not the normal Java 0-origin. Try:
> genIds = stmt.getGeneratedKeys();
> modifiedId = genIds.getInt(1); // 1-origin
Craig
On Apr 4, 2005, at 12:52 AM, Stefan Marr wrote:
> Hi,
>
> I'm trying to get a little practice using derby with JDBC.
> So I've begin to do some "standard" SQL stuff.
>
> Unfortunately I get in trouble with my real first INSERT-statement.
>
> I'd like to have an auto-increment column for ids and get the id from
> the
> done insert.
>
> http://incubator.apache.org/derby/manuals/reference/sqlj229.html
> or
> http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/
> com.ibm.c
> loudscape.doc/sqlj229.htm
>
> told me, this should be possible.
>
> My test database is set up with the following lines:
>
> stmt.execute("CREATE TABLE personen (person_id INTEGER NOT NULL
> GENERATED
> ALWAYS AS IDENTITY, person_name VARCHAR(100), person_ort VARCHAR(32),
> PRIMARY KEY (person_id))");
>
> stmt.execute("CREATE TABLE daten (datum_id INTEGER NOT NULL GENERATED
> ALWAYS
> AS IDENTITY, person_id INTEGER, datum_value TIMESTAMP, datum_typ
> SMALLINT,
> PRIMARY KEY (datum_id))");
>
> Now I'm trying to insert:
>
> stmt.execute("insert into personen (person_name, person_ort) values
> ('peter', 'london')", Statement.RETURN_GENERATED_KEYS);
>
>
> And get the Id:
>
> genIds = stmt.getGeneratedKeys();
> modifiedId = genIds.getInt(0);
>
> On the last line I'm getting an error instead of the value :(
> I've also tried genIds.getInt("person_id");
>
> In the documentation is written an array with name or column index
> should
> work to, so I'd try the following:
>
> String[] colIndexes = new String[1];
> colIndexes[0] = "person_id";
>
> or
>
> int[ ] colIndexes = new int[1];
> colIndexes[0] = 1; //or 0
>
> stmt.execute("insert into personen (person_name, person_ort) values
> ('peter', 'london')", colIndexes);
>
> But now I'm getting a "not implemented".
>
> Any suggestions?
>
> May be the tables aren't set up right?
>
>
> Kind Regards
> Stefan Marr
>
>
>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!