You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by David Hodge <dy...@gmail.com> on 2006/09/15 13:57:38 UTC
Obtaining Generated Primary Keys
I am using Oracle and I am wondering if there is a way to obtain a trigger
generated Primary key after doing an INSERT with iBatis.
I know there is a selectKey function but that seems to work only if you have
a select statement, which makes sense. Other databases have ways
for you to select the last_generated_id() but I do not think Oracle has this
capability.
Has anyone else had this problem or know of a work around?
Thanks,
David Y. Hodge
Re: Obtaining Generated Primary Keys
Posted by David Hodge <dy...@gmail.com>.
That makes sense. However, I am concerned with possible concurrency
issues. If two inserts occur at the same time we can't guarantee the select
key would work. Is this a valid concern with selectKey solution you
mentioned?
Thanks,
David Y. Hodge
On 9/15/06, Larry Meadors <lm...@apache.org> wrote:
>
> David,
>
> You can still use the selectKey element in your insert if you have
> some way (other than the generated key) of uniquely identifying the
> inserted data.
>
> <insert ...>
> insert into blah blah blah
> <selectKey property="yourIdPropertyName" ...>
> select your_id from blah blah blah
> </selectKey>
> </insert>
>
> Then as long as you call insert() it should work.
>
> Larry
>
>
> On 9/15/06, David Hodge <dy...@gmail.com> wrote:
> > I am using Oracle and I am wondering if there is a way to obtain a
> trigger
> > generated Primary key after doing an INSERT with iBatis.
> >
> > I know there is a selectKey function but that seems to work only if you
> have
> > a select statement, which makes sense. Other databases have ways
> > for you to select the last_generated_id() but I do not think Oracle has
> this
> > capability.
> >
> > Has anyone else had this problem or know of a work around?
> >
> >
> > Thanks,
> >
> > David Y. Hodge
> >
>
Re: Obtaining Generated Primary Keys
Posted by Larry Meadors <lm...@apache.org>.
David,
You can still use the selectKey element in your insert if you have
some way (other than the generated key) of uniquely identifying the
inserted data.
<insert ...>
insert into blah blah blah
<selectKey property="yourIdPropertyName" ...>
select your_id from blah blah blah
</selectKey>
</insert>
Then as long as you call insert() it should work.
Larry
On 9/15/06, David Hodge <dy...@gmail.com> wrote:
> I am using Oracle and I am wondering if there is a way to obtain a trigger
> generated Primary key after doing an INSERT with iBatis.
>
> I know there is a selectKey function but that seems to work only if you have
> a select statement, which makes sense. Other databases have ways
> for you to select the last_generated_id() but I do not think Oracle has this
> capability.
>
> Has anyone else had this problem or know of a work around?
>
>
> Thanks,
>
> David Y. Hodge
>
Re: Obtaining Generated Primary Keys
Posted by David Hodge <dy...@gmail.com>.
Unfortunately, the trigger has an algorithm to create the key by
concatenating a couple fields. I do not have control over the trigger so
thus the dilemma. I think we are going to end up writing a stored procedure
to get the key.
Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
>
> Just curious... if the trigger is not using a sequence, what is it
> using?
>
> - Patrick.
>
> ________________________________
>
> From: David Hodge [mailto:dyhodge@gmail.com]
> Sent: Friday, September 15, 2006 9:04 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Obtaining Generated Primary Keys
>
>
> The trigger creates the Primary Key before the INSERT. There is no
> sequence and if I did specify the key it would be overridden by the
> trigger. I thought I could maybe use the RETURNING INTO clause where I
> specify a variable to return the primary key into. The SQL works but I
> am not sure how to get it back so that iBatis can use it or map it.
>
> Thanks,
>
> David Y. Hodge
>
>
> On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
>
> An immediate call to "SELECT your_sequence_name.currval FROM
> dual" after
> the insert?
>
> Or...
>
> How is the trigger setup? Does it override the value if you
> specify it
> yourself?
>
> This trigger (from
> http://www.oracle-base.com/articles/8i/AutoNumber.php
> <http://www.oracle-base.com/articles/8i/AutoNumber.php> ) will get the
> next
> sequence if NOT specified in the insert. This would allow you
> to get
> the nextval yourself and pass it in to the insert statement:
>
> CREATE OR REPLACE TRIGGER dept_bir
> BEFORE INSERT ON departments
> FOR EACH ROW
> WHEN (new.id IS NULL)
> BEGIN
> SELECT dept_seq.NEXTVAL
> INTO :new.id
> FROM dual;
> END;
> /
>
> - Patrick.
>
> ________________________________
>
> From: David Hodge [mailto:dyhodge@gmail.com]
> Sent: Friday, September 15, 2006 8:37 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Obtaining Generated Primary Keys
>
>
> The Problem is that I can not use a sequence, as the key is
> generated
> via a Trigger. Unfortunately, I do not have control over this
> trigger.
> Otherwise, what you said would work perfectly.
>
> Thanks,
>
> David Y. Hodge
>
>
> On 9/15/06, Crocker, Patrick <pa...@verizon.com>
> wrote:
>
> Using Oracle Sequences, you need to obtain the key
> first, then
> use it in
> the INSERT statement:
>
> <select id="getNextId" resultClass="decimal">
> SELECT your_sequence_name.nextval AS nextid FROM dual
> </select
>
> <insert id="insert" parameterClass="myTable">
> INSERT INTO my_table
> (MY_ID, MY_COLUMN)
> VALUES
> (#myId#, #myColumn#)
> </insert>
>
> - Patrick.
>
> ________________________________
>
> From: David Hodge [mailto:dyhodge@gmail.com]
> Sent: Friday, September 15, 2006 6:58 AM
> To: user-java@ibatis.apache.org
> Subject: Obtaining Generated Primary Keys
>
>
> I am using Oracle and I am wondering if there is a way
> to obtain
> a
> trigger generated Primary key after doing an INSERT with
> iBatis.
>
> I know there is a selectKey function but that seems to
> work only
> if you
> have a select statement, which makes sense. Other
> databases
> have ways
> for you to select the last_generated_id() but I do not
> think
> Oracle has
> this capability.
>
> Has anyone else had this problem or know of a work
> around?
>
>
> Thanks,
>
> David Y. Hodge
>
>
>
>
>
>
>
>
Re: Obtaining Generated Primary Keys
Posted by David Hodge <dy...@gmail.com>.
How would you do it as anonymous pl/sql?
Thanks,
David Y. Hodge
On 9/15/06, Larry Meadors <lm...@apache.org> wrote:
>
> In that case, you may be able to do it as anonymous pl/sql. But I
> think I'd try the selectKey way first...but that anon pl/sql might be
> faster - just one call to the database instead of two.
>
> Bah, try them both, and use what works best for you. ;-)
>
> Larry
>
>
> On 9/15/06, David Hodge <dy...@gmail.com> wrote:
> > The trigger creates the Primary Key before the INSERT. There is no
> sequence
> > and if I did specify the key it would be overridden by the trigger. I
> > thought I could maybe use the RETURNING INTO clause where I specify a
> > variable to return the primary key into. The SQL works but I am not
> sure
> > how to get it back so that iBatis can use it or map it.
> >
> >
> > Thanks,
> >
> > David Y. Hodge
> >
> > On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
> > > An immediate call to "SELECT your_sequence_name.currval FROM dual"
> after
> > > the insert?
> > >
> > > Or...
> > >
> > > How is the trigger setup? Does it override the value if you specify
> it
> > > yourself?
> > >
> > > This trigger (from
> > > http://www.oracle-base.com/articles/8i/AutoNumber.php )
> > will get the next
> > > sequence if NOT specified in the insert. This would allow you to get
> > > the nextval yourself and pass it in to the insert statement:
> > >
> > > CREATE OR REPLACE TRIGGER dept_bir
> > > BEFORE INSERT ON departments
> > > FOR EACH ROW
> > > WHEN (new.id IS NULL)
> > > BEGIN
> > > SELECT dept_seq.NEXTVAL
> > > INTO :new.id
> > > FROM dual;
> > > END;
> > > /
> > >
> > > - Patrick.
> > >
> > > ________________________________
> > >
> > > From: David Hodge [mailto:dyhodge@gmail.com]
> > > Sent: Friday, September 15, 2006 8:37 AM
> > > To: user-java@ibatis.apache.org
> > > Subject: Re: Obtaining Generated Primary Keys
> > >
> > >
> > > The Problem is that I can not use a sequence, as the key is generated
> > > via a Trigger. Unfortunately, I do not have control over this trigger.
> > > Otherwise, what you said would work perfectly.
> > >
> > > Thanks,
> > >
> > > David Y. Hodge
> > >
> > >
> > > On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
> > >
> > > Using Oracle Sequences, you need to obtain the key first, then
> > > use it in
> > > the INSERT statement:
> > >
> > > <select id="getNextId" resultClass="decimal">
> > > SELECT your_sequence_name.nextval AS nextid FROM dual
> > > </select
> > >
> > > <insert id="insert" parameterClass="myTable">
> > > INSERT INTO my_table
> > > (MY_ID, MY_COLUMN)
> > > VALUES
> > > (#myId#, #myColumn#)
> > > </insert>
> > >
> > > - Patrick.
> > >
> > > ________________________________
> > >
> > > From: David Hodge [mailto:dyhodge@gmail.com]
> > > Sent: Friday, September 15, 2006 6:58 AM
> > > To: user-java@ibatis.apache.org
> > > Subject: Obtaining Generated Primary Keys
> > >
> > >
> > > I am using Oracle and I am wondering if there is a way to
> obtain
> > > a
> > > trigger generated Primary key after doing an INSERT with
> iBatis.
> > >
> > > I know there is a selectKey function but that seems to work
> only
> > > if you
> > > have a select statement, which makes sense. Other databases
> > > have ways
> > > for you to select the last_generated_id() but I do not think
> > > Oracle has
> > > this capability.
> > >
> > > Has anyone else had this problem or know of a work around?
> > >
> > >
> > > Thanks,
> > >
> > > David Y. Hodge
> > >
> > >
> > >
> > >
> > >
> >
> >
>
Re: Obtaining Generated Primary Keys
Posted by Larry Meadors <lm...@apache.org>.
In that case, you may be able to do it as anonymous pl/sql. But I
think I'd try the selectKey way first...but that anon pl/sql might be
faster - just one call to the database instead of two.
Bah, try them both, and use what works best for you. ;-)
Larry
On 9/15/06, David Hodge <dy...@gmail.com> wrote:
> The trigger creates the Primary Key before the INSERT. There is no sequence
> and if I did specify the key it would be overridden by the trigger. I
> thought I could maybe use the RETURNING INTO clause where I specify a
> variable to return the primary key into. The SQL works but I am not sure
> how to get it back so that iBatis can use it or map it.
>
>
> Thanks,
>
> David Y. Hodge
>
> On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
> > An immediate call to "SELECT your_sequence_name.currval FROM dual" after
> > the insert?
> >
> > Or...
> >
> > How is the trigger setup? Does it override the value if you specify it
> > yourself?
> >
> > This trigger (from
> > http://www.oracle-base.com/articles/8i/AutoNumber.php )
> will get the next
> > sequence if NOT specified in the insert. This would allow you to get
> > the nextval yourself and pass it in to the insert statement:
> >
> > CREATE OR REPLACE TRIGGER dept_bir
> > BEFORE INSERT ON departments
> > FOR EACH ROW
> > WHEN (new.id IS NULL)
> > BEGIN
> > SELECT dept_seq.NEXTVAL
> > INTO :new.id
> > FROM dual;
> > END;
> > /
> >
> > - Patrick.
> >
> > ________________________________
> >
> > From: David Hodge [mailto:dyhodge@gmail.com]
> > Sent: Friday, September 15, 2006 8:37 AM
> > To: user-java@ibatis.apache.org
> > Subject: Re: Obtaining Generated Primary Keys
> >
> >
> > The Problem is that I can not use a sequence, as the key is generated
> > via a Trigger. Unfortunately, I do not have control over this trigger.
> > Otherwise, what you said would work perfectly.
> >
> > Thanks,
> >
> > David Y. Hodge
> >
> >
> > On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
> >
> > Using Oracle Sequences, you need to obtain the key first, then
> > use it in
> > the INSERT statement:
> >
> > <select id="getNextId" resultClass="decimal">
> > SELECT your_sequence_name.nextval AS nextid FROM dual
> > </select
> >
> > <insert id="insert" parameterClass="myTable">
> > INSERT INTO my_table
> > (MY_ID, MY_COLUMN)
> > VALUES
> > (#myId#, #myColumn#)
> > </insert>
> >
> > - Patrick.
> >
> > ________________________________
> >
> > From: David Hodge [mailto:dyhodge@gmail.com]
> > Sent: Friday, September 15, 2006 6:58 AM
> > To: user-java@ibatis.apache.org
> > Subject: Obtaining Generated Primary Keys
> >
> >
> > I am using Oracle and I am wondering if there is a way to obtain
> > a
> > trigger generated Primary key after doing an INSERT with iBatis.
> >
> > I know there is a selectKey function but that seems to work only
> > if you
> > have a select statement, which makes sense. Other databases
> > have ways
> > for you to select the last_generated_id() but I do not think
> > Oracle has
> > this capability.
> >
> > Has anyone else had this problem or know of a work around?
> >
> >
> > Thanks,
> >
> > David Y. Hodge
> >
> >
> >
> >
> >
>
>
RE: Obtaining Generated Primary Keys
Posted by "Crocker, Patrick" <pa...@verizon.com>.
Just curious... if the trigger is not using a sequence, what is it
using?
- Patrick.
________________________________
From: David Hodge [mailto:dyhodge@gmail.com]
Sent: Friday, September 15, 2006 9:04 AM
To: user-java@ibatis.apache.org
Subject: Re: Obtaining Generated Primary Keys
The trigger creates the Primary Key before the INSERT. There is no
sequence and if I did specify the key it would be overridden by the
trigger. I thought I could maybe use the RETURNING INTO clause where I
specify a variable to return the primary key into. The SQL works but I
am not sure how to get it back so that iBatis can use it or map it.
Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
An immediate call to "SELECT your_sequence_name.currval FROM
dual" after
the insert?
Or...
How is the trigger setup? Does it override the value if you
specify it
yourself?
This trigger (from
http://www.oracle-base.com/articles/8i/AutoNumber.php
<http://www.oracle-base.com/articles/8i/AutoNumber.php> ) will get the
next
sequence if NOT specified in the insert. This would allow you
to get
the nextval yourself and pass it in to the insert statement:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
- Patrick.
________________________________
From: David Hodge [mailto:dyhodge@gmail.com]
Sent: Friday, September 15, 2006 8:37 AM
To: user-java@ibatis.apache.org
Subject: Re: Obtaining Generated Primary Keys
The Problem is that I can not use a sequence, as the key is
generated
via a Trigger. Unfortunately, I do not have control over this
trigger.
Otherwise, what you said would work perfectly.
Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <pa...@verizon.com>
wrote:
Using Oracle Sequences, you need to obtain the key
first, then
use it in
the INSERT statement:
<select id="getNextId" resultClass="decimal">
SELECT your_sequence_name.nextval AS nextid FROM dual
</select
<insert id="insert" parameterClass="myTable">
INSERT INTO my_table
(MY_ID, MY_COLUMN)
VALUES
(#myId#, #myColumn#)
</insert>
- Patrick.
________________________________
From: David Hodge [mailto:dyhodge@gmail.com]
Sent: Friday, September 15, 2006 6:58 AM
To: user-java@ibatis.apache.org
Subject: Obtaining Generated Primary Keys
I am using Oracle and I am wondering if there is a way
to obtain
a
trigger generated Primary key after doing an INSERT with
iBatis.
I know there is a selectKey function but that seems to
work only
if you
have a select statement, which makes sense. Other
databases
have ways
for you to select the last_generated_id() but I do not
think
Oracle has
this capability.
Has anyone else had this problem or know of a work
around?
Thanks,
David Y. Hodge
Re: Obtaining Generated Primary Keys
Posted by David Hodge <dy...@gmail.com>.
The trigger creates the Primary Key before the INSERT. There is no sequence
and if I did specify the key it would be overridden by the trigger. I
thought I could maybe use the RETURNING INTO clause where I specify a
variable to return the primary key into. The SQL works but I am not sure
how to get it back so that iBatis can use it or map it.
Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
>
> An immediate call to "SELECT your_sequence_name.currval FROM dual" after
> the insert?
>
> Or...
>
> How is the trigger setup? Does it override the value if you specify it
> yourself?
>
> This trigger (from
> http://www.oracle-base.com/articles/8i/AutoNumber.php) will get the next
> sequence if NOT specified in the insert. This would allow you to get
> the nextval yourself and pass it in to the insert statement:
>
> CREATE OR REPLACE TRIGGER dept_bir
> BEFORE INSERT ON departments
> FOR EACH ROW
> WHEN (new.id IS NULL)
> BEGIN
> SELECT dept_seq.NEXTVAL
> INTO :new.id
> FROM dual;
> END;
> /
>
> - Patrick.
>
> ________________________________
>
> From: David Hodge [mailto:dyhodge@gmail.com]
> Sent: Friday, September 15, 2006 8:37 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Obtaining Generated Primary Keys
>
>
> The Problem is that I can not use a sequence, as the key is generated
> via a Trigger. Unfortunately, I do not have control over this trigger.
> Otherwise, what you said would work perfectly.
>
> Thanks,
>
> David Y. Hodge
>
>
> On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
>
> Using Oracle Sequences, you need to obtain the key first, then
> use it in
> the INSERT statement:
>
> <select id="getNextId" resultClass="decimal">
> SELECT your_sequence_name.nextval AS nextid FROM dual
> </select
>
> <insert id="insert" parameterClass="myTable">
> INSERT INTO my_table
> (MY_ID, MY_COLUMN)
> VALUES
> (#myId#, #myColumn#)
> </insert>
>
> - Patrick.
>
> ________________________________
>
> From: David Hodge [mailto:dyhodge@gmail.com]
> Sent: Friday, September 15, 2006 6:58 AM
> To: user-java@ibatis.apache.org
> Subject: Obtaining Generated Primary Keys
>
>
> I am using Oracle and I am wondering if there is a way to obtain
> a
> trigger generated Primary key after doing an INSERT with iBatis.
>
> I know there is a selectKey function but that seems to work only
> if you
> have a select statement, which makes sense. Other databases
> have ways
> for you to select the last_generated_id() but I do not think
> Oracle has
> this capability.
>
> Has anyone else had this problem or know of a work around?
>
>
> Thanks,
>
> David Y. Hodge
>
>
>
>
>
RE: Obtaining Generated Primary Keys
Posted by "Crocker, Patrick" <pa...@verizon.com>.
An immediate call to "SELECT your_sequence_name.currval FROM dual" after
the insert?
Or...
How is the trigger setup? Does it override the value if you specify it
yourself?
This trigger (from
http://www.oracle-base.com/articles/8i/AutoNumber.php) will get the next
sequence if NOT specified in the insert. This would allow you to get
the nextval yourself and pass it in to the insert statement:
CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
- Patrick.
________________________________
From: David Hodge [mailto:dyhodge@gmail.com]
Sent: Friday, September 15, 2006 8:37 AM
To: user-java@ibatis.apache.org
Subject: Re: Obtaining Generated Primary Keys
The Problem is that I can not use a sequence, as the key is generated
via a Trigger. Unfortunately, I do not have control over this trigger.
Otherwise, what you said would work perfectly.
Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
Using Oracle Sequences, you need to obtain the key first, then
use it in
the INSERT statement:
<select id="getNextId" resultClass="decimal">
SELECT your_sequence_name.nextval AS nextid FROM dual
</select
<insert id="insert" parameterClass="myTable">
INSERT INTO my_table
(MY_ID, MY_COLUMN)
VALUES
(#myId#, #myColumn#)
</insert>
- Patrick.
________________________________
From: David Hodge [mailto:dyhodge@gmail.com]
Sent: Friday, September 15, 2006 6:58 AM
To: user-java@ibatis.apache.org
Subject: Obtaining Generated Primary Keys
I am using Oracle and I am wondering if there is a way to obtain
a
trigger generated Primary key after doing an INSERT with iBatis.
I know there is a selectKey function but that seems to work only
if you
have a select statement, which makes sense. Other databases
have ways
for you to select the last_generated_id() but I do not think
Oracle has
this capability.
Has anyone else had this problem or know of a work around?
Thanks,
David Y. Hodge
Re: Obtaining Generated Primary Keys
Posted by David Hodge <dy...@gmail.com>.
The Problem is that I can not use a sequence, as the key is generated via a
Trigger. Unfortunately, I do not have control over this trigger. Otherwise,
what you said would work perfectly.
Thanks,
David Y. Hodge
On 9/15/06, Crocker, Patrick <pa...@verizon.com> wrote:
>
> Using Oracle Sequences, you need to obtain the key first, then use it in
> the INSERT statement:
>
> <select id="getNextId" resultClass="decimal">
> SELECT your_sequence_name.nextval AS nextid FROM dual
> </select
>
> <insert id="insert" parameterClass="myTable">
> INSERT INTO my_table
> (MY_ID, MY_COLUMN)
> VALUES
> (#myId#, #myColumn#)
> </insert>
>
> - Patrick.
>
> ________________________________
>
> From: David Hodge [mailto:dyhodge@gmail.com]
> Sent: Friday, September 15, 2006 6:58 AM
> To: user-java@ibatis.apache.org
> Subject: Obtaining Generated Primary Keys
>
>
> I am using Oracle and I am wondering if there is a way to obtain a
> trigger generated Primary key after doing an INSERT with iBatis.
>
> I know there is a selectKey function but that seems to work only if you
> have a select statement, which makes sense. Other databases have ways
> for you to select the last_generated_id() but I do not think Oracle has
> this capability.
>
> Has anyone else had this problem or know of a work around?
>
>
> Thanks,
>
> David Y. Hodge
>
>
RE: Obtaining Generated Primary Keys
Posted by "Crocker, Patrick" <pa...@verizon.com>.
Using Oracle Sequences, you need to obtain the key first, then use it in
the INSERT statement:
<select id="getNextId" resultClass="decimal">
SELECT your_sequence_name.nextval AS nextid FROM dual
</select
<insert id="insert" parameterClass="myTable">
INSERT INTO my_table
(MY_ID, MY_COLUMN)
VALUES
(#myId#, #myColumn#)
</insert>
- Patrick.
________________________________
From: David Hodge [mailto:dyhodge@gmail.com]
Sent: Friday, September 15, 2006 6:58 AM
To: user-java@ibatis.apache.org
Subject: Obtaining Generated Primary Keys
I am using Oracle and I am wondering if there is a way to obtain a
trigger generated Primary key after doing an INSERT with iBatis.
I know there is a selectKey function but that seems to work only if you
have a select statement, which makes sense. Other databases have ways
for you to select the last_generated_id() but I do not think Oracle has
this capability.
Has anyone else had this problem or know of a work around?
Thanks,
David Y. Hodge