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:44:27 GMT
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 <patrick.crocker@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 <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
> <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