There's a new functionality in pyodbc 2.1.3, that can be used to
implement limitby on MSSQL (http://code.google.com/p/pyodbc/issues/
detail?id=16). Specifically, there is a new function skip() that skips
forward on a cursor. Thus limitby(min, max) can be implemented as
conn =pyodbc.connect()
conn.execute(SELECT TOP max .....).skip(min)
Since limitby is most likely to be used for paging through a table, or
a relation, it may be worthwhile to implement this (for all databases)
as a class, that could be stored in the session object. The class
would remember the beginning and end of the retrieved page. When next
page is retrieved, it would add a WHERE condition.
Say we wanted
...select( ...., orderby(name), limitby(0,20))
The class would remember what name would be for the first and 20-th
value (say namemin,namemax) and when user wants
... select(...., orderby(name), limitby(21,40))
it would issue instead the query
... select((....) AND (name > namemax), orderby(name), limitby(0,20))
I don't know if this is possible to implement, but it seems more
efficient than other approaches.
--Ondrej
On 24. Dec., 17:43 h., mdipierro <mdipie...@cs.depaul.edu> wrote:
> Thank you. I will send another round of tests.
>
> Massimo
>
> On Dec 24, 10:16 am, Mike <michael.mcgreg...@gmail.com> wrote:
>
> > Hi Massimo -
>
> > They all threw errors. I listed the errors and how I fixed them below.
> > All of this was done directly in SQL SERVER 2005, but no test data.
>
> > Thanks for taking a look at this! I hope this info helps...
>
> > --****************
> > --STATEMENT 1
> > --****************
>
> > --ERROR
> > --Msg 8120, Level 16, State 1, Line 1
> > --Column 't1.id' is invalid in the select list because it is not
> > contained in either an aggregate function or the GROUP BY clause.
>
> > --FIXED
> > SELECT t1.name
> > FROM t1
> > WHERE t1.id>0
> > GROUP BY t1.name ORDER BY t1.name;
>
> > --COMMENTS
> > --Removed t1.id from the SELECT
>
> > --****************
> > --STATEMENT 2
> > --****************
>
> > --ERROR
> > --Msg 156, Level 15, State 1, Line 2
> > --Incorrect syntax near the keyword 'GROUP'.
>
> > --FIXED
> > SELECT t2_id, t2_t1, t2_name, t1_id, t1_name
> > FROM (SELECT t2.id AS t2_id,
> > t2.t1 AS t2_t1,
> > t2.name AS t2_name,
> > t1.id AS t1_id,
> > t1.name AS t1_name,
> > ROW_NUMBER() OVER(ORDER BY t1.name) AS w_rown
> > FROM t2, t1
> > WHERE t1.id=t2.t1) AS derived_table
> > WHERE w_rown>=1 AND w_rown<2
> > GROUP BY t2_id, t2_t1, t2_name, t1_id, t1_name
> > ORDER BY t1_name;
>
> > --COMMENTS
> > --I don't think you want the GROUP BY in the OVER ()??.
> > --I switched the WHERE clauses for the derived table and the outer
> > query. That provides the join information to the inner query and the
> > outer query selects the set of rows requested
> > --Ambiguous column names come into play since the t1 and t2 table
> > aliases are only available in the derived table. To work around that I
> > had to give each column in the derived table a unique alias. But those
> > would probably need to be converted back to table.column (vs.
> > table_column) before returning the set to the user
> > --Finally each non-aggregate column in the select list has to be in
> > the group by.
>
> > --****************
> > --STATEMENT 3
> > --****************
>
> > --ERROR AND COMMENTS SAME AS STATEMENT 2
> > --FIXED
> > SELECT t1_id, t1_name, t2_id, t2_t1, t2_name
> > FROM (SELECT t1.id AS t1_id,
> > t1.name AS t1_name,
> > t2.id AS t2_id,
> > t2.t1 AS t2_t1,
> > t2.name AS t2_name,
> > ROW_NUMBER() OVER(ORDER BY t1.name) AS w_rown
> > FROM t1 LEFT JOIN t2 ON t1.id=t2.t1 ) AS derived_table
> > WHERE w_rown>=1 AND w_rown<2
> > GROUP BY t1_id, t1_name, t2_id, t2_t1, t2_name
> > ORDER BY t1_name;
>
> > On Dec 24, 4:53 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > Making some progress but I need your help.
>
> > > Given this:
>
> > > db.define_table('t1',SQLField('name'))
> > > db.define_table('t2',SQLField('t1',db.t1),SQLField('name'))
>
> > > Are these valid MSSQL Statements?
>
> > > 1) db(db.t1.id>0)._select(orderby=db.t1.name,groupby=db.t1.name)
>
> > > 'SELECT t1.id, t1.name FROM t1 WHERE t1.id>0 GROUP BY t1.name ORDER BY
> > > t1.name;'
>
> > > 2) db(db.t1.id==db.t2.t1)._select
> > > (orderby=db.t1.name,groupby=db.t1.name,limitby=(1,2))
>
> > > 'SELECT t2.id, t2.t1, t2.name, t1.id, t1.name FROM (SELECT t2.id,
> > > t2.t1, t2.name, t1.id, t1.name, ROW_NUMBER() OVER( GROUP BY t1.name
> > > ORDER BY t1.name) AS w_rown FROM t2, t1 WHERE w_rown>=1 AND w_rown<2)
> > > WHERE t1.id=t2.t1 GROUP BY t1.name ORDER BY t1.name;'
>
> > > 3) db()._select(db.t1.ALL,db.t2.ALL,left=db.t2.on
> > > (db.t1.id==db.t2.t1),orderby=db.t1.name,groupby=db.t1.name,limitby=
> > > (1,2))
>
> > > 'SELECT t1.id, t1.name, t2.id, t2.t1, t2.name FROM (SELECT t1.id,
> > > t1.name, t2.id, t2.t1, t2.name, ROW_NUMBER() OVER( GROUP BY t1.name
> > > ORDER BY t1.name) AS w_rown FROM t1 LEFT JOIN t2 ON t1.id=t2.t1 WHERE
> > > w_rown>=1 AND w_rown<2) GROUP BY t1.name ORDER BY t1.name;'
>
> > > If not, how should they be fixed (SQL)?
>
> > > Massimo
>
> > > On Dec 23, 4:59 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > > I think so. This is what we do with Oracle. Tomorrow.
> > > > Thank you for the example. It helps.
>
> > > > Massimo
>
> > > > On Dec 23, 1:26 pm, Mike <michael.mcgreg...@gmail.com> wrote:
>
> > > > > Hi Everyone,
>
> > > > > As the books notes,limitbyisn't fully supported by mssql since the
> > > > > TOP command always starts at index 0.
>
> > > > > So to implement paging I ended up writing my own SQL to mimic the
> > > > >limitbybehavior. The main example I used was
> > > > >fromhttp://www.15seconds.com/issue/070628.htmandrequiresSQL2005's
> > > > > ROW_NUMBER function.
>
> > > > > My web2py controller snippet:
>
> > > > > #build the SQL query
> > > > > query = "SELECT " + select_cols + " FROM (SELECT ROW_NUMBER() OVER
> > > > > (ORDER BY "+sidx+" "+sord+") AS rownum, " + select_cols + " FROM "+
> > > > > from_tbl + where_clause
> > > > > #close the inner query and finish the outer query by using the
> > > > > rownum() to return only the rows requested by the
> > > > > user
> > > > > query = query + ") AS tbl1 WHERE rownum >= "+str(first_rownum)+"
> > > > > AND rownum < "+str(last_rownum)+" ORDER BY " + sidx + " " + sord
>
> > > > > This produces something to the effect of...
> > > > > SELECT person, income
> > > > > FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
> > > > > rownum, person, income FROM Salaries) AS Salaries1
> > > > > WHERE rownum >= 5 AND rownum <= 9
> > > > > ORDER BY income
>
> > > > > Note that the row numbers start with 1 (not 0) by default.
>
> > > > > My question is could this method be incorporated into the DAL? I've
> > > > > peeked at the sourcecode by I must admit it's a bit over my head
> > > > > still. If it is possible I'm willing to do my best to help with a
> > > > > patch but would need a fair amount of help.
>
> > > > > Thanks!
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
