[sqlalchemy] Re: SQLAlchemy and scrollable cursors
On Feb 19, 7:23 pm, Rick Morrison rickmorri...@gmail.com wrote: Unfortunately, AFAICT, MS-SQL does not have an OFFSET clause (it uses TOP instead of LIMIT). How does SQLA handle this situation? For mssql2005 and higher, (those versions of mssql that support window functions using OVER, row_number(), rank(), etc.), we simulate an OFFSET by wrapping the query with an outer query, and using where row_number() = offset and row_number() offset + limit For mssql 2000 and lower, you'll be limited to use of LIMIT, and the mssql dialect will know to use TOP instead of the LIMIT syntax. For this to work in SA 0.4 and 0.5, you'll need to add the engine keyword has_window_funcs=1 to your connection string. From what I understand, SA 0.6+ will sniff out the mssql version and automatically toggle the behavior. Rick Thanks for the info - much appreciated. Frank --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy and scrollable cursors
On Feb 19, 2009, at 12:23 PM, Rick Morrison wrote: For this to work in SA 0.4 and 0.5, you'll need to add the engine keyword has_window_funcs=1 to your connection string. From what I understand, SA 0.6+ will sniff out the mssql version and automatically toggle the behavior. well, in 0.4/0.5, if you dont set the flag, and try to do LIMIT/ OFFSET, it raises an exception. So we just took the whole thing out. The effect is, if you try to do LIMIT/OFFSET and the DB doesn't support it, it raises an exception. Which is close enough to the previous behavior minus the 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy and scrollable cursors
Unfortunately, AFAICT, MS-SQL does not have an OFFSET clause (it uses TOP instead of LIMIT). How does SQLA handle this situation? For mssql2005 and higher, (those versions of mssql that support window functions using OVER, row_number(), rank(), etc.), we simulate an OFFSET by wrapping the query with an outer query, and using where row_number() = offset and row_number() offset + limit For mssql 2000 and lower, you'll be limited to use of LIMIT, and the mssql dialect will know to use TOP instead of the LIMIT syntax. For this to work in SA 0.4 and 0.5, you'll need to add the engine keyword has_window_funcs=1 to your connection string. From what I understand, SA 0.6+ will sniff out the mssql version and automatically toggle the behavior. Rick --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
