[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. On Feb 13, 2008, at 4:50 PM, Paul Johnston wrote: 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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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 ? On Feb 14, 2008, at 12:05 PM, Rick Morrison wrote: 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? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. On Thu, Feb 14, 2008 at 12:11 PM, jason kirtland [EMAIL PROTECTED] wrote: 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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
On Feb 13, 2008, at 12:03 PM, John Keith Hohm wrote: 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. 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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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. 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. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: executing stored procedure which returns rows
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 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
