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
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.
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,
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
On Mar 22, 2010, at 4:06 PM, Kevin Wormington wrote:
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.
wheres the parameters here ?
res = engine.execute(procedure(BILLING.subscriber_balance, subid, balance))
should be engine.execute(procedure(...), {'param1':p1, 'param2':p2})
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.
--
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
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 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
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 to the
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
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. 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
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.
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
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 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
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 be
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
Re: [sqlalchemy] callproc support
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=-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
