Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re: DBI disconnect database errors

by EvanCarroll (Chaplain)
on Nov 16, 2009 at 06:54 UTC ( #807375=note: print w/replies, xml ) Need Help??

in reply to DBI disconnect database errors

Upgrade Apache::Session, your version is old. This was reported in ticket rt://32148 fixed in 1.85_01 and noted in the CHANGES per CPAN convention. Also, for the love of god don't call finish on handles which you intend to read all the data from (it will be called for you). Please review the DBI docs on this.

Evan Carroll
The most respected person in the whole perl community.

Replies are listed 'Best First'.
Re^2: DBI disconnect database errors
by Anonymous Monk on Nov 16, 2009 at 08:21 UTC
    I got this code from Paul, the author of Perl and MySQL for the web...

    I did scan the DBI module documentation, and do see the finish calls are different than what I do...

    However, I am not reading from the database while it is connected, I store the data in a hashref:
    $sth = $dbh->prepare(qq{select * from `table`}); $sth->execute(); my $_somevar = $sth->fetchrow_hashref(); $sth->finish();
    $sth = $dbh->prepare(qq{select * from `table`}); $sth->execute(); while(my $_somevar = $sth->fetchrow_hashref()) { my $_someid = $_somvar->{primarykeyfield}; $sth2 = $dbh->prepare(qq{select * from `table1` where `primarykeyfield +` = ?}); $sth2->execute($_someid); $_thisvar = $sth2->fetchrow_hashref(); $sth2->finish(); # now I have data from table in $_somvar # and data from table1 in $_thisvar } $sth->finish();
    So, I call finish on them after I'm done reading from there, but not before. That way I don't leave any connections open, is that not correct?


      If you want to track down which handles DBI is warning about see visit_handles which would allow you to loop through them and see which are Active and what SQL they are running.

      OK, being able to look at that code, I wold explicitly NOT to do that. I would not explicitly prepare something for a single select, use selectrow_hashref instead, and put in the sql an explicit `LIMIT 1`. Also, using '*' should be reserved, when you want all the rows in the table. This should hold true if one of those rows is a 50 meg block of text.

      Also, in your second example you could just as well do that in one query right? Through a join. This saves work on the DB.

      $dbh->selectrow_hashref( 'SELECT t1.* FROM table AS t0' . ' JOIN table1 AS t1' . ' USING ( primarykeyfield )' );
      Or, if you wanted precisely the same as above, which will render the row from table1 random because the lack of a sort order on table, you can still do that in one statement
      $dbh->selectrow_hashref( 'SELECT t1.* FROM table AS t0' . ' WHERE t1.primarykeyfield IN ( SELECT primarykeyfield FROm tabl +e LIMIT 1 ) );

      Evan Carroll
      The most respected person in the whole perl community.
        Thanks for that...

        I normally just use all the data in both, if I only need info from one table, I do it like this:
        # I need the members information for their profile update... #$_un carries the username they are logged in as: $sth = $dbh->selectrow_array(qq{select * from `members` where `USERNAM +E` = ? LIMIT 1}); $sth->execute($_un); my $_mbrInfo = $sth->fetchrow_hashref(); $sth->finish(); # then go build the html page and form for them to update their profil +e, prepopulating the data that is already in their
        That is how I get their data, however, if I need multiple tables of information, since I store data in multiple tables, I get each of them:
        $sth = $dbh->selectrow_array(qq{select * from `members` where `USERNAM +E` = ? LIMIT 1}); $sth->execute($_un); my $_mbrInfo = $sth->fetchrow_hashref(); $sth->finish(); # Got member record, now get their billing address: $sth = $dbh->prepare(qq{select * from `shippingAddresses` where `mid` += ?}); $sth->execute($_mbrInfo->{mid}); my $_addy = $sth->fetchrow_hashref(); $sth->finish();
        That is how I get all the info I need, I don't usually get all the fields if I only need a few... If I only need one or two fields I do it this way:
        # Get Members Id, Name, Phone, Email: my($_mbrId,$_mbrName,$_mbrPhone,$_mbrEmail) = $dbh->selectrow_array(qq +{select `mid`,`name`,`phone`,`email` from `members` where `USERNAME` += ?}, undef, $_un);
        Or if I need data only out of another table, but it is based on the members id, and I only have the useranme, I do it like this:
        # Get address: $sth = $dbh->selectrow_array(qq{select * from `shippingAddresses` wher +e `Id` = (select `mid` from `members` where `USERNAME` = ?)}); $sth->execute($_un); my $_addy = $sth->fetchrow_hashref(); $sth->finish();
        That is how I would get just the fields I need... Since usernames are unique, I don't need to put a limit of 1 on it since there should only be one matching record.

        That is how I handle those in my programming.

        I am always open to new ideas though. I am currently learning Ajax to make some of the things I do much better, I am loving what I am learning! That is a GREAT language.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://807375]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (11)
As of 2017-09-21 09:11 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (244 votes). Check out past polls.