On Jul 9, 2007, at 9:37 PM, BruceC wrote:
> > Hi all, I am using Pylons, & SQLAlchemy (via PYODBC) to connect > through to an MSSQL database. In particular, I have been trying to use > Christoph Haas' alternative Paginator (http://workaround.org/pylons/ > paginator/paginator.html) webhelper, which looks great. > > I can get it to show pagination links for a list of records coming > from a mapped Class object, but when I try to use one of the > pagination links, I get an error, because the pagination webhelper in > Pylons sends a SELECT statement to the database, passing a LIMIT & and > OFFSET value. Unfortunately, unlike many other database engines (eg > MySQL) , SQL Server does not have OFFSET in it's SQL syntax. > > My implementation does need pagination, & I am stuck having to use SQL > Server... Has anyone managed to get pagination going on Pylons using > SQL Server as the DB engine? > > If so, can you explain how you got around the fact that MSSQL can't > handle SQL requests involving LIMIT & OFFSET? > There are a few different workarounds for the lack of offset in SQL Server, I recall a popular one being to nest your query in a couple SELECT TOPs, basically manually plucking the data out yourself. Though it looks like SQL Server 2005 now supports the more standard ROW_NUMBER() function. Are you using this version? I notice the SQLAlchemy Oracle support seems to be using ROW_NUMBER() for offset: http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/ sqlalchemy/databases/oracle.py#L557 If that's the case it could be doing roughly the same for SQL Server 05. CCing the sqlalchemy ML, might this be considered a SQLAlchemy bug? -- Philip Jenvey --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
