Should I manually close DB Connection and Statement's cursor's after a fetch?

Question

I'm building something with Silex and Doctrine DBAL using a Mysql database.

I was wondering if it considered best practice to close the cursor of a statement and database connection after one's done it. For example:

$sql = '
    SELECT
        `id`, `userid`, `name`, `content` 
    FROM 
        `pages` p 
    WHERE 
        p.`userid` in (?)'; 

$stmt = $conn->executeQuery(
    $sql,
    [$userIds],
    [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
);

$rows = $stmt->fetchAll();

$stmt->closeCursor();

$conn->close();

I can't find any examples of this. Both the Silex and Doctrine doc's don't use the statements but they're there for a reason right?

Answer 1

$stmt->closeCursor() appears to just be a wrapper around PDOStatement::closeCursor() and would allow you to reexecute the query if you needed to:

PDOStatement::closeCursor() frees up the connection to the server so that other SQL statements may be issued, but leaves the statement in a state that enables it to be executed again.

This method is useful for database drivers that do not support executing a PDOStatement object when a previously executed PDOStatement object still has unfetched rows. If your database driver suffers from this limitation, the problem may manifest itself in an out-of-sequence error.

In the example you posted, you are not executing multiple queries and instead closing the connection immediately afterwords. Thus, calling it is not beneficial. If you're really concerned about memory usage, setting $stmt to null should be more effective.

Connection::close() appears to just sets the connection on the object to null:

/**
 * Closes the connection.
 *
 * @return void
 */
public function close()
{
    $this->_conn = null;
    $this->_isConnected = false;
}

It frees up some memory, thus making it a better practice than not calling it. However I would expect the benefit to be negligible as it will be closed when the script terminates anyway.