You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Ilia Iourovitski <ii...@ydyn.com> on 2001/10/30 20:42:00 UTC
Oracle 8i insert returning mode
Hi All,
Is there any way to generate primary key from Oracle Sequence and to insert
record in one statement.
"INSERT INTO tableName (pkName,...) VALUES (sequenceName.nextval,...)
RETURNING primKeyName INTO ?".
Thanks
Ilia
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: Oracle 8i insert returning mode
Posted by Ilia Iourovitski <ii...@ydyn.com>.
I'm trying to avoid one roundtrip to the RDBMS during insert.
What is the difference for torque which sql sequence to execute?
Isn't it up to DB Adapter and database xml mapping file how to get primary
key and how to configure key generator.
For me the advantage of Torque in comparison with Castor is speed.
Thanks,
Ilia
-----Original Message-----
From: Jason van Zyl [mailto:jvanzyl@zenplex.com]
Sent: Tuesday, October 30, 2001 4:48 PM
To: Turbine Users List
Subject: Re: Oracle 8i insert returning mode
On 10/30/01 5:42 PM, "Ilia Iourovitski" <ii...@ydyn.com> wrote:
> It is possible:
>
> CallableStatement cstmt = conn.prepareCall("{call insert into foo_tbl
> (foo_id, VALUE ) values (foo_SEQ.nextval, 'bar') returning foo_id into ?}
> ");
> cstmt.registerOutParameter(1, java.sql.Types.NUMERIC);
> cstmt.executeUpdate();
> String new_foo_id = cstmt.getString(1);
>
> All in one sql call.
>
> Castor supports this feature http://castor.exolab.org/key-generator.html
What are you trying to do. The whole point of Torque is to only have to deal
with your application objects. If you want an SQL toolkit than Torque is
probably not for you.
The methods in the generated OM classes won't do what you need?
object.save();
Object key = object.getPrimaryKey();
> Thanks,
>
> Ilia
>
> -----Original Message-----
> From: jmcnally [mailto:jmcnally]On Behalf Of John McNally
> Sent: Tuesday, October 30, 2001 12:01 PM
> To: Turbine Users List
> Subject: Re: Oracle 8i insert returning mode
>
>
> It is my understanding that there is no way to retrieve the id via jdbc
> if the sequence is updated via the insert statement. so you can write
> insert statements like you propose and call
> BasePeer.executeStatement(sql) but you will not be able to get the id.
>
> I am not sure about the extra sql RETURNING..., but I do not think you
> will be able to use this with jdbc, how will this data be returned?
>
> john mcnally
>
> Ilia Iourovitski wrote:
>>
>> Hi All,
>> Is there any way to generate primary key from Oracle Sequence and to
> insert
>> record in one statement.
>> "INSERT INTO tableName (pkName,...) VALUES (sequenceName.nextval,...)
>> RETURNING primKeyName INTO ?".
>>
>> Thanks
>>
>> Ilia
>>
>> --
>> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
>> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
--
jvz.
Jason van Zyl
http://tambora.zenplex.org
http://jakarta.apache.org/turbine
http://jakarta.apache.org/velocity
http://jakarta.apache.org/alexandria
http://jakarta.apache.org/commons
--
To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
For additional commands, e-mail:
<ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Oracle 8i insert returning mode
Posted by Jason van Zyl <jv...@zenplex.com>.
On 10/30/01 5:42 PM, "Ilia Iourovitski" <ii...@ydyn.com> wrote:
> It is possible:
>
> CallableStatement cstmt = conn.prepareCall("{call insert into foo_tbl
> (foo_id, VALUE ) values (foo_SEQ.nextval, 'bar') returning foo_id into ?}
> ");
> cstmt.registerOutParameter(1, java.sql.Types.NUMERIC);
> cstmt.executeUpdate();
> String new_foo_id = cstmt.getString(1);
>
> All in one sql call.
>
> Castor supports this feature http://castor.exolab.org/key-generator.html
What are you trying to do. The whole point of Torque is to only have to deal
with your application objects. If you want an SQL toolkit than Torque is
probably not for you.
The methods in the generated OM classes won't do what you need?
object.save();
Object key = object.getPrimaryKey();
> Thanks,
>
> Ilia
>
> -----Original Message-----
> From: jmcnally [mailto:jmcnally]On Behalf Of John McNally
> Sent: Tuesday, October 30, 2001 12:01 PM
> To: Turbine Users List
> Subject: Re: Oracle 8i insert returning mode
>
>
> It is my understanding that there is no way to retrieve the id via jdbc
> if the sequence is updated via the insert statement. so you can write
> insert statements like you propose and call
> BasePeer.executeStatement(sql) but you will not be able to get the id.
>
> I am not sure about the extra sql RETURNING..., but I do not think you
> will be able to use this with jdbc, how will this data be returned?
>
> john mcnally
>
> Ilia Iourovitski wrote:
>>
>> Hi All,
>> Is there any way to generate primary key from Oracle Sequence and to
> insert
>> record in one statement.
>> "INSERT INTO tableName (pkName,...) VALUES (sequenceName.nextval,...)
>> RETURNING primKeyName INTO ?".
>>
>> Thanks
>>
>> Ilia
>>
>> --
>> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
>> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
> --
> To unsubscribe, e-mail:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
jvz.
Jason van Zyl
http://tambora.zenplex.org
http://jakarta.apache.org/turbine
http://jakarta.apache.org/velocity
http://jakarta.apache.org/alexandria
http://jakarta.apache.org/commons
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: Oracle 8i insert returning mode
Posted by Ilia Iourovitski <ii...@ydyn.com>.
It is possible:
CallableStatement cstmt = conn.prepareCall("{call insert into foo_tbl
(foo_id, VALUE ) values (foo_SEQ.nextval, 'bar') returning foo_id into ?}
");
cstmt.registerOutParameter(1, java.sql.Types.NUMERIC);
cstmt.executeUpdate();
String new_foo_id = cstmt.getString(1);
All in one sql call.
Castor supports this feature http://castor.exolab.org/key-generator.html
Thanks,
Ilia
-----Original Message-----
From: jmcnally [mailto:jmcnally]On Behalf Of John McNally
Sent: Tuesday, October 30, 2001 12:01 PM
To: Turbine Users List
Subject: Re: Oracle 8i insert returning mode
It is my understanding that there is no way to retrieve the id via jdbc
if the sequence is updated via the insert statement. so you can write
insert statements like you propose and call
BasePeer.executeStatement(sql) but you will not be able to get the id.
I am not sure about the extra sql RETURNING..., but I do not think you
will be able to use this with jdbc, how will this data be returned?
john mcnally
Ilia Iourovitski wrote:
>
> Hi All,
> Is there any way to generate primary key from Oracle Sequence and to
insert
> record in one statement.
> "INSERT INTO tableName (pkName,...) VALUES (sequenceName.nextval,...)
> RETURNING primKeyName INTO ?".
>
> Thanks
>
> Ilia
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
--
To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
For additional commands, e-mail:
<ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Oracle 8i insert returning mode
Posted by John McNally <jm...@collab.net>.
It is my understanding that there is no way to retrieve the id via jdbc
if the sequence is updated via the insert statement. so you can write
insert statements like you propose and call
BasePeer.executeStatement(sql) but you will not be able to get the id.
I am not sure about the extra sql RETURNING..., but I do not think you
will be able to use this with jdbc, how will this data be returned?
john mcnally
Ilia Iourovitski wrote:
>
> Hi All,
> Is there any way to generate primary key from Oracle Sequence and to insert
> record in one statement.
> "INSERT INTO tableName (pkName,...) VALUES (sequenceName.nextval,...)
> RETURNING primKeyName INTO ?".
>
> Thanks
>
> Ilia
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>