Thanks for pointing to the Oracle code Philip - very interesting
point, & given the seriousness of this for my implementation, I
appreciate the speedy reply :)
We are indeed using SQL Server 2005, & ROW_NUMBER had caught my eye,
but it seemed to involve re-ordering how the Select statement was
being constructed within SQLAlchemy's mssql.py in a way that I didn't
fully understand...
This following style of select statement in MSSQL appears most similar
in functionality to the LIMIT/OFFSET approach used by the pagination
code (except for the fact that with MSSQL, the OVER call requires a
column to order by, which the pagination code was not specifying in
its current LIMIT/OFFSET form):
WITH alias AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY idColumn) AS 'RowNumber'
FROM myTable
)
SELECT *
FROM alias
WHERE RowNumber BETWEEN startRowNumber AND endRowNumber ;
Though frankly, it seems to me like a bug in SQLAlchemy
implementation, if it works for other engines...still working on it,
so if anyone has solved this, I'd love to hear from you :)
On Jul 10, 3:28 pm, Philip Jenvey <[EMAIL PROTECTED]> wrote:
> 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
"pylons-discuss" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---
