I've just been looking through the code in mssql.py and the change mentioned in the changeset I mentioned isn't there anymore. I also can't see that's it's been abstracted to a parent class. Is there a possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the EXEC keyword in order to return result sets for MSSQL stored procedures, or if there would be a better approach. Thanks. On May 8, 11:24 am, Daniel <daniel.watr...@gmail.com> wrote: > Michael, > > I just found this > thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html > which corresponds to this > changeset:http://www.sqlalchemy.org/trac/changeset/4159 > > It seems that this issue has come up in the past. I've tried the > following modified query: > result = conn.execute('EXEC claim_highest_priority_work') > which should satisfy the regexp, but it still produces the closed > cursor error. > > Not sure if this helps you help me... > > On May 8, 11:09 am, Daniel <daniel.watr...@gmail.com> wrote: > > > Michael, > > > I'm not sure if this helps, but I've noticed that if my stored > > procedure returns all null values, then I can fetch them. If they are > > non-null values I get an error: > > > [Dbg]>>> result = conn.execute('claim_highest_priority_work') > > [Dbg]>>> print result.fetchone() > > Traceback (most recent call last): > > File "<interactive input>", line 1, in <module> > > File "C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py", line > > 1668, in fetchone > > self.connection._handle_dbapi_exception(e, None, None, > > self.cursor, self.context) > > File "C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py", line > > 931, in _handle_dbapi_exception > > raise exc.DBAPIError.instance(statement, parameters, e, > > connection_invalidated=is_disconnect) > > ProgrammingError: (ProgrammingError) Attempt to use a closed cursor. > > None None > > [Dbg]>>> result = conn.execute('claim_highest_priority_work') > > [Dbg]>>> print result.fetchone() > > (None, None, None, None, None) > > > Any idea why this would be? > > > On May 8, 10:46 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > > > > Daniel wrote: > > > > > Thanks Michael, > > > > > When I try that it produces this SQL > > > > SELECT claim_highest_priority_work() AS claim_highest_priority_work_1 > > > > > and this error > > > > 'claim_highest_priority_work' is not a recognized built-in function > > > > name. > > > > this is more of an MSSQL "how to" issue then. on most databases, running > > > a function and returning results looks like "SELECT function()". On > > > oracle, its "SELECT function() FROM DUAL". What is it on MSSQL ? > > > > > On May 8, 10:20 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > > > >> Daniel wrote: > > > > >> > Hello, > > > > >> > I've created a stored procedure in MSSQL. I'm not sure how to use it > > > >> > in SQLAlchemy. The stored procedure is called > > > >> > 'claim_highest_priority_work' and I can call it directly as follows: > > > > >> > result = conn.execute('claim_highest_priority_work') > > > > >> > I know it runs because I see the result in the database, but I'm not > > > >> > sure how to access the return values? They exactly match one of the > > > >> > tables I've defined. > > > > >> > What's the right way to map/execute a stored procedure and > > > >> > subsequently access what it returns? > > > > >> you probably want to select from it. an expression construct which > > > >> achieves this would be: > > > > >> select([func.claim_highest_priority_work()]) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~----------~----~----~----~------~----~------~--~---
