sybperl-l Archive

From: "Albert Ng" <albert at ANGIS dot USYD dot EDU dot AU>
Subject: Re: Queries n rows at a time
Date: Jul 16 1998 12:03AM

Sorry but this sounds exactly like what I was asking about a few weeks
back. And what I ended up doing was do a simplier SQL to get the PK
out of the table 1st. And once that's in a perl array, we just get N number
of rows and then do the real SQL on these values.

A better way that I would like to do is to create a view with the SQL and
then add 1 more column to it which will have a sequencial integer in it. So
all we need to do is say select from view where rowid is between i and i+N

Alb
-----Original Message-----
From: Simon Yaminian 
To: SybPerl Discussion List 
Cc: sy50027@milton.com 
Date: Thursday, July 16, 1998 1:54 AM
Subject: Re: Queries n rows at a time


>
>> From owner-SYBPERL-L@listproc.net Wed Jul 15 11:37:50 1998
>> >Received: from as01.us by ltcm.com (8.7.1/1.32(sec)-LK3)
>>         id LAA25600; Wed, 15 Jul 1998 11:20:00 -0400
>> Date: Wed, 15 Jul 1998 11:20:01 -0400
>> From: Ashutosh Joglekar 
>> Mime-Version: 1.0
>> To: SybPerl Discussion List 
>> Subject: Re: Queries n rows at a time
>> Content-Transfer-Encoding: 7bit
>> X-Listprocessor-Version: 8.2.07 -- ListProc(tm) by CREN
>>
>> Perhaps I have misunderstood the question but isn't it enough to run the
query
>> through $dbh->dbcmd, dbsqlexec (not thru $dbh->sql or siblings). Then run
a
>> dbnextrow loop and in the loop, work with 200 rows at a time, then get
the next
>> 20 rows, etc.
>>
>> Something like :
>>
>> $dbh->dbcmd($query);
>> $dbh->dbsqlexec;
>> $dbh->dbresults;
>> my ($count) = 0;
>> while (($col1, $col2, $col3) = $dbh->dbnextrow)
>> {
>>     $count++;
>>     # save $col1, $col2, $col3 into some structure
>>     if ($count == 200)
>>     {
>>         $count = 0;
>>         # process the batch of rows
>>     }
>> }
>>
>>
> Thank you for the thought.
>
>The hangup is that when the query is put together in the dbcmd, the
>dbsqlexec call does not return until the query is completed and this
>may take a while. The catch is to break the dbsqlexec call for instance
>into a set of "current" and "next" exec calls so we can have a break
>in the processing time while also having some data to work with. Again
>put all query semantics aside. The idea may seem complicated but it is
>in really simple. The key is to maintain a reference point (by Sybperl)
>in the submitted query.
>
>Regards
>Simon Yaminian
>