need a help

--0-1551227092-1126604033=:25655
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

hello , i am a new perl programmer and i work in a nested loops that each lop excutes an sql to get a result ( 1 - M )relationship. but i faced problem: that i must finish the excute the parent sql before continuing fetching the childs results.
sql format:
-------------------------------
my ($sql)=qq
{
SELECT $fields
FROM $tables
WHERE $condition
ORDER BY $OB DESC
};

my ($sth)=$dbh->prepare($sql);
$sth->execute() || bail_out("Connt prepare retrive accounts query");
while(@arr1=$sth->fetchrow_array())
{
push(@result,[@arr1]);
#=================
my ($sql_2)=qq
{
SELECT $fields
FROM $tables
WHERE $condition depending on the parent specific result
ORDER BY $OB DESC
};

my ($sth_2)=$dbh->prepare($sql_2);
$sth_2->execute() || bail_out("Connt prepare retrive accounts query");

while(@arr2=$sth_2->fetchrow_array())
{

push(@result2,[@arr2]); }
$sth_2->finish();
#====================
}
$sth->finish();
return @result;


#============
my regards
Dakka, Rami





---------------------------------
Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.
--0-1551227092-1126604033=:25655--

Re: need a help

Three solutions:

1) Save the results of the first select in an array. This solves the problem, unless the initial query results are too large.

2) Open two database handles and let the inner select use the other handle. This also solves the problem without using
extra memory but uses an extra connection to the database.

3) Use a nested select and let the database do the work. Without knowing what database and what type of conditions you
are referring to, I do not know if this will work for you.

I prefer the solutions in reverse order is feasible.

rami doqa wrote:

> hello , i am a new perl programmer and i work in a nested loops that each lop excutes an sql to get a result ( 1 - M )relationship. but i faced problem: that i must finish the excute the parent sql before continuing fetching the childs results.
> sql format:
> -------------------------------
> my ($sql)=qq
> {
> SELECT $fields
> FROM $tables
> WHERE $condition
> ORDER BY $OB DESC
> };
>
> my ($sth)=$dbh->prepare($sql);
> $sth->execute() || bail_out("Connt prepare retrive accounts query");
> while(@arr1=$sth->fetchrow_array())
> {
> push(@result,[@arr1]);
> #=================
> my ($sql_2)=qq
> {
> SELECT $fields
> FROM $tables
> WHERE $condition depending on the parent specific result
> ORDER BY $OB DESC
> };
>
> my ($sth_2)=$dbh->prepare($sql_2);
> $sth_2->execute() || bail_out("Connt prepare retrive accounts query");
>
> while(@arr2=$sth_2->fetchrow_array())
> {
>
> push(@result2,[@arr2]); }
> $sth_2->finish();
> #====================
> }
> $sth->finish();
> return @result;
>
>
> #============
> my regards
> Dakka, Rami
>
>
> ---------------------------------
> Yahoo! for Good
> Click here to donate to the Hurricane Katrina relief effort.

--
James D. White (jdw@ou.edu)
Director of Bioinformatics
Department of Chemistry and Biochemistry/ACGT
University of Oklahoma
101 David L. Boren Blvd., SRTC 2100
Norman, OK 73019
Phone: (405) 325-4912, FAX: (405) 325-7762