ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From François Schiettecatte <fschietteca...@gmail.com>
Subject Re: AW: Problem while using RETURNING.. INTO cluase with iBATIS 3.0
Date Wed, 10 Mar 2010 13:16:44 GMT
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: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
> 


---------------------------------------------------------------------
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