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