ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Crocker, Patrick" <patrick.croc...@verizon.com>
Subject RE: Obtaining Generated Primary Keys
Date Fri, 15 Sep 2006 14:28:09 GMT
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