For example, it is mentioned in the closeCursor() method, is it the actual pointer when traversing a result set or is it the cursor used in stored procedures?

On the PDOStatement::execute() page it says:

Some drivers require to close cursor before executing next statement.

CodePudding user response：

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.

CodePudding user response：

You're basically on the right track when you say that it's a pointer when traversing the result set, but it's the MySQL position, not the PHP one. In this case, PDO is telling MySQL to reset its location, as any further unfetched results are to be discarded.

PHP itself doesn't keep track of the cursor.