How Zend DB Manage Database Connections

Question

I am using Zend Framework for my PHP developments and here is a small function I used to execute a query. This is not about an error. The code and everything works fine. But I want to know some concept behind this.

/** 
    * Get dataset by executing sql statement
    * 
    * @param  string $sql - SQL Statement to be executed
    * 
    * @return bool 
    */
    public function executeQuery($sql)
    {
        $this->sqlStatement = $sql;

        if ($this->isDebug)
        {
            echo $sql;
            exit;
        }

        $objSQL = $this->objDB->getAdapter()->prepare($sql);

        try
        {           
            return $objSQL->execute();

        }
        catch(Exception $error)
        {

            $this->logMessage($error->getMessage() . "  SQL : " .$sql);
            return false;
        }
        return false;
    }

Bellow are unclear areas for me.

1. How Zend_Db_Table_Abstract Maintain database connections?
2. Is it creating new connection all the time when I call this function or Does it have some connection pooling?
3. I didn't write any coding to open or close database connection. So will zend framework automatically close connections?
4. If this open and close connection works all the time if I execute this function, Is there any performance issue?

Thank you and appreciate your suggestions and opinion on this.

Idiocrasy

Answer 1

Creating Connection

Creating an instance of an Adapter class does not immediately connect to the RDBMS server. The Adapter saves the connection parameters, and makes the actual connection on demand, the first time you need to execute a query. This ensures that creating an Adapter object is quick and inexpensive. You can create an instance of an Adapter even if you are not certain that you need to run any database queries during the current request your application is serving.

If you need to force the Adapter to connect to the RDBMS, use the getConnection() method. This method returns an object for the connection as represented by the respective PHP database extension. For example, if you use any of the Adapter classes for PDO drivers, then getConnection() returns the PDO object, after initiating it as a live connection to the specific database.

It can be useful to force the connection if you want to catch any exceptions it throws as a result of invalid account credentials, or other failure to connect to the RDBMS server. These exceptions are not thrown until the connection is made, so it can help simplify your application code if you handle the exceptions in one place, instead of at the time of the first query against the database.

Additionally, an adapter can get serialized to store it, for example, in a session variable. This can be very useful not only for the adapter itself, but for other objects that aggregate it, like a Zend_Db_Select object. By default, adapters are allowed to be serialized, if you don't want it, you should consider passing the Zend_Db::ALLOW_SERIALIZATION option with FALSE, see the example above. To respect lazy connections principle, the adapter won't reconnect itself after being unserialized. You must then call getConnection() yourself. You can make the adapter auto-reconnect by passing the Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE with TRUE as an adapter option.

Closing a Connection

Normally it is not necessary to close a database connection. PHP automatically cleans up all resources and the end of a request. Database extensions are designed to close the connection as the reference to the resource object is cleaned up.

However, if you have a long-duration PHP script that initiates many database connections, you might need to close the connection, to avoid exhausting the capacity of your RDBMS server. You can use the Adapter's closeConnection() method to explicitly close the underlying database connection.

Since release 1.7.2, you could check you are currently connected to the RDBMS server with the method isConnected(). This means that a connection resource has been initiated and wasn't closed. This function is not currently able to test for example a server side closing of the connection. This is internally use to close the connection. It allow you to close the connection multiple times without errors. It was already the case before 1.7.2 for PDO adapters but not for the others.

More information

Caroche