Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

DBI Frustrations

by amt (Monk)
on Sep 16, 2004 at 17:35 UTC ( #391515=perlquestion: print w/replies, xml ) Need Help??
amt has asked for the wisdom of the Perl Monks concerning the following question:

Gentlemen, I'm not new with DBI by any means, but I am having the most terrible time finding what is wrong with this code.

These are the declarations of the queries. It goes to a MySQL database.

sub recursive_print_traversal { $parent = shift; # the parent $addr = "select * from address_tbl where Subnet='$parent'"; $subs = "select * from subnet_tbl where VLAN='$parent'"; $addr_str = $dbh->prepare($addr); # prepare address sele +ct $subs_str = $dbh->prepare($subs); # prepare subnet selec +t $addr_rst = $addr_str->execute(); # execute address sele +ct $subs_rst = $subs_str->execute(); # execute subnet selec +t

Later I attempt to use the MySQL queries, like so.

if ( $subs_rst == 0){ print "<tr><td colspan=2 align=center>None.</td></tr>" +; print "</table></td></tr>"; } else { while ( @row = $subs_rst->fetchrow_array ) { >> recursive_print_traversal($row[0]); } }

However, it yields the following error.

Can't call method "fetchrow_array" without a package or object referen +ce at /var/www/cgi-bin/report.cgi line 127.

The line is question is mark with ">>" above.


This problem has been solved. Curse PHP for what it has done to my Perl over the past month...

Janitored by davido: Restored deleted content. Please don't delete the contents of your nodes once the question has been resolved.

Replies are listed 'Best First'.
Re: DBI Frustrations
by gmax (Abbot) on Sep 16, 2004 at 17:43 UTC

    You are mixing a statement handler with its result.

    $subs_rst = $subs_str->execute(); # execute subnet select

    This instruction returns the execution result, i.e. if the query was executed correctly. (See the DBI docs).

    For your fetch statement you need to use $subs_str, i.e.the statement handler.

    As a side note, be aware that the execute method DOES NOT return the number of rows for a SELECT query.

    Moreover (this is not related to your problem, but it can create other unpleasant side effects) you should not interpolate variables in your queries, but use placeholders instead.

    By deleting the original content, you made this answer - and the other ones below - almost meaningless. It's a pity, because the value of a PerlMonks node is in the question as much as in the answer. Other people can benefit from your mistakes. Don't be ashame of that. Everybody makes mistakes. The important thing is to learn from them, so please put the question back in place.

     _  _ _  _  
    (_|| | |(_|><
Re: DBI Frustrations
by mpeppler (Vicar) on Sep 16, 2004 at 18:37 UTC
    Please don't remove the text of your node after it has been answered.

    It makes the thread confusing, and means that someone else reading it won't learn anything by reading it.


Re: DBI Frustrations
by Ven'Tatsu (Deacon) on Sep 16, 2004 at 17:47 UTC
    Shouldn't you be calling fetchrow_array on the statment handle, not on the result?
    while ( @row = $subs_str->fetchrow_array ) {
    Incedentaly, $subs_str and $subs_rst look so alike to my eyes that I didn't notice at first that they were even different variables.
Re: DBI Frustrations
by VSarkiss (Monsignor) on Sep 16, 2004 at 17:50 UTC

    Your variables are confusing. I can't tell if $subs_rst is global, and is thus the same one being used in the sub and outside of it. If so, you're clobbering the statement handle in the sub with the return value of execute.

    First thing I'd suggest is to use strict so you can tell if you're blowing away something....

Re: DBI Frustrations
by Zaxo (Archbishop) on Sep 16, 2004 at 17:50 UTC

    You need to call fetchrow_array in the while condition using the statement handle $subs_str (I think that's the one you mean), not the result of the previous execute.

    while ( @row = $subs_str->fetchrow_array ) { recursive_print_traversal($row[0]); }

    You are on very thin ice with your variables. In a recursive function it is important to get scoping right. Make your variables lexical with my or else localize them. The easy check for that is to make your code run under strict.

    After Compline,

Re: DBI Frustrations
by cfreak (Chaplain) on Sep 17, 2004 at 14:07 UTC
    In addition to the comments above, it also appears that you're calling the prepare statement for every element of your loop. prepare() and execute() are designed for using placeholders, you then call prepare() once for each query and pass the variables to the execute(), that way the query gets cached and you're program is both more managable and faster. Here's an example using some of your code (I took it out of the sub though):
    $addr = "select * from address_tbl where Subnet=?"; $subs = "select * from subnet_tbl where VLAN=?"; $addr_str = $dbh->prepare($addr); # prepare address select $subs_str = $dbh->prepare($subs); # prepare subnet select #sth is the statement handle from your original query while(@row = $sth->fetchrow_array()) { $addr_str->execute($row[0]); }

    If you want the recursion I suggest to make hte queries and the prepared statement handle global then your sub just executes.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://391515]
Approved by gmax
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (9)
As of 2017-05-23 12:31 GMT
Find Nodes?
    Voting Booth?