executing stored procedure which returns rows

[John Keith Hohm]

I'm using and loving SQLAlchemy 0.4.3dev_r4136 but I am having a
problem with (drum roll) a legacy database schema. I'm using pyodbc
on Unix.

The primary keys in a legacy table are alphanumeric and must be
generated by a MSSQL stored procedure which returns a single result
row with a single unnamed char(12). How can I execute this procedure
with SQLAlchemy?

I tried the obvious session.execute("EXEC sp_new_foo_key").fetchone()
and variations with text() and select() but I always get this error:

<class 'pyodbc.ProgrammingError'>: Attempt to use a closed cursor.

The typemap argument to text() looked like it might do what I need,
but I don't see how to use it with the unnamed result column, and I'm
not sure it would actually result in the correct execution method.

I gather this is because the mssql dialect implements
returns_rows_text() as a regexp matching just SELECT and sp_columns.
SQL Server Profiler shows the statement being executed from SQLAlchemy
as an RPC instead of a SQL batch like when I do the EXEC from SQL
Server Management Studio.

[Michael Bayer]

that's the issue. we grep for "SELECT"-like statements in order to
determine if we can close the cursor immediately. I think adding EXEC
to the ms_is_select would fix this. As far as RPC vs. SQL batch
that's determined by the DBAPI - we call cursor.execute() as opposed
to callproc(), if thats significant.

[Rick Morrison]

>  I'm using pyodbc on Unix.

<blink> You are???

This statement jumped out of the message for me. Can you please describe your setup to the list? There is a lot of interest in this configuration.

[John Keith Hohm]

I am using unixodbc-2.2.11 as packaged by Ubuntu 7.10 (gutsy) with
locally-installed freetds-0.64 (the tdsodbc Ubuntu package was 0.63
and had issues with SQL Server 2005) and locally-installed
pyodbc-2.0.52. I configured the server with tds version = 8.0 in /etc/
freetds/freetds.conf, defined the FreeTDS driver in /etc/odbcinst.ini,
and the DSN in /etc/odbc.ini; it works fine once I figured out the URL
needs to look like mssql://myuser:mypass@/mydbname?dsn=DSN_FROM_ODBCINI
(note the slash after the at symbol).

I know the documentation suggests this is not reliable, but I haven't
had any problems that I would attribute to pyodbc.

[Paul Johnston]

John,

>I am using unixodbc-2.2.11 as packaged by Ubuntu 7.10 (gutsy) with
>
>

That sounds very promising, I have been meaning to have a go at this for
a while.

Can you do me a favor and run the unit tests using your current setup?
Run alltests.py and append &text_as_varchar=1 to the dburi (a few mssql
tests rely on this). Save the stdout and stderr and send them to me.
This would really help us gauge how much work on unix support is needed.
For comparison, a run on windows with pyodbc has about 40 test failures.

Paul

[Michael Bayer]

so, does somebody want to add "EXEC" to the is_select() regexp ?

I think we should also add a flag to text() which allows this too,
along the lines of "returns_results=True".

[Rick Morrison]

Sure, I'll take care of it. Is there an easy way to side-step things like columns named 'exec', or is that just a risk we take?

[Michael Bayer]

the regexp is \s*(keywords) so it should only match EXEC as the first
thing in the string.

is the EXEC the only way to call an SP in MS-SQL ? no SELECT
<procname> ?

[jason kirtland]

Michael Bayer wrote:
> so, does somebody want to add "EXEC" to the is_select() regexp ?
>
> I think we should also add a flag to text() which allows this too,
> along the lines of "returns_results=True".

There was some talk of trying to auto-detect resultsets with cursor
inspection. My recollection from poking at it was that results were
promising on most db-apis, but server-side cursors remained to be
tested. If that can't be 100% reliable then we would definitely need a
hinting flag.

[Rick Morrison]

Only rarely is there only one way to do something in MSSQL ;-)

Stored procedures can also be called simply by name, omitting the "EXEC":

    EXEC procedure_foo <parms>  
              or
    procedure_foo  <parms>

and I believe they can also be called from within a subquery:

    select * from (procedure_foo)

I think the EXEC-in-front case is probably our 90% use-case, and users can re-write as needed.

[Rick Morrison]

This approach would be ideal, and would work with row-returning functions, etc.  but obviously depends on some rather sophisticated cooperation with the dbapi. I don't think pymssql would be up to the task, although I think the ODBC-derived dbapis might work.

[Rick Morrison]

> I think we should also add a flag to text() which allows this too,
> along the lines of "returns_results=True".

+1 on that, it would be useful as a fallback for those oddball situations.

[John Keith Hohm]

> Stored procedures can also be called simply by name, omitting the "EXEC":
>
> EXEC procedure_foo <parms>
> or
> procedure_foo <parms>

True, but as you suggested it's hardly a burden to type the EXEC.

> and I believe they can also be called from within a subquery:
>
> select * from (procedure_foo)

That would be lovely, but I can find no way to wrap a procedure in a
select. If it were possible I could have just rewritten the procedure
call as a select and matched the existing regexp.

[John Keith Hohm]

> > I think we should also add a flag to text() which allows this too,
> > along the lines of "returns_results=True".
>
> +1 on that, it would be useful as a fallback for those oddball situations.

Indeed, Microsoft SQL Server interprets myriad bespoke "SQL"
constructs which return results. Perhaps the
MSSQLDialect.reflecttable() implementation should use the flag itself
rather than special-case the regexp for sp_columns. Or it could just
use EXEC presuming it is added to the regexp.

[Don Dwiggins]

Rick Morrison wrote:
> Only rarely is there only one way to do something in MSSQL ;-)
>
> Stored procedures can also be called simply by name, omitting the "EXEC":
>
> EXEC procedure_foo <parms>
> or
> procedure_foo <parms>

True, as long as the call is the first statement in the batch;
otherwise, you need the exec.

>
> and I believe they can also be called from within a subquery:
>
> select * from (procedure_foo)

No, but mssql has the concept of "table-valued user defined function",
so you could have something like

select * from dbo.foo(@var)

-- think of it as a "parameterized view".

I agree that something like the "returns_results" hint might be a good
way to go.

--
Don Dwiggins
Advanced Publishing Technology

[Rick Morrison]

OK, it's in trunk r4159.

I've also included an implementation of LIMIT/OFFSET using row_number() that I've been using locally for the past few days. Instructions on how to enable it are in the CHANGES file.

[jason kirtland]

Rick Morrison wrote:
> This approach would be ideal, and would work with row-returning
> functions, etc. but obviously depends on some rather sophisticated
> cooperation with the dbapi. I don't think pymssql would be up to the
> task, although I think the ODBC-derived dbapis might work.

It's not that fancy: just checking for the cursor.description attribute.

There needs to be some work done in this area anyhow. I noticed that
the MySQLdb db-api crashes if a stored procedure returns multiple result
sets and nextset() isn't called for all of them... so we'd want to be
able to detect a pending resultset in any case. And support procedures
returning multiple resultsets in general.

[Michael Bayer]

you can do that as select(["*"]).select_from(func.procedure.foo()) .

[Rick Morrison]

> And support procedures returning multiple resultsets in general.

That would be great, although I think such things are pretty poor form. Years ago I worked on a legacy system that had a calc procedure returning 20+ result sets, and a variable number of them at that. What a nightmare that was trying to keep all that straight. But it happens.

[John Keith Hohm]

> >> select * from (procedure_foo)
>
> > That would be lovely, but I can find no way to wrap a procedure in a
> > select. If it were possible I could have just rewritten the procedure
> > call as a select and matched the existing regexp.
>
> you can do that as select(["*"]).select_from(func.procedure.foo()) .

Thanks, I understand that that will generate "SELECT * FROM
procedure_foo()"; the trouble is that SQL Server does not permit
selecting from procedures in that way. Happily, though, I realized I
can create a table-valued function that executes the procedure, and I
can select from the table-valued function in this way.

[Rick Morrison]

> happily, though, I realized I can create a table-valued function that executes the procedure, and I

> can select from the table-valued function in this way.

Right, table-valued functions were added in MSSQL2000, but they do have some limitations compared to regular stored procedures - notably, they cannot modify data. So they aren't a full solution for everyone.

0.4.3 has the EXEC in the is_select regexp for MSSQL, so it should work with data-returning full stored procedures as well.