How to run a stored procedure?

[Daniel]

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?

Thanks

[Michael Bayer]

you probably want to select from it. an expression construct which
achieves this would be:

select([func.claim_highest_priority_work()])

[Daniel]

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.

[Michael Bayer]

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 ?

[Daniel]

On mssql, when I run the query, either calling it by name or calling
EXEC and then the procedure name, it returns the result. The problem
is that if I try to call result.fetchall(), it give this error:
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None

I know that when I execute the query in the MSSQL environment it
returns a result set, but sqlalchemy doesn't seem to get the result
set.

[Michael Bayer]

Daniel wrote:
>
> On mssql, when I run the query, either calling it by name or calling
> EXEC and then the procedure name, it returns the result. The problem
> is that if I try to call result.fetchall(), it give this error:
> ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
> None None
>
> I know that when I execute the query in the MSSQL environment it
> returns a result set, but sqlalchemy doesn't seem to get the result
> set.

its again a DBAPI interaction issue which either some of the MSSQL folks
here could chime in to help with or if you could figure out how to get
what you want from a raw pyodbc connection....

[Daniel]

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?

[Daniel]

Michael,

I just found this thread: http://www.mail-archive.com/sqlal...@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...

[Daniel]

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.

[Daniel]

Any reply on this? Should I submit a new bug report?

[Michael Bayer]

Email on the pyodbc mailing list for instructions on how to execute a
stored procedure. The information there will guide how this is done
with SQLAlchemy.

[Daniel]

Michael,

I can execute a stored procedure from SQLAlchemy, but I can't get a
result set back out of SQLAlchemy. I've verified that the SP executes
as expected and I know that it's returning a result set. I'm
following what's been suggested on this forum
http://groups.google.com/group/sqlalchemy/browse_thread/thread/12e3360ee7efe15d
and http://www.mail-archive.com/sqlal...@googlegroups.com/msg08048.html.

What type of question do you think I should ask on the pyodbc forum?
It seems the issue is that a bug that was fixed in a previous version
of SQLAlchemy has crept back in. I might be able to fix it with a
little help, but things have changed quite a bit since the changeset
that fixed it last time(see previous post on this thread) and I'm not
sure where to start.

[Michael Bayer]

Daniel wrote:
>
> Michael,
>
> I can execute a stored procedure from SQLAlchemy, but I can't get a
> result set back out of SQLAlchemy. I've verified that the SP executes
> as expected and I know that it's returning a result set. I'm
> following what's been suggested on this forum
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/12e3360ee7efe15d
> and http://www.mail-archive.com/sqlal...@googlegroups.com/msg08048.html.
>
> What type of question do you think I should ask on the pyodbc forum?
> It seems the issue is that a bug that was fixed in a previous version
> of SQLAlchemy has crept back in. I might be able to fix it with a
> little help, but things have changed quite a bit since the changeset
> that fixed it last time(see previous post on this thread) and I'm not
> sure where to start.

this is what I want to know:

import pyodbc
connection = pyodbc.connect("connection string")
cursor = connection.cursor()

cursor.execute("EXEC my_stored_procedure")
results = cursor.fetchall()

will the above work ? or is something else needed ?

[Daniel]

Michael,

I've just found the following article about pyodbc:
http://nyc-dba.blogspot.com/2008/03/returning-rows-from-stored-procedure.html

Their suggestion to add 'SET NOCOUNT ON' to the T-SQL definition of
the stored procedure worked. With that in place I was able to run the
query as you suggested directly using pyodbc.

I have also now verified that I can run it directly from the
SQLAlchemy session as follows:
>>> result = self.session.execute('exec claim_highest_priority_work')
>>> print result
<sqlalchemy.engine.base.ResultProxy object at 0x012B6330>
>>> rows = result.fetchall()
>>> rows
[(264, 'Work Assignment')]

Thank you.

On May 11, 11:10 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> Daniel wrote:
>
> > Michael,
>
> > I can execute a stored procedure from SQLAlchemy, but I can't get a
> > result set back out of SQLAlchemy.  I've verified that the SP executes
> > as expected and I know that it's returning a result set.  I'm
> > following what's been suggested on this forum

> >http://groups.google.com/group/sqlalchemy/browse_thread/thread/12e336...
> > andhttp://www.mail-archive.com/sqlal...@googlegroups.com/msg08048.html.