Patil
I am ignorant about Oracle, I use MySQL (another fine Oracle product :), my
first thought would be to use a stored procedure.
François
On Mar 10, 2010, at 4:27 AM, Patil Yogesh wrote:
>
> 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:[email protected]]
>> Gesendet: Dienstag, 9. März 2010 19:49
>> An: [email protected]
>> 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: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>>
>
>
> -----
> --
> 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: [email protected]
> For additional commands, e-mail: [email protected]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
