When Should I Use Closecursor() for Pdo Statements

Why we use PDO::closeCursor() method/function in PDO PHP?

Some databases does not support to execute and fetch the result from the next query until the previous query has the un-fetched results.

Let suppose you are executing 2 queries, the first one is in the fetching state and is not completed yet and on the same moment you want the first query to be stop in the state where it is currently in, and want to move to next query so the PDO::closeCursor() is used between the two queries to pause the first query in the same state and begin the next one.

Remember: This method is not compatible with all the PDO drivers.

Is a call to PDOStatement::closeCursor() necessary if the statement object is unset anway?

pdo_mysql_stmt_dtor() runs the same cleanup operations as pdo_mysql_stmt_cursor_closer(), so as long as the statement object is either explicitly unset or goes out of scope, the operations will always be performed.

It is therefore not strictly necessary to call closeCursor() if the statement is about to be destroyed anyway. Personally I would do it anyway as I like to be explicit for readability, but that comes down to personal stylistic preferences.

Based on the references above, this can only be said for certain about PDO_mysql - for other drivers this may not hold true.

Why closeCursor() is required for PDO rollback() to be reflected in output?

The above reply by Ryan Vincent is the correct one.

So I am specifying this to mark my question as solved.

Thanks,

Qwerty

PDO-bindParam, PDO-bindValue and PDO-closeCursor

The 'recurring' bindParam() here is not really necessary:

$thread = 0;
$modThread->bindParam(':id', $thread);

while($thread < 20)
{
    $thread++;
    $modThread->execute(); //executing with the new value, which you couldn't do with bindValue
}

You don't need a closeCursor() when there is no resultset (i.e, only with SELECT s or procedures giving results back) , but usually I've already done a fetchAll somewhere in a previous statement / row.

What is a cursor in PDO?

A cursor is nothing more than a pointer. The word has different meanings depending on the context, but it always means more or less something that points at another thing.

In the context of databases, there exist database cursors. Implicitly every result set has a cursor. You can also declare special cursors, for example in stored procedures. Usually, cursors are forward-only iterators, but some databases offer more advanced options.

PDO is not a database, so what does cursor mean in the context of PDO?

Depending on the underlying database and the query mode, PDO might prefetch(buffer) all rows into PHP memory or retrieve each row one by one from the database. There might also be multiple result sets after the execution of a single query.

closeCursor() "closes cursor" in two different ways if we can even say it closes anything at all. In case of unbuffered queries, closing means reading all remaining rows from the database server, after which the database will close the cursor itself. In case of buffered queries, PDO will free the memory associated with the result set. It also ensures that all pending results sets from the server are read and traversed.

Internally, PDO's driver will keep a cursor for buffered results. It's just a int field that points to the current index in the result set.

If you are using buffered queries, which is the default, then calling closeCursor() seems useless. If you are using unbuffered queries then calling closeCursor() seems pretty useless too... except in situations where you want to manually free up the database connection. These would be extremely rare as most of the time you are dealing with one statement at a time and buffered queries are the norm.

What is PDO::ATTR_CURSOR?

From php.net:

Selects the cursor type. PDO currently supports either PDO::CURSOR_FWDONLY and PDO::CURSOR_SCROLL. Stick with PDO::CURSOR_FWDONLY unless you know that you need a scrollable cursor.

As mentioned above, some databases support scrollable cursors. Databases that don't might still support simple forward-only cursors for result sets.

While PDO_MySQL doesn't actually support cursors, mysqli does. Let me explain how they work on MySQL example. It doesn't offer scrollable cursors, but you can ask MySQL to open a single non-scrollable read-only cursor. It works just like buffered mode on PHP side, except that the results are not fetched from the server. They are "buffered" on the server with an explicit cursor attached to it. This means that you can still execute other queries on the connection link, while the results are pending on the server-side. You can open only a single cursor at a time, which means you can't SELECT two results sets at the same time anyway. Such cursors might also be open by stored procedures, but this functionality is a little broken...

Of course, all of the above is super-advanced PDO usage. Most users will be happy with buffered result sets in PHP. Don't worry about cursors, they're usually more trouble than their worth.

pdo free result

closest method is close cursor for PDO statements.

http://us.php.net/manual/en/pdostatement.closecursor.php

$stmt = $dbh->prepare('SELECT foo FROM bar');
$stmt->closeCursor();

---

Related Topics