Thread: [cx-oracle-users] cx_oracle, returning insert id
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: kellie h. <kel...@ya...> - 2006-03-22 20:09:29
|
Hello,
I am trying to get back the id of a record after inserting it into the
table. My Oracle database has a trigger that automatically creates the
id on insert. The PL/SQL statement that works for me is:
SQL> declare id int;
2 begin
3 insert into table (field) values ('foo') returning tableid into
id;
4 dbms_output.put_line(id);
5 end;
6 /
How can I run this through cx_oracle and get the id stored in a
variable so I can use it?
Many thanks.
Kellie Hobbs, UC Berkeley
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
|
|
From: Anthony T. <ant...@gm...> - 2006-03-22 20:45:52
|
Sure. Its quite straightforward but not covered by the DB API.
# this creates the bind variable for use by Oracle
idVar =3D cursor.var(cx_Oracle.NUMBER)
# execute the statement exactly as you normally would,
# binding the variable you just created above
cursor.execute("""
insert into SomeTable (
SomeOtherColumn
) values (
:someValue
) returning Id
into :id""",
id =3D idVar,
someValue =3D "SomeValue")
# get the value after the statement is executed
id =3D idVar.getvalue()
Hope that explains things.
On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> Hello,
>
> I am trying to get back the id of a record after inserting it into the
> table. My Oracle database has a trigger that automatically creates the
> id on insert. The PL/SQL statement that works for me is:
> SQL> declare id int;
> 2 begin
> 3 insert into table (field) values ('foo') returning tableid into
> id;
> 4 dbms_output.put_line(id);
> 5 end;
> 6 /
>
> How can I run this through cx_oracle and get the id stored in a
> variable so I can use it?
>
> Many thanks.
>
> Kellie Hobbs, UC Berkeley
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting langua=
ge
> that extends applications into web and mobile media. Attend the live webc=
ast
> and join the prime developer group breaking into this new coding territor=
y!
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D110944&bid=3D241720&dat=
=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: kellie h. <kel...@ya...> - 2006-03-22 21:02:41
|
It works! Great! Thank you.
Kellie
--- Anthony Tuininga <ant...@gm...> wrote:
> Sure. Its quite straightforward but not covered by the DB API.
>
> # this creates the bind variable for use by Oracle
> idVar = cursor.var(cx_Oracle.NUMBER)
>
> # execute the statement exactly as you normally would,
> # binding the variable you just created above
> cursor.execute("""
> insert into SomeTable (
> SomeOtherColumn
> ) values (
> :someValue
> ) returning Id
> into :id""",
> id = idVar,
> someValue = "SomeValue")
>
> # get the value after the statement is executed
> id = idVar.getvalue()
>
> Hope that explains things.
>
> On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > Hello,
> >
> > I am trying to get back the id of a record after inserting it into
> the
> > table. My Oracle database has a trigger that automatically creates
> the
> > id on insert. The PL/SQL statement that works for me is:
> > SQL> declare id int;
> > 2 begin
> > 3 insert into table (field) values ('foo') returning tableid
> into
> > id;
> > 4 dbms_output.put_line(id);
> > 5 end;
> > 6 /
> >
> > How can I run this through cx_oracle and get the id stored in a
> > variable so I can use it?
> >
> > Many thanks.
> >
> > Kellie Hobbs, UC Berkeley
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> > that extends applications into web and mobile media. Attend the
> live webcast
> > and join the prime developer group breaking into this new coding
> territory!
> >
>
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
|
|
From: kellie h. <kel...@ya...> - 2006-04-03 21:07:31
|
Hello,
Thanks for your help so far. Now can you tell me how to pass the table
name and columns to be updated to the execute statement?
Thanks again.
Kellie Hobbs
--- Anthony Tuininga <ant...@gm...> wrote:
> Sure. Its quite straightforward but not covered by the DB API.
>
> # this creates the bind variable for use by Oracle
> idVar = cursor.var(cx_Oracle.NUMBER)
>
> # execute the statement exactly as you normally would,
> # binding the variable you just created above
> cursor.execute("""
> insert into SomeTable (
> SomeOtherColumn
> ) values (
> :someValue
> ) returning Id
> into :id""",
> id = idVar,
> someValue = "SomeValue")
>
> # get the value after the statement is executed
> id = idVar.getvalue()
>
> Hope that explains things.
>
> On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > Hello,
> >
> > I am trying to get back the id of a record after inserting it into
> the
> > table. My Oracle database has a trigger that automatically creates
> the
> > id on insert. The PL/SQL statement that works for me is:
> > SQL> declare id int;
> > 2 begin
> > 3 insert into table (field) values ('foo') returning tableid
> into
> > id;
> > 4 dbms_output.put_line(id);
> > 5 end;
> > 6 /
> >
> > How can I run this through cx_oracle and get the id stored in a
> > variable so I can use it?
> >
> > Many thanks.
> >
> > Kellie Hobbs, UC Berkeley
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> > that extends applications into web and mobile media. Attend the
> live webcast
> > and join the prime developer group breaking into this new coding
> territory!
> >
>
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
|
|
From: Anthony T. <ant...@gm...> - 2006-04-03 21:43:49
|
You require a string like the one in the example. You cannot pass the
table name and columns to update as bind variables, if that is what
you are trying to get at. :-)
On 4/3/06, kellie hobbs <kel...@ya...> wrote:
> Hello,
>
> Thanks for your help so far. Now can you tell me how to pass the table
> name and columns to be updated to the execute statement?
>
> Thanks again.
>
> Kellie Hobbs
>
> --- Anthony Tuininga <ant...@gm...> wrote:
>
> > Sure. Its quite straightforward but not covered by the DB API.
> >
> > # this creates the bind variable for use by Oracle
> > idVar =3D cursor.var(cx_Oracle.NUMBER)
> >
> > # execute the statement exactly as you normally would,
> > # binding the variable you just created above
> > cursor.execute("""
> > insert into SomeTable (
> > SomeOtherColumn
> > ) values (
> > :someValue
> > ) returning Id
> > into :id""",
> > id =3D idVar,
> > someValue =3D "SomeValue")
> >
> > # get the value after the statement is executed
> > id =3D idVar.getvalue()
> >
> > Hope that explains things.
> >
> > On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > > Hello,
> > >
> > > I am trying to get back the id of a record after inserting it into
> > the
> > > table. My Oracle database has a trigger that automatically creates
> > the
> > > id on insert. The PL/SQL statement that works for me is:
> > > SQL> declare id int;
> > > 2 begin
> > > 3 insert into table (field) values ('foo') returning tableid
> > into
> > > id;
> > > 4 dbms_output.put_line(id);
> > > 5 end;
> > > 6 /
> > >
> > > How can I run this through cx_oracle and get the id stored in a
> > > variable so I can use it?
> > >
> > > Many thanks.
> > >
> > > Kellie Hobbs, UC Berkeley
> > >
|
|
From: kellie h. <kel...@ya...> - 2006-04-03 21:53:43
|
That was what I was trying to get at.. oh well.
Thanks.
Kellie
--- Anthony Tuininga <ant...@gm...> wrote:
> You require a string like the one in the example. You cannot pass the
> table name and columns to update as bind variables, if that is what
> you are trying to get at. :-)
>
> On 4/3/06, kellie hobbs <kel...@ya...> wrote:
> > Hello,
> >
> > Thanks for your help so far. Now can you tell me how to pass the
> table
> > name and columns to be updated to the execute statement?
> >
> > Thanks again.
> >
> > Kellie Hobbs
> >
> > --- Anthony Tuininga <ant...@gm...> wrote:
> >
> > > Sure. Its quite straightforward but not covered by the DB API.
> > >
> > > # this creates the bind variable for use by Oracle
> > > idVar = cursor.var(cx_Oracle.NUMBER)
> > >
> > > # execute the statement exactly as you normally would,
> > > # binding the variable you just created above
> > > cursor.execute("""
> > > insert into SomeTable (
> > > SomeOtherColumn
> > > ) values (
> > > :someValue
> > > ) returning Id
> > > into :id""",
> > > id = idVar,
> > > someValue = "SomeValue")
> > >
> > > # get the value after the statement is executed
> > > id = idVar.getvalue()
> > >
> > > Hope that explains things.
> > >
> > > On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > > > Hello,
> > > >
> > > > I am trying to get back the id of a record after inserting it
> into
> > > the
> > > > table. My Oracle database has a trigger that automatically
> creates
> > > the
> > > > id on insert. The PL/SQL statement that works for me is:
> > > > SQL> declare id int;
> > > > 2 begin
> > > > 3 insert into table (field) values ('foo') returning tableid
> > > into
> > > > id;
> > > > 4 dbms_output.put_line(id);
> > > > 5 end;
> > > > 6 /
> > > >
> > > > How can I run this through cx_oracle and get the id stored in a
> > > > variable so I can use it?
> > > >
> > > > Many thanks.
> > > >
> > > > Kellie Hobbs, UC Berkeley
> > > >
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
|
|
From: Steve S. <ssw...@ve...> - 2006-04-03 22:51:22
|
One way to get it as dynamic as you want would be to create a stored =20
procedure in the database that accepts the table, column, and value, =20
then generates the SQL as a string and runs it via EXECUTE IMMEDIATE.
Of course, you could just as easily construct the SQL string in =20
Python and run via execute()...
Also, if your trigger is just generating IDs, you could just make use =20=
of the nextval property of a sequence object and generate the ID as =20
part of the insert, or as a separate select before the insert.
Just my $0.02...
On Apr 3, 2006, at 5:53 PM, kellie hobbs wrote:
> That was what I was trying to get at.. oh well.
>
> Thanks.
>
> Kellie
>
> --- Anthony Tuininga <ant...@gm...> wrote:
>
>> You require a string like the one in the example. You cannot pass the
>> table name and columns to update as bind variables, if that is what
>> you are trying to get at. :-)
>>
>> On 4/3/06, kellie hobbs <kel...@ya...> wrote:
>>> Hello,
>>>
>>> Thanks for your help so far. Now can you tell me how to pass the
>> table
>>> name and columns to be updated to the execute statement?
>>>
>>> Thanks again.
>>>
>>> Kellie Hobbs
>>>
>>> --- Anthony Tuininga <ant...@gm...> wrote:
>>>
>>>> Sure. Its quite straightforward but not covered by the DB API.
>>>>
>>>> # this creates the bind variable for use by Oracle
>>>> idVar =3D cursor.var(cx_Oracle.NUMBER)
>>>>
>>>> # execute the statement exactly as you normally would,
>>>> # binding the variable you just created above
>>>> cursor.execute("""
>>>> insert into SomeTable (
>>>> SomeOtherColumn
>>>> ) values (
>>>> :someValue
>>>> ) returning Id
>>>> into :id""",
>>>> id =3D idVar,
>>>> someValue =3D "SomeValue")
>>>>
>>>> # get the value after the statement is executed
>>>> id =3D idVar.getvalue()
>>>>
>>>> Hope that explains things.
>>>>
>>>> On 3/22/06, kellie hobbs <kel...@ya...> wrote:
>>>>> Hello,
>>>>>
>>>>> I am trying to get back the id of a record after inserting it
>> into
>>>> the
>>>>> table. My Oracle database has a trigger that automatically
>> creates
>>>> the
>>>>> id on insert. The PL/SQL statement that works for me is:
>>>>> SQL> declare id int;
>>>>> 2 begin
>>>>> 3 insert into table (field) values ('foo') returning tableid
>>>> into
>>>>> id;
>>>>> 4 dbms_output.put_line(id);
>>>>> 5 end;
>>>>> 6 /
>>>>>
>>>>> How can I run this through cx_oracle and get the id stored in a
>>>>> variable so I can use it?
>>>>>
>>>>> Many thanks.
>>>>>
>>>>> Kellie Hobbs, UC Berkeley
>>>>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by xPML, a groundbreaking scripting
>> language
>> that extends applications into web and mobile media. Attend the live
>> webcast
>> and join the prime developer group breaking into this new coding
>> territory!
>> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=110944&bid$1720&dat=12164=
2
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting =20
> language
> that extends applications into web and mobile media. Attend the =20
> live webcast
> and join the prime developer group breaking into this new coding =20
> territory!
> http://sel.as-us.falkag.net/sel?=20
> cmd=3Dlnk&kid=3D110944&bid=3D241720&dat=3D121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-------------------------------
Steve Swartzlander
ssw...@ve...
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X