Nesting SELECT statements

heezy has asked for the wisdom of the Perl Monks concerning the following question:

Has any one else encountered problems when nesting mySQL select statements using the DBI module?

I know full well that it's possible to do something like this...(because I have and I use it)

    my $sql = qq{ SELECT * FROM slname};
    $qry = $dbhutils->prepare($sql);
    $qry->execute();
    my $slnameHTML = "";

    while (@row = $qry->fetchrow_array){
        $slname = $row[0];
        $slID = $row[1];
        $slnameHTML = $slnameHTML . "<option value=\"$slID\">$slname<\
+/option>\n";        
    }
    $qry->finish();
[download]

However if I do a similier kind of while loop but this time I use one of the pieces of data to perform another SELECT query, the DBI module whinges at me with the following error..

DBD::mysql::st fetchrow_array failed: fetch() without execute() at....
[download]

Here's the code and it appears to be quite straight forward...?

my $sql = qq{ SELECT * FROM titles ORDER BY slID, countryID, languageI
+D, title};
$qry = $dbh->prepare($sql);
$qry->execute();

while(@row = $qry->fetchrow_array){
    print $cgi->h2("Hello!!!");
    my $countryID = $row[0];
    my $languageID = $row[1];
    my $slID = $row[2];
    my $title = $row[3];

    my $countryname = &getCountryNameFromID($countryID);

    print $cgi->p("$slID");
    print $cgi->p("$countryname");
    print $cgi->p("$languageID");
    print $cgi->p("$title");
    print $cgi->p("");
}
$qry->finish();
[download]

The "&getCountryNameFromID" performs a simple SELECT query and uses and entirely different db handle altogether (same db, different Perl script included using a require)


# Takes the countryID as a parameter e.g. "12"
sub getCountryNameFromID{
    my $countryID = $_[0];
    my $sql = qq{ SELECT fullcountryname FROM countries WHERE (country
+ID="$countryID") };
    $qry = $dbhutils->prepare($sql);
    $qry->execute();
    @row = $qry->fetchrow_array;    
    my $countryname = $row[0];
    $qry->finish();
    return $countryname;
}
[download]

Bizzare?? Unexpected?? Any ideas anyone??

Note: the first time it goes through the loop it does the 5 "print $cgi->p(..." statements

Comment on Nesting SELECT statements Select or Download Code

Replies are listed 'Best First'.
Re: Nesting SELECT statements by gjb (Vicar) on Feb 06, 2003 at 22:01 UTC
It seems that the query object in both the `while` loop and the subroutine is one and the same (`$qry`) since I don't see a `my $qry;` anywhere in the subroutine. You `$qry->finish();` it in the subroutine, so you can't fetch anything anymore in the `while`. Hope this helps, -gjb-	[reply] [d/l] [select]
Re: Re: Nesting SELECT statements by heezy (Monk) on Feb 06, 2003 at 22:07 UTC
Oh my god do I feel silly! I've been looking for info on this for hours! Thank you it's all sorted now Sorry for posting such a daft question m	[reply]
Re: Nesting SELECT statements by diotalevi (Canon) on Feb 07, 2003 at 02:55 UTC
You're coding your SQL the wrong way. This isn't an answer to your question (gjb already handled that) but a barb your buggy SQL coding style. Quit it with the `SELECT ` and then fetching into an array. Either specify your column names explicitly like `SELECT countryid, languageid, slid, title` and fetch to an array OR continue to use `SELECT ` but fetch to a hash instead. You've got to break the implied link between column order in your RDBMS table and your perl code. Your current style just makes things harder than they need to be. There's a wonderful example in the DBI documentation - I'll include it just because it's so pretty `$sth->execute; my %row; $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } )); while ($sth->fetch) { # And now the same hash is re-used for every iteration. print "$row{region}: $row{sales}\n"; }` [download] Seeking Green geeks in Minnesota	[reply] [d/l] [select]