RE: setoptioin works, sam, 01-11-2007
Follow-up: N rows per page with multiple key columns
by Roger Harris, February 3, 2007 13:55
I'm not sure if I made this clear, so:
Since CGIs can't easily make use of SQL cursors to step through a table (because CGI is a single request/response protocol) the easiest way to use ODBscript for showing N rows per page and then have a "more" link to see the next N requires that you have one or more columns that define unique rows, and that you sort the rows in that order. With that, the general technique is to use <% SETOPTION SQL_MAX_ROWS = N %> to specify the number of rows you want; use a SELECT statement that has an ORDER BY clause that sorts the rows by the unique key; and add a WHERE clause condition that says to select only rows that are greater than the last row displayed. Then, your "more" link can pass the last displayed column values back into the same script for use in the WHERE clause.
In the example files, a single column is used, but it is possible to extend that to multiple columns, provided that the set of columns identifies a unique row. You said you had three columns that you want to sort on and you also have a column with a unique row ID. So, lets add that ID column to the sort key (even if you're not displaying that column) so we can use the techique. Your script would then look something like this (where A, B, and C are your primary sort columns, and I'll assume those are text columns and the ID is numeric):<% DEFAULT prevA="", prevB="", prevC="", prevID=0;The small "trick" here is that the WHERE clause for all columns except the last one have a "greater than or equal" condition (because there could be more rows with those values and we don't want to skip them), and the last column (your ID column) only has a "greater than" condition (because if it was greater than or equal, we would reselect the last row already displayed). Regardless of the number of columns required to make the row unique, the general idea is use "greater than or equal" for all conditions EXCEPT the last one, which should just be "greater than." Also note that in the link we are passing values such as prevA=$url($A$), which works because those column variables such as $A$ will have the values from the last row selected when the EACHROW loop terminates. (Also note I used the URL function, prevA=$url($A$), to avoid protential problems with special characters in those text columns, but that's not an issue with numeric columns.)
SETOPTION SQL_MAX_ROWS = 20; SELECT A, B, C, ID, X, Y, Z FROM Table WHERE A >= '$prevA$' AND B >= '$prevB$' AND C >= '$prevC$' AND ID > $prevID$ ORDER BY A, B, C, ID;
EACHROW; %> (Format the result table) <% ENDROW; %>