[DB-SIG] Re: [DB-SIG] http://www.python.org/topics/database/DatabaseAPI-2.0.ht ml

[Matthew T. Kromer]

Anthony Tuininga wrote:

>There is no DB independent way of doing this that I am aware of. In
>Oracle you use the "returning" clause. I am not familiar with DCOracle2
>but I suspect something similar can be done.
>
>import cx_Oracle
>
>v_Connection = cx_Oracle.connect("user/pw@tns")
>v_Cursor = v_Connection.cursor()
>v_Vars = v_Cursor.setinputsizes(p_PrimaryKeyValue = cx_Oracle.NUMBER)
>v_Cursor.execute("""
>    insert into SomeTable (
>      SomeValue
>    ) values (
>      :p_SomeValue
>    ) returning PrimaryKey into :p_PrimaryKeyValue""")
>print "PrimaryKeyValue:", v_Vars["p_PrimaryKeyValue"].getvalue()
>
>This method is also not compatible with the DB-API as the DB-API does
>not specify a method for handling output variables in anything other
>than stored procedures. If you want to conform to the DB-API, you will
>have to use a stored procedure and use the cursor method callproc()
>instead of the method above.
>  
>


DCOracle does this similarly, albeit in yet another incompatible way <smirk>

import DCOracle2
db = DCOracle2.connect('scott/tiger')
c = db.cursor()
rval = db.BindingArray(1, 45, 'SQLT_STR')
c.execute('Insert into foo values(...) returning SomeVar into :rval', 
rval=rval)

print rval[0]

BindingArrays in DCOracle2 are incredibly primative; and not exactly 
pleasant to use.  One quirk is they can't decode Oracle types for you to 
Python numbers -- so you use SQLT_STR as the format to have Oracle 
convert the value to a string instead (they really provide something 
like a raw buffer for input/output values).  Normally the Python layer 
handles all of this for you, but it doesn't understand how to do that 
for RETURNING clauses.