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:03:53 GMT
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