You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "Srini.Gullipalli" <Sr...@gmail.com> on 2008/03/01 04:29:37 UTC
Oracle - INSERT/UPDATE RETURNING INTO
How can I achieve this using iBatis. Not sure if this syntax is ANSI SQL,
but Oracle supports this
UPDATE aQueueTable SET LOCK_IN = 'Y'
WHERE LOCK_IN='N' and ROWNUM < 2
RETURNING keyColumn INTO :key
Alternate use of this syntax :
INSERT INTO aTable (sequenceColumn, otherColumns...) VALUES ( aSeq.NEXTVAL,
values...) RETURNING sequenceColumn INTO :key
This syntax eliminates an additional round trip to database ( compared to
selectkey )
--
View this message in context: http://www.nabble.com/Oracle---INSERT-UPDATE-RETURNING-INTO-tp15772285p15772285.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Oracle - INSERT/UPDATE RETURNING INTO
Posted by Srini Gullipalli <sr...@gmail.com>.
Good question. Found the answer :-) Two ways. Tested both and seem to
work.
(1) Anonymous PL/SQL Block and register output parameters using a
CallableStatement. Use ibatis <procedure> for the anonymous PL/SQL block
BEGIN
UPDATE aQueueTable SET LOCK_IN = 'Y'
WHERE LOCK_IN='N' and ROWNUM < 2
RETURNING keyColumn INTO ?;
END;
(2) Oracle JDBC3.0 drivers support registering return parameters on
prepared statements also. This is not part of JDBC 3.0 spec :-(
statement = (OraclePreparedStatement)connection.prepareStatement(sql);
statement.registerReturnParameter(1, OracleTypes.NUMBER);
if (statement.executeUpdate() > 0)
{
resultSet = statement.getReturnResultSet();
while (resultSet.next())
{
System.out.println("Value for column 1 is " + resultSet.getLong(1));
}
}
iBatis team,
Any plans to support JDBC 3.0 generated keys ?
- Srini.
Larry Meadors wrote:
> How would you do it with JDBC?
>
> Larry
>
>
> On Fri, Feb 29, 2008 at 8:29 PM, Srini.Gullipalli
> <Sr...@gmail.com> wrote:
>
>> How can I achieve this using iBatis. Not sure if this syntax is ANSI SQL,
>> but Oracle supports this
>>
>> UPDATE aQueueTable SET LOCK_IN = 'Y'
>> WHERE LOCK_IN='N' and ROWNUM < 2
>> RETURNING keyColumn INTO :key
>>
>> Alternate use of this syntax :
>>
>> INSERT INTO aTable (sequenceColumn, otherColumns...) VALUES ( aSeq.NEXTVAL,
>> values...) RETURNING sequenceColumn INTO :key
>>
>> This syntax eliminates an additional round trip to database ( compared to
>> selectkey )
>>
>>
>> --
>> View this message in context: http://www.nabble.com/Oracle---INSERT-UPDATE-RETURNING-INTO-tp15772285p15772285.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>>
Re: Oracle - INSERT/UPDATE RETURNING INTO
Posted by Larry Meadors <la...@gmail.com>.
How would you do it with JDBC?
Larry
On Fri, Feb 29, 2008 at 8:29 PM, Srini.Gullipalli
<Sr...@gmail.com> wrote:
>
> How can I achieve this using iBatis. Not sure if this syntax is ANSI SQL,
> but Oracle supports this
>
> UPDATE aQueueTable SET LOCK_IN = 'Y'
> WHERE LOCK_IN='N' and ROWNUM < 2
> RETURNING keyColumn INTO :key
>
> Alternate use of this syntax :
>
> INSERT INTO aTable (sequenceColumn, otherColumns...) VALUES ( aSeq.NEXTVAL,
> values...) RETURNING sequenceColumn INTO :key
>
> This syntax eliminates an additional round trip to database ( compared to
> selectkey )
>
>
> --
> View this message in context: http://www.nabble.com/Oracle---INSERT-UPDATE-RETURNING-INTO-tp15772285p15772285.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>