On Mar 21, 2010, at 9:33 PM, Kevin Wormington wrote:
> As a test I altered the compile_procedure and the call to cursor.callproc and
> do get the values back from the stored procedure from the "print res" before
> the return. I get exceptions about the cursor being close when I try to
> access the ResultProxy object returned though. I think it's because the
> callproc returns just the results and not a cursor/set.
ah well, the result proxy closes the cursor when there's no cursor.description,
i.e. that theres no results to fetch.
The workarounds are getting uglier here, but you can do this:
result = cursor.callproc(<stuff>)
context.callproc_result = result
then on the outside when you get your result, context is there:
result = conn.execute(myprocedure...)
print result.context.callproc_result
>
> def compile_procedure(element, compiler, **kw):
> return "%s" % (element.name)
>
> class ProcedureProxy(ConnectionProxy):
>
> def cursor_execute(self, execute, cursor, statement, parameters, context,
> executemany):
> """Intercept low-level cursor execute() events."""
> if context and isinstance(context.compiled.statement, procedure):
> engine.logger.info("calling stored procedure: %s", statement)
> if hasattr(cursor, 'callproc'):
> subid = 1000
> balance = 0
> res = cursor.callproc(statement, (subid,balance))
> print res
> return res
> # return cursor.callproc(statement, parameters)
> else:
> return cursor.execute(statement, parameters)
> else:
> return execute(cursor, statement, parameters, context)
>
>
>
> Kevin Wormington wrote:
>> I was able to get it working from just the ibm_db_dbi interface - the actual
>> call has to be:
>> cursor.callproc('BILLING.subscriber_balance',(subid,balance))
>> these both cause the sql errors:
>> cursor.callproc('BILLING.subscriber_balance',(1000,0))
>> cursor.callproc('BILLING.subscriber_balance',(1000,balance))
>> The compiler.process(literal you suggested results in "CALL
>> BILLING.subscriber_balance(?,?)(?,?)". I tried changing it to just
>> return element.name and get just "CALL BILLING.subscriber_balance( )" sent
>> to the DB. How can I get the return cursor.callproc(statement, parameters)
>> to actually have the (subid,balance) in the parameters?
>> Kevin
>> Michael Bayer wrote:
>>> On Mar 21, 2010, at 8:07 PM, Kevin Wormington wrote:
>>>
>>>> I just modified the compile to return just the procedure name and the
>>>> cursor.callproc to send the statement and the two parameters as a tuple
>>>> and the DB2 receives the correct SQL:
>>>>
>>>> CALL BILLING.subscriber_balance(?,?)
>>>>
>>>> But I get the following back from ibm_db_dbi:
>>>>
>>>> return cursor.callproc(statement, (1000,0))
>>>> File
>>>> "/usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py",
>>>> line 973, in callproc
>>>> result = self._callproc_helper(procname, parameters)
>>>> File
>>>> "/usr/local/lib/python2.6/dist-packages/ibm_db-1.0-py2.6-linux-i686.egg/ibm_db_dbi.py",
>>>> line 951, in _callproc_helper
>>>> raise _get_exception(inst)
>>>> ibm_db_dbi.DatabaseError: ibm_db_dbi::DatabaseError: Describe Param
>>>> Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00
>>>> SQLCODE=-99999
>>>>
>>>> Looks like it doesn't like my values perhaps not being actual variables to
>>>> bind.
>>>
>>> heres how to get the binds to come through, in case thats the problem:
>>>
>>> from sqlalchemy import literal
>>>
>>> @compiles(procedure)
>>> def compile_procedure(element, compiler, **kw):
>>> return "%s(%s)" % (element.name,
>>> ",".join(compiler.process(literal(expr)) for expr in element.args))
>>>
>>> "literal" is basically the same as "bindparam". when you send it to the
>>> compiler the value becomes part of the compiled's params, and the generated
>>> string is "?" or whatever paramstyle is.
>>>
>>> usually in SQLA core we do the conversion to bindparam in the constructor
>>> of the clause construct.
>>>
>>>
>>>
>>>
>>>
>>>
>>>>
>>>> Kevin Wormington wrote:
>>>>> 2010-03-21 18:40:49,656 INFO sqlalchemy.engine.base.Engine.0x...cb0c
>>>>> calling stored procedure: BILLING.subscriber_balance(1000,?)
>>>>> INFO:sqlalchemy.engine.base.Engine.0x...cb0c:calling stored procedure:
>>>>> BILLING.subscriber_balance(1000,?)
>>>>> It looks correct there but all that is getting logged is the statement
>>>>> which has had all the arguments combined together by the compile. I
>>>>> think that the 1000,? should remain as parameters to cursor.callproc
>>>>> instead of everything going into statement. ibm_db_dbi.callproc is
>>>>> probably adding the second set of () since it doesn't think there are any
>>>>> arguments. Just not sure how to fix it.
>>>>> Michael Bayer wrote:
>>>>>> On Mar 21, 2010, at 7:34 PM, Kevin Wormington wrote:
>>>>>>
>>>>>>> From DB2 logs it appears that the following is what's getting sent to
>>>>>>> DB2 and resulting in the error:
>>>>>>>
>>>>>>> CALL BILLING.subscriber_balance(1000,?)( )
>>>>>>>
>>>>>>>
>>>>>>> This is using:
>>>>>>>
>>>>>>> print engine.execute(procedure("BILLING.subscriber_balance", subid,
>>>>>>> "?"))
>>>>>>>
>>>>>>> Any idea how I can get rid of the extra set of ()'s?
>>>>>> what does the logging at the Python level say ? whatever goes into
>>>>>> callproc() is the most you can control. that recipe is allowing you to
>>>>>> control the string output exactly.
>>>>>>
>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>> Kevin
>>>>>>>
>>>>>>> Michael Bayer wrote:
>>>>>>>> On Mar 21, 2010, at 12:50 PM, Kevin Wormington wrote:
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> I am using SQLAlchemy 0.5.8 with the ibm_db_sa (DB2) adapter and I am
>>>>>>>>> wanting to add simple session.callproc support so that I can get
>>>>>>>>> results from stored procedures that don't use a select or table
>>>>>>>>> format. I haven't done any development on SA before but at first
>>>>>>>>> glance it appears that I would just need to add the method in the ibm
>>>>>>>>> dialect class and into engines/base.py to raise an exception for
>>>>>>>>> other dialects that don't support it. Can it really be that easy?
>>>>>>>>> Also, if I implement this will it be a possibility to get it
>>>>>>>>> integrated into future releases?
>>>>>>>> So here's the things i ask/state about this:
>>>>>>>> 1. IBM's dialect works with 0.5 ? it says its only for 0.4.
>>>>>>>> 2. the callproc requirement here assumes that there's absolutely no
>>>>>>>> way to make this work using plain execute(). For example with
>>>>>>>> cx_oracle you can use outparams with plain execute(). I don't know
>>>>>>>> the details here for DB2, but if callproc() really provides
>>>>>>>> functionality that is impossible otherwise, then yes we need to
>>>>>>>> examine ways to call it.
>>>>>>>> 3. the way this feature would work would be:
>>>>>>>> from sqlalchemy import procedure
>>>>>>>> result = engine.execute(procedure.name_of_my_procedure(arg1, arg2,
>>>>>>>> ...))
>>>>>>>> or with plain text or such, you can do this:
>>>>>>>> result =
>>>>>>>> engine.connect().execution_options(callproc=True).execute("my
>>>>>>>> procedure...")
>>>>>>>> 4. execution_options is only availble in 0.6. Similarly, any "built
>>>>>>>> in" version of this feature would be for 0.6.
>>>>>>>> 5. I'd like to have IBM's dialect on 0.6. Two ways to do this would
>>>>>>>> either be to write a new dialect from scratch using their DBAPI, or to
>>>>>>>> just port their 0.4/0.5 dialect to 0.6. I'd prefer the latter, but I
>>>>>>>> don't know the licensing details. If IBM were to give me the "green
>>>>>>>> light" I'd just wrap their dialect into SQLAlchemy core where all the
>>>>>>>> other ones are.
>>>>>>>> 6. in 0.5 or any, the functionality of #3 can be achieved using public
>>>>>>>> API points, here's a demo:
>>>>>>>> from sqlalchemy.ext.compiler import compiles
>>>>>>>> from sqlalchemy.interfaces import ConnectionProxy
>>>>>>>> from sqlalchemy.sql.expression import ClauseElement
>>>>>>>> from sqlalchemy import create_engine
>>>>>>>> class procedure(ClauseElement):
>>>>>>>> supports_execution = True
>>>>>>>> _autocommit = False
>>>>>>>> _execution_options = {}
>>>>>>>> def __init__(self, name, *args):
>>>>>>>> self.name = name
>>>>>>>> self.args = args
>>>>>>>> @compiles(procedure)
>>>>>>>> def compile_procedure(element, compiler, **kw):
>>>>>>>> return "%s(%s)" % (element.name, ",".join(str(expr) for expr in
>>>>>>>> element.args))
>>>>>>>> class ProcedureProxy(ConnectionProxy):
>>>>>>>> def cursor_execute(self, execute, cursor, statement, parameters,
>>>>>>>> context, executemany):
>>>>>>>> """Intercept low-level cursor execute() events."""
>>>>>>>> if context and isinstance(context.compiled.statement,
>>>>>>>> procedure):
>>>>>>>> engine.logger.info("calling stored procedure: %s", statement)
>>>>>>>> if hasattr(cursor, 'callproc'):
>>>>>>>> return cursor.callproc(statement, parameters)
>>>>>>>> else:
>>>>>>>> return cursor.execute(statement, parameters)
>>>>>>>> else:
>>>>>>>> return execute(cursor, statement, parameters, context)
>>>>>>>> engine = create_engine('sqlite://', proxy=ProcedureProxy(), echo=True)
>>>>>>>> print engine.execute(procedure("pragma table_info", "foo"))
>>>>>>>>
>>>>>>>>> Kevin
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>>> Groups "sqlalchemy" group.
>>>>>>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>>>>>>> To unsubscribe from this group, send email to
>>>>>>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>>>>>>> For more options, visit this group at
>>>>>>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>>>>>>
>>>>>>> --
>>>>>>> You received this message because you are subscribed to the Google
>>>>>>> Groups "sqlalchemy" group.
>>>>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>>>>> To unsubscribe from this group, send email to
>>>>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>>>>> For more options, visit this group at
>>>>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>>> To unsubscribe from this group, send email to
>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
