ADO GetRows() example for newbies

[Gustavo Cordova]

> 
> sam_collett at lycos.co.uk (Sam Collett) wrote:
> 
> >How could you do pages of records (in ASP for example), you may only
> >want a few records at a time (if you have 1000 records for example)?
> >For example I may want a page with 10 records per page, with the page
> >numbers at the bottom.
> >For 100 records:
> >10 pages (10 links)
> >Start at 1 on page 1, 11 on page 2 etc)
> 
> Unfortunately, this varies from SQL provider to SQL provider. 
>  In Postgres, for example, I can say:
> 
>    SELECT * FROM users ORDER BY lastname OFFSET 20 LIMIT 10;
> 
> That fetches me records number 21 through 30.  By changing the OFFSET
> value, I change which subset I get.
> 
> SQLServer uses a different syntax.  I believe "TOP" is the SQLServer
> equivalent of LIMIT, but I don't know how to do "OFFSET".
> 
> Another alternative is to use a cursor, but again the 
> implementation vaires by backend.
>

And since the question is squarely in ASPs, I'd do the following:

1. Obtain ALL results into an array of arrays with:

import win32com.client, cPickle

	RS = win32com.client.Dispatch("ADODB.Recordset")
	RS.Open("...query...", "...connection...")
	if not RS.EOF:
		# Fetch ALL rows and transform from it's "natural" form
		# [ (A_1, A_2, ...), (B_1, B_2, ...), ...]
		# into a more useful
		# [ (A_1, B_1, ...), (A_2, B_2, ...), ...]
		# and pickle into a string.
		all_rows = cPickle.dumps(zip(*RS.getrows()))

		# Make up a random filename.
		fname = make_a_random_filename_somehow()

		# And save locally on the server.
		open(Server.MapFilename(fname),"w").write(fname)

		# AND STORE THE FILENAME.
		Session("PickleName").Value = fname

2. When you're displaying the results, read the pickle and
   display from there:

	fname = str(Session("PickleName").Value)
	all_rows = cPickle.loads(open(fname).read())

3. When you're done with the data, destroy the pickle file
   so it isn't left hanging around there:

	fname = str(Session("PickleName").Value)
      os.remove(fname)
	Session("PickleName").Value = None


This helps, because you don't have to query every time you
display the results page, and they're cached locally on a
file which can be quickly read and displayed.

#include <all-normal-security-stuff-regarding-local-files.h>

Hope this helps.

-gustavo