ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Hodge" <dyho...@gmail.com>
Subject Re: Obtaining Generated Primary Keys
Date Fri, 15 Sep 2006 14:25:16 GMT
How would you do it as anonymous pl/sql?

Thanks,

David Y. Hodge

On 9/15/06, Larry Meadors <lmeadors@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 <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