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