Solved

proper use of DB::connect using PEAR

Posted on 2006-07-14
3
645 Views
Last Modified: 2012-05-05
i am implementing a medium size app in php using mysql/PEAR.  it's design is loosely based on the orielly book book "php and mysql".  yes, i'm pretty new at mysql.

each page of the authors example app begins with $conn = DB::connect($connString,true) [sic] but there is no $conn->disconnect().   sometimes my app gets a compliant (error) that i have exceeded too many connections.

in looking at the PEAR doc, i believe the 2nd param in the connect() call is incorrect, but my original coding had it that way which resulted in no complaints (???))

assuming one script per page of html output page:
1.  is it proper usage to DB::connect() in each script?
2. can i save the object returned from that connect() and use it on other pages/scripts?  is that the right thing to do?
3. or should each  page/script start with DB::connect() and end with disconnect()?

saving the connection object like this
   $_SESSION["connection"] = DB::connect($connStr)
then using it later, like this
   $query = "SELECT blah blah blah..."
   $result = $_SESSION["connection"]->query($query)
seems to work on some pages (in my development environment, anyway, using php4 - the actual hosting site uses php5)

thoughts?

thanks
0
Comment
Question by:prevostpilot
  • 2
3 Comments
 
LVL 8

Expert Comment

by:Autogard
ID: 17113355
>>1.  is it proper usage to DB::connect() in each script?

Yes, that works fine.

>>2. can i save the object returned from that connect() and use it on other pages/scripts?  is that the right thing to do?

Actually, in PHP database connections are meant to last the life of a page and a new connection should be established with each page.  In older versions of PHP they actually had something called a persistent connection which could be used over various pages, but I think they are phasing it out in the newer versions.

>>3. or should each  page/script start with DB::connect() and end with disconnect()?

There really isn't much need to use the same connection on another page.

# Somewhere near the beginning
$dbconn = DB::connect($dbsettings);
if(PEAR::isError($dbconn))
{
      die($dbconn->getMessage());
}
else
{
        # Code here

        # Somewhere at the end
        $dbconn->disconnect();
}
0
 

Author Comment

by:prevostpilot
ID: 17115080
Autogard -

thanks for the quick response.  just one last clarification:

>> >>3. or should each  page/script start with DB::connect() and end with disconnect()?
>> 
>> There really isn't much need to use the same connection on another page.

is the disconnect() needed?   if not, what causes the disconnect? "inactivity" timeout? does mysql timeout and disconn or does PEAR do the disconn?

a day or two later i ran the exact same app (with a connect at the top of each script), and had no problem.
why do you suppose i got the "too many connections" error?  thnking back, i'm pretty sure the error came from my
  if (DB::isError($result))
            trigger_error($result->getMessage(), E_USER_ERROR);   // trigger_error() is a custom handler
tho i could be mistaken at this point - perhaps it came from within PEAR - cant be sure as i didn't save the error msg.

tnx

0
 
LVL 8

Accepted Solution

by:
Autogard earned 150 total points
ID: 17115864
>>is the disconnect() needed?

From: http://opensource.apress.com/article/36/a-quick-tutorial-of-pear-db
DB::disconnect
boolean disconnect ()
This is, obviously, the function to use when disconnecting from a database. It is always good practice to use the disconnect function. In PHP, the end of a script should automagically do a disconnect, but I wouldn’t rely on it. Use it just to be safe.
To use this function, we will reference the database object that was returned from an inital connect statement:
$db = DB::connect($dsn, false);
$db->disconnect();

Like it says the disconnect "should" occur, but maybe wasn't in your script.  Always safe, and good practice to put the actual disconnect call there.  If it doesn't disconnect then the mysql timeout should kick in.  Check your "connect_timeout" setting for mysql.  But if you get a lot of connections you can get to your limit real quick.  There is a mysql variable that controls max connections too.

Official doc for disconnect is here: http://pear.php.net/manual/en/package.database.db.db-common.disconnect.php
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hibernate Not Rolling Back When something fails in a stored procedure 2 50
MySQL Error 3 39
[MYSQL]: Delete is very slow 4 54
PHP: Insert Data into MySQL 5 12
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now