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 Brian Spindler <bs...@netuitive.com> on 2009/10/13 15:29:47 UTC
auto generated ids
I have a table let's say FOO with a column name 'ID' and it is auto
increment.
Now I want to insert one row into that table and then insert 10 rows
into an adjacency table with the new generated id. I thought something
like this might work:
INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
VALUES (
(SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
'MyName');
...
and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads the
generated id from ADJTBL as opposed to the FOO table. What am I doing
wrong? Is this possible? I am running this from a .sql file within IJ
tool.
Thanks,
Brian
RE: Corrupt derby db
Posted by Brian Spindler <bs...@netuitive.com>.
I appreciate the time Rick but I apologize. I was preparing myself for
a discussion with the customer but now it seems they have simply used
the term "corruption" to indicate that stale records were in the
database. False alarm! We're using 10.5.3 release btw (latest and
greatest! )
-----Original Message-----
From: Richard.Hillegas@Sun.COM [mailto:Richard.Hillegas@Sun.COM]
Sent: Friday, October 16, 2009 11:16 AM
To: Derby Discussion
Subject: Re: Corrupt derby db
Hi Brian,
It's hard to say anything useful without more information. For starters:
o What version of Derby is being used?
o What's the stack trace which signals that you have a data corruption?
Thanks,
-Rick
Brian Spindler wrote:
> I don't have specifics at the moment (I can get them) but we have a
> small application that uses the Embedded version of Derby. Problem is
> every 3-6mos after running at customer site they get a corrupted derby
> database. I was curious if this was a programming error and what
> typically causes corruption to derby? It seems to happen pretty
> consistently although I have no idea why.
>
> Thanks for any insights.
>
> -Brian
>
>
Re: Corrupt derby db
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Brian,
It's hard to say anything useful without more information. For starters:
o What version of Derby is being used?
o What's the stack trace which signals that you have a data corruption?
Thanks,
-Rick
Brian Spindler wrote:
> I don't have specifics at the moment (I can get them) but we have a
> small application that uses the Embedded version of Derby. Problem is
> every 3-6mos after running at customer site they get a corrupted derby
> database. I was curious if this was a programming error and what
> typically causes corruption to derby? It seems to happen pretty
> consistently although I have no idea why.
>
> Thanks for any insights.
>
> -Brian
>
>
Corrupt derby db
Posted by Brian Spindler <bs...@netuitive.com>.
I don't have specifics at the moment (I can get them) but we have a
small application that uses the Embedded version of Derby. Problem is
every 3-6mos after running at customer site they get a corrupted derby
database. I was curious if this was a programming error and what
typically causes corruption to derby? It seems to happen pretty
consistently although I have no idea why.
Thanks for any insights.
-Brian
RE: auto generated ids
Posted by Brian Spindler <bs...@netuitive.com>.
Thanks Knut!
I removed the extra INSERT statements and simply appended the values as
you shown in your example and it worked great. Thanks for clarifying.
-----Original Message-----
From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM]
Sent: Tuesday, October 13, 2009 10:06 AM
To: Derby Discussion
Subject: Re: auto generated ids
Brian Spindler <bs...@netuitive.com> writes:
> I have a table let's say FOO with a column name 'ID' and it is auto
> increment.
>
> Now I want to insert one row into that table and then insert 10 rows
> into an adjacency table with the new generated id. I thought
something
> like this might work:
>
> INSERT INTO FOO(NAME) VALUES ('BAR');
> INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
> VALUES (
> (SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
> 'MyName');
> ...
>
> and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads
the
> generated id from ADJTBL as opposed to the FOO table. What am I doing
> wrong? Is this possible? I am running this from a .sql file within
IJ
> tool.
IDENTITY_VAL_LOCAL() returns the most recently assigned value of an
identity column for a connection in a single-row INSERT statement, so
once you've inserted a new row into ADJTBL, the return value from
IDENTITY_VAL_LOCAL() will change. See
http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html.
Inserting all 10 rows into ADJTBL in one INSERT statement should work,
though:
INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME) VALUES
(IDENTITY_VAL_LOCAL(), 'MyName1'),
(IDENTITY_VAL_LOCAL(), 'MyName2'),
...
(IDENTITY_VAL_LOCAL(), 'MyName10');
--
Knut Anders
Re: auto generated ids
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Brian Spindler <bs...@netuitive.com> writes:
> I have a table let's say FOO with a column name 'ID' and it is auto
> increment.
>
> Now I want to insert one row into that table and then insert 10 rows
> into an adjacency table with the new generated id. I thought something
> like this might work:
>
> INSERT INTO FOO(NAME) VALUES ('BAR');
> INSERT INTO ADJTBL (FOO_ID, BAR_NAME)
> VALUES (
> (SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO),
> 'MyName');
> ...
>
> and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads the
> generated id from ADJTBL as opposed to the FOO table. What am I doing
> wrong? Is this possible? I am running this from a .sql file within IJ
> tool.
IDENTITY_VAL_LOCAL() returns the most recently assigned value of an
identity column for a connection in a single-row INSERT statement, so
once you've inserted a new row into ADJTBL, the return value from
IDENTITY_VAL_LOCAL() will change. See
http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html.
Inserting all 10 rows into ADJTBL in one INSERT statement should work,
though:
INSERT INTO FOO(NAME) VALUES ('BAR');
INSERT INTO ADJTBL (FOO_ID, BAR_NAME) VALUES
(IDENTITY_VAL_LOCAL(), 'MyName1'),
(IDENTITY_VAL_LOCAL(), 'MyName2'),
...
(IDENTITY_VAL_LOCAL(), 'MyName10');
--
Knut Anders