On Mar 22, 2010, at 3:48 PM, Kevin Wormington wrote:
> That's getting closer. Now if I could just figure out how to get it to use
> the parameters that I'm actually passing in engine.execute. It appears
> ibm_db_sa botches the sql (to "CALL BILLING.subscriber_balance()") if use the
> commented out cursor.callproc(statement, parameters).
well you're right there so figure out what is different about "parameters"
versus your tuple there. a good candidate is, "parameters" is probably a
list, not a tuple.
>
>
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.interfaces import ConnectionProxy
> from sqlalchemy.sql.expression import ClauseElement
> from sqlalchemy import create_engine
> from sqlalchemy import literal
>
> 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" % (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
> result = cursor.callproc(statement, (subid,balance))
> # result = cursor.callproc(statement, parameters)
> context.callproc_result = result
> return result
> else:
> return cursor.execute(statement, parameters)
> else:
> return execute(cursor, statement, parameters, context)
>
>
> engine =
> create_engine('ibm_db_sa://db2inst1:somegreatpassw...@x.x.x.x:xxx/BILLING',
> proxy=ProcedureProxy(), echo=True)
>
> subid=1000
> balance=0
> res = engine.execute(procedure("BILLING.subscriber_balance", subid, balance))
> print res.context.callproc_result
>
>
> Michael Bayer wrote:
>> 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.
>
--
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.
