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/sqlalchemy@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 ?
>
> On May 11, 9:32 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>> 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 wrote:
>>
>> > Any reply on this? Should I submit a new bug report?
>>
>> > On May 8, 11:49 am, Daniel <daniel.watr...@gmail.com> wrote:
>> >> 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
-~----------~----~----~----~------~----~------~--~---
