Re: [cx-oracle-users] Followup: cx_oracle, returning insert id

[Steve S. <ssw...@ve...>]

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