ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Patil Yogesh <patilyoge...@gmail.com>
Subject Re: AW: Problem while using RETURNING.. INTO cluase with iBATIS 3.0
Date Wed, 10 Mar 2010 09:27:30 GMT

I have thought of this approach, but it requires two round trips to
database.... First for retrieving sequence.nextVal and then for inserting
the value into the database..........

I want to achieve this in only one database call.....

I want to insert generated nextVal as well as want it to be returned.....

Please provide your thoughts on it.........



stephen.friedrich wrote:
> 
> This is what I use (not counting any typos while transfering my code to
> your example).
> You can grab the id from the employee object afterwards:
> 
> <insert id="insertValue" parameterType="Employee">
>     <selectKey keyProperty="id" resultType="long" order="BEFORE">
>         select EMPLOYEE_SEQ.nextval from dual
>     </selectKey>
> 
>     insert into emp (ID, NAME, DESIGNATION, DEPARTMENT) 
>     values (#{id, jdbcType=NUMERIC},
>             #{name, jdbcType=VARCHAR}, 
>             #{designation, jdbcType=VARCHAR}, 
>             #{department,jdbcType=VARCHAR})
> </insert>
> 
> -- Stephen
> 
> -----Ursprüngliche Nachricht-----
> Von: Patil Yogesh [mailto:patilyogeshp@gmail.com] 
> Gesendet: Dienstag, 9. März 2010 19:49
> An: user-java@ibatis.apache.org
> Betreff: Problem while using RETURNING.. INTO cluase with iBATIS 3.0
> 
> 
> I am using iBATIS3.0/Java with oracle database.
> 
> I want my insert to return the id (which is generated by a sequence). For
> inserting I am using 
> 
> <insert id="insertValue" parameterType="Employee">
>        insert into emp (ID, NAME, DESIGNATION, DEPARTMENT) 
>        values (EMPLOYEE_SEQ.nextval, #{name, jdbcType=VARCHAR}, 
>                  #{designation, jdbcType=VARCHAR}, #{department,
> jdbcType=VARCHAR})
> </insert>
> 
> For returning inserted sequenceId I am trying to use RETURNING .. INTO
> clause. But it is failing.... I am missing out something....
> 
> I am using following syntax for using RETURNING... INTO clause...
> 
> <select id="insertEnvelope" parameterType="ValidEnvelope" resultMap="test"
> statementType="CALLABLE">
>     DECLARE
> 	envelopeId VARCHAR2(20);
>     BEGIN
> 	INSERT INTO MV.ENVELOPE (ID, PARTY_ID, TYPE_ID, GROUP_ID, NAME, 
>             CURRENT_BALANCE, DISPLAY_FLAG, STATUS, SORT_ORDER)
> 	    values (MV.ENVELOPE_SEQ.nextval, #{partyId, jdbcType=VARCHAR}, 
>                         #{typeId, jdbcType=DECIMAL},
> 	                #{groupId, jdbcType=VARCHAR}, #{name, jdbcType=VARCHAR}, 
> 	                #{currentBalance, jdbcType=DECIMAL}, #{displayFlag,
> jdbcType=DECIMAL},
> 	                #{status, jdbcType=VARCHAR}, #{sortOrder,
> jdbcType=INTEGER})
> 	    RETURNING ID into envelopeId;
> 	END;
> </select>
> 
> I am able to insert record successfully but I am not able to retrive
> inserted ID into it.
> 
> Please help me to find out where I am going wrong.......
> 
> -----
> --
> Regards,
> Yogesh Patil.
> -- 
> View this message in context:
> http://old.nabble.com/Problem-while-using-RETURNING..--INTO-cluase-with-iBATIS-3.0-tp27840213p27840213.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
> 
> 
> 


-----
--
Regards,
Yogesh Patil.
-- 
View this message in context: http://old.nabble.com/Problem-while-using-RETURNING..--INTO-cluase-with-iBATIS-3.0-tp27840213p27847308.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message