Take a look at stored procedure "SampleCDs_Paging_UDF" at http://www.aspfaq.com/show.asp?id=2120
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Doug Hughes Sent: Thursday, February 23, 2006 12:43 PM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit I've got a paging mechanism built into the Iterator right now. So long as the Iterator is cached (which it is inside a record (so long as the record is cached)) you can page with that. But there are still some cases where that's not a good solution. IE: 30000 records being returned. This is where a nicely done limit function would be nice. Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cody Caughlan Sent: Thursday, February 23, 2006 12:28 PM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit Well, it gets even trickier too. To correctly do paging you also need to know how many rows your query would have returned *without* the TOP/LIMIT clause in there (so you know how many pages to draw). This is where it gets really complicated. MySQL: SELECT SQL_CALC_FOUND_ROWS id,name FROM users LIMIT 30,10 Will you get the next 10 rows starting from row 30. And then you can run "SELECT FOUND_ROWS()" and it will return the complete number of rows your query would have returned *without* the LIMIT clause, e.g. "238" or whatever. MSSQL is even tricker. To even do offsets you have to do nested TOPs as described here: http://rosca.net/writing/articles/serverside_paging.asp To get the complete number of rows you have to take your full query and do a COUNT(*) with all other criteria in the query being equal. MSSQL is definitely tricker than MySQL when it comes down to this. If anyone knows of a better way to do paging with MSSQL I would love to hear about it. With that being said, we were developing an application that returned hundres of pages (with a page size of 20) and since there was so much calculations in the SELECT clauses we needed a way to cache these results as the users went from page to page (but were still navigating the same result set, even with column sorting). Anyways, we came up with a fast and efficient way to do this so if anyone is interested in hearing that, let me know. /Cody -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Doug Hughes Sent: Thursday, February 23, 2006 9:13 AM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit Eww, I'm not diggin' that. I'll think on this some more. I'll also think on your suggestion Chris. The Oracle solution is the hardest. (Why the heck is Oracle so well respected and so wonkey at the same time?!) Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bowden, Beth (NIH/NIEHS) [E] Sent: Thursday, February 23, 2006 11:37 AM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit If your sql includes a sort, "SELECT * FROM foo WHERE rownum <= n order by z" won't work well because the where clause is applied prior to the order by clause. This will work pretty uniformly for Oracle: "select * from (SELECT * FROM foo order by z) WHERE rownum <= n" Beth Bowden Phone: 919-541-3355 FAX: 919-541-3687 E-Mail: [EMAIL PROTECTED] Address: NIEHS, MD EC-03, P.O. BOX 12233, Research Triangle Park, NC 27709 -----Original Message----- From: Chris Blackwell [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 11:39 AM To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit Hi Doug, Yeah I think that would be a good idea, if you can find a way to implement it. From a little bit of googling it seems the syntax does vary a lot. Mysql: SELECT * FROM foo LIMIT n PGSql: SELECT * FROM foo LIMIT n MSSQL: SELECT TOP n * FROM foo Oracle: SELECT * FROM foo WHERE rownum <= n If you can find a way to do that, maybe the MySQL (and PGSql) implementation could include the optional offset value. query.setMaxRows(n [, offset]) wouldn't break anything ;) Keep up the good work Cheers, Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Doug Hughes Sent: 23 February 2006 16:07 To: [email protected] Subject: RE: [Reactor For CF] maxrows and limit As it regards to limiting, I may well change the maxrows to a db-specific mechanism. However, I won't add the full range of paging that the limit operator allows for in mysql. So, for all dbs, the maxRows would run a db-specific TOP or LIMIT (or whatever) to return only the maxRows number of rows. Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sean Corfield Sent: Thursday, February 23, 2006 10:59 AM To: [email protected] Subject: Re: [Reactor For CF] maxrows and limit On 2/23/06, Marlon Moyer <[EMAIL PROTECTED]> wrote: > They support it by other means. I know mssql supports TOP, which is very > much like LIMIT, but it does not support OFFSET, which, when it all comes > down to it is very important with db based paging. Reactor currently does not support limiting results returned by a query in the DB-specific ways you mention. However, it does now support paging on large record sets (assuming you wanted to page thru all 13,000 rows). -- Sean A Corfield -- http://corfield.org/ Got frameworks? "If you're not annoying somebody, you're not really alive." -- Margaret Atwood -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
