DBD ODBC Nested Queries

[menkes]

Is it possible to do nested queries with DBD::ODBC?

I have been taking the first query, throwing the results into a hash, then looping through the hash for the next query. It just seems there should be a more efficient way.

If I just nest the queries I get an error that the execute statement is already busy...

This is what causes the error:

my $query = "exec sp_getSomeList";
my $sth = $dbh->prepare($query);
$sth->execute();
while(my $row = $sth->fetchrow_hashref) 
{
    my $query2 = "exec sp_getSomeDetails $row->{'ID'}";
    my $sth2 = $dbh->prepare($query2);
    $sth2->execute;
    while(my $row2 = $sth2->fetchrow_hahsref)
    {
        ## do dome stuff
    }
}

Anyone know a more betterer way than creating a hash from the first result set then looping through that hash?

 

[Rieekan]

Yes, I've done this from time to time when I need a quick and dirty way to get data.

- Rieekan

 

[fishiface]

This is a server-end restriction rather than a DBI restriction. Have you tried opening two connections?

f

"As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilkes

 

[duncdude]

Isn't this the same as performing a NESTED SELECT?

example(s)

http://www.databasejournal.com/sqletc/article.php/26861_1402351_4

Kind Regards
Duncan

 

[duncdude]

... whereby you need to run a SELECT first to gather results - and then use the outcome of that query to integrate in the outer (first part) SELECT. This can all be wrapped up in 1 statement

i'm probably trying to tell you how to suck eggs though! sorry if i am...


Kind Regards
Duncan

 

[menkes]

Thanks for the responses.

Two connections would work. Not sure if there are any negatives to that....?

A nested Select statement would work just fine....if I was doing a select. Since I am running stored procedures, I cannot do that. Of course, I could just create a new SP (hmmmm).

 

[fishiface]

Twin connections use a bit of server resource and can intefere with COMIT/ROLLBACK operations (as you have two sessions). You wouldn't be able to access temporary tables created in one session from the other. Other than that it's not a real problem. If your data sizes are reasonable, you can always store the result of the outer query in a data structure and then finish the first query, prepare the second and then run the second query independently, using the stored values. For large data volumes you need to consider timing as well as resource issues for this approach.

f

"As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilkes

 

[stevexff]

I'd go with creating a new SP that does what you want...