Re: [sqlalchemy] callproc support
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
Re: [sqlalchemy] callproc support
Michael Bayer wrote: On Mar 22, 2010, at 3:48 PM, Kevin Wormington wrote: 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). well you're right there so figure out what is different about parameters versus your tuple there. a good candidate is, parameters is probably a list, not a tuple. parameters is an empty list coming into ProcedureProxy.cursor_execute. Somehow the parameters aren't making it from the engine execute to cursor_execute - which probably has to do with my modification of the @compile decorator but my Python skills are not that great yet to know what to change. cursor.execute accepts either a list or tuple Thanks, 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.
Re: [sqlalchemy] callproc support
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.
Re: [sqlalchemy] callproc support
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
Re: [sqlalchemy] callproc support
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
Re: [sqlalchemy] Re: @comparable_using
After fixing my cutpaste problem I just get DB2 sql errors from the sample code. I'm going to turn on some more logging in db2 and see what is actually reaching it. Kevin Matthew wrote: I got around this by switching to declarative and declaring my property like this: _id = column_property(Column(id,Integer,primary_key=True), comparator_factory=some_comparator_factory()) And then used @synonym_for for the getter: @synonym_for(_id) @property def id(self): return some_function(self._id) I'm still not sure what was wrong with my original code, so would be interested in any comments, for academic reasons. Thank you! Matthew -- 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.
Re: [sqlalchemy] callproc support
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? 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.
Re: [sqlalchemy] callproc support
Michael Bayer wrote: On Mar 21, 2010, at 5:44 PM, Kevin Wormington wrote: 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. well that would imply a way of doing it with execute(). if the SA dialect crashes but not the DBAPI then the SA dialect has a bug. Sort of: the DB2 side had to be changed from CREATE PROCEDURE to CREATE FUNCTION so they aren't quite equivalent functionality especially if it were a complex SP which mine is not. Also, I was successful with the function using ibm_db not ibm_db_dbi ... but ibm_db_dbi used ibm_db so one would think it should work. 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. its likely behaving the same as your execute scenario above and still would have problems. I will give this a try. Would this be thread-safe under Pylons? yeah there is no shared state there. -- 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.
Re: [sqlalchemy] callproc support
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
Re: [sqlalchemy] callproc support
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=-9 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
Re: [sqlalchemy] callproc support
It appears this is some issue with ibm_db_dbi. I tried it using just the ibm_db_dbi calls and no SA and get the same error msg: CLI0150E Driver not capable. Explanation: The operation is valid but not supported by either the driver or the data source. User response: Specify a valid operation. 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=-9 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
Re: [sqlalchemy] callproc support
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=-9 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
Re: [sqlalchemy] callproc support
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=-9 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