Semi-OT: GETNEXT methodology in SQL?

Dan Coutu coutu at snowy-owl.com
Tue Jun 9 17:51:49 PDT 2009


One method that jumps to mind immediately requires that you keep a
counter value and then use it along with a syntax like this:

select * from mytable limit 0,1;

You then generate a sequence of select statements where you increment
the zero by one (this is where the counter comes in.) This will let you
walk through every record in the table, one at a time, with no unique
index being used.

Hmm, a little research shows that MySQL 5 now supports variables and
stored procedures. So you ought to be able to do this:

set @counter = 0;
set @tablesize := select count(*) from mytable;
while @tablesize > @counter
    select * from mytable limit @counter,1;
end while;

I hope this helps. Note that the record count is zero based and
@tablesize is one based.

Dan

Fairlight wrote:
> Okay, mark your calendars, because I'm going to actually be complimentary
> to filePro.
>
> fP makes "stepping through" your records dead simple, whether they contain
> unique data fields or not.  GETNEXT is a bit of a Godsend.
>
> Unfortunately, I need to emulate this behaviour in mysql, and I'm coming
> up blank.  Anyone have experience with both and know the methodology for
> emulating a GETNEXT loop in mysql?  I need a data worm that can work on
> a record-by-record basis on a table that was designed poorly with no
> guaranteed unique values in place--in EVERY SINGLE TABLE.  So I need a
> general methodology for emulating this, not one based on one table schema.
>
> I've already got a feeler out in a more SQL-oriented community.  However,
> the methodology I need to use is so fP-centric that I figured it made sense
> to ask here, as anyone here that's ever done a LOOKUP/GETNEXT loop should
> know exactly what it is I'm wanting to do.  So it made sense to ask here as
> well.
>
> Thanks in advance to anyone that knows both sides of the road and can help.
>
> mark->
>   



More information about the Filepro-list mailing list