Re: paging

From: Anders Altberg (x_pragma_at_telia.com)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 16:27:55 +0200

Here's a solution
lcFilter = ""
SELECT * FROM Table ORDER BY columnx TOP 20 WHERE columnx => ?lcFilter
Note what is in the bottom record of columnx, it could be lastname or
cust_no . Put that value in lcFilter
SELECT * FROM Table ORDER BY columnx TOP 20 WHERE columnx => ?lcFilter
will now return the following 20
-Anders

"jorge" <jor70@mail.com> wrote in message
news:39160c64.0408230252.13503995@posting.google.com...
> I think a solution could be something like
>
> select generateKey() as NewKey, * from table
> where cCondition
> order by cOrder
> group by cGroup
> having between ( NewKey, FirstRecordOfPage,
> FirstRecordofPage+ItemsPerPage )
>
> The problem is that the "Visual FoxPro Driver" not let me do something
> like that in my execute string
>
> Jorge
>
>
> "Olaf Doschke" <b2xhZi5kb3NjaGtlQHQtb25saW5lLmRl.strconv.14@t-online.de>
wrote in message news:<cgat8f$chp$05$1@news.t-online.com>...
> > > I used in mysql "select * from mytable order by FieldNumberNN limit
> > > 21,20 " to bring next 20 records starting at row 21
> > > I already noticed the clause limit is not supported, anyone can point
> > > me how to get the same result in foxpro without getting all the table
> > > first ?
> >
> > I've tested, that the following would not read in the whole table
> > first:
> >
> > select 0
> > use (home()+"foxcode") && just an example-table (about 1000 records)
> > select * from foxcode where between(Recno(),1,20)
> > select * from foxcode where between(Recno(),21,40)
> > select * from foxcode where between(Recno(),41,60)
> > ...
> >
> > To test, if really only few records are read over network
> > I did the following trick:
> >
> > PUBLIC oWatcher
> > oWatcher = CREATEOBJECT("Watcher")
> > DEFINE CLASS Watcher AS Custom
> > PROCEDURE setstepon()
> > SET STEP ON
> > RETURN .T.
> > ENDPROC
> > ENDDEFINE
> >
> > Paste that to the command window, select it
> > with the mouse and then "execute selection"
> > with the context menu (right click).
> >
> > Now you can use the Watcher to watch what
> > happens during the select with
> > select * from foxcode where between(Recno(),5,7);#
> > and oWatcher.setstepon()
> >
> > Since the setstepon()-method always returns .T. this
> > won't affect the result set.
> >
> > It doesn't matter, if the debugger tells you
> > "source not available", just count how
> > many interruptions you get this way. It's
> > not 1000, although it's one more than you
> > would expect: For the three records with
> > recno 5,6 and 7 it's four interruptions, not
> > three.
> >
> > I've somehow get used to it, there always
> > seems to be one more call than you've got
> > records in the result, that's normal. Don't
> > ask me why.
> >
> > But be aware: for more complex SQL Recno()
> > can evaluate some record number of a table you
> > don't expect. Also calling Recno() with the appro-
> > priate table name - in this case Recno("foxcode") -
> > will not work. In this case I've seen the table foxcode
> > opened again with the Alias "B" during the select.
> > This way foxpro takes care, not to move the record
> > pointer in the alias "foxcode", which was open
> > intially.
> >
> > Another obvious disadvantage: if you have some
> > other ordering, like order by Abbrev, you won't
> > get the 5th, 6th and 7th record of that order, as
> > Recno() is always the physical record number
> > and doesn't reflect any ordering by order clauses
> > or set indexes.
> >
> > If you can't live without "real" paging, you'd
> > probably be better off staying at the mySQL
> > database and use SQL Passthrough to get
> > your data for a FoxPro Frontend, although
> > you may loose more advantages not using
> > foxpro data than you get back from a mySQL
> > backend, for example those dirty tricks I just
> > showed off ;-).
> >
> > Bye, Olaf.

Loading