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:patilyoge...@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-unsubscr...@ibatis.apache.org
> For additional commands, e-mail: user-java-h...@ibatis.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
> For additional commands, e-mail: user-java-h...@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-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
