ADO GetRows() example for newbies
Gustavo Cordova
gcordova at hebmex.com
Mon Mar 4 10:41:48 EST 2002
>
> 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
More information about the Python-list
mailing list