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