ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Obtaining Generated Primary Keys
Date Fri, 15 Sep 2006 14:10:13 GMT
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 <dyhodge@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 <patrick.crocker@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 <patrick.crocker@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
> >
> >
> >
> >
> >
>
>

Mime
View raw message