On Tuesday 30 October 2007 08:40:37 Joel Pearson wrote:
> I'm developing a TurboGears / SQLAlchemy application with multiple
> deployments that use either MySQL or Microsoft SQL Server, as specified by
> the customer. So far, all of the code has worked well on both databases,
> which has been a huge time saver for me.
>
> I recently tried out the @paginate() decorator, and while I was pleased
> with the results, I quickly discovered that it does not work with MSSQL,
> raising the following exception:
>
> InvalidRequestError: MSSQL does not support LIMIT with an offset
>
> I've come across this sort of limitation before, and the solutions tend to
> be either inefficient or complicated. For my needs, though (paging through
> less than 100 records), an inefficient solution would work just fine.
> Fortunately, I was able to create a workaround with just a few lines of
> code and a line in the configuration file.
>
> First, I added a new variable in the [global] section of my dev.cfg file:
>
> paginate.simulate_offset=True
>
> I then modified paginate.py in my turbogears installation. By default,
> @paginate() works the same as before, making use of both "limit" and
> "offset" in the database (or their equivalents). But if the variable
> "paginate.simulate_offset" exists in the config file and evaluates to True,
> then @paginate() uses "limit" in the database ("TOP" in MSSQL), but
> simulates "offset" by skipping over the results until it reaches the
> requested records.
>
> Obviously, paging through a thousand records would be slow, but it's plenty
> fast for the small result sets I use. And in my situation, a page that
> loads slowly is better than a page that never loads at all. I realize,
> however, that in other situations it would be better to have one page
> "crash" rather than overload the database-- that's why this workaround is
> off by default. (I briefly considered naming the config file variable
> "paginate.I_promise_not_to_complain_if_paginate_runs_slowly", in order to
> emphasize the trade off, but that seemed like too much typing. ;-)
>
> It would be very helpful to me if this patch were accepted. While I've only
> tried it with SQLAlchemy and MSSQL, I expect it to work with SQLObject as
> well. In addition, it should allow SQLALchemy users to use @paginate() with
> Access and MaxDB databases, which also lack support for "offset".
ORACLE doesn't even know limit - you can fake that using "ROWNUM < x", but the
offset won't work either.
So I'm in favor of making the use of limit + offset configurable. But actually
turn them off totally I'd prefer, because of above mentioned limits of other
DBs.
Diez
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" 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/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
