Please disregard...cut & paste messed up the indention.
Kevin Wormington wrote:
I get the following when trying the example implemention:
Traceback (most recent call last):
File "test.py", line 6, in <module>
class procedure(ClauseElement):
File "test.py", line 15, in procedure
@compiles(procedure)
NameError: name 'procedure' is not defined
Here is the code for that class:
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))
Kevin Wormington wrote:
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.
Yes, with a few minor changes to match renamed logging components.
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.
I have tried several different ways of getting plain execute to work
including via the low-level ibm_db.execute interface with no luck. I
have also tried changing the DB2 side to be a function instead of a
stored procedure so that something like "select
myfunctionname(parameter) from SYSIBM.SYSDUMMY1" would work; this
crashes under SA but works with the low-level ibm_db. The
ibm_db.callproc() function works as expected at the low level and
appears to be AFAIK the only way to get a stored procedure to return
results for DB2. I'm using DB2 Express-C 9.7.
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.
I initially tried porting it to 0.6 but there are substantial
differences...ok more than I could fix on one bottle of Mountain Dew!
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"))
I will give this a try. Would this be thread-safe under Pylons?
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.
