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.
