When should PDO::closeCursor() be used?

[NotionCommotion]

1. When for MySQL?

When for SQLite?

Does it make a difference if it is a single hit webserver or a PHP server or client that continuously runs?

[Jacques1]

PDO::closeCursor() frees the result set. Since the statement destructor does this automatically, the method is irrelevant except for edge cases. When you have a large result set and a script which keeps the statement open for a long time (thus preventing destructor execution), then it might be useful to manually free the result set. Or when you've run into an out-of-sync error due to an unbuffered query and a strange coding style (like another user did recently).

[NotionCommotion]

PDO::closeCursor() frees the result set. Since the statement destructor does this automatically, the method is irrelevant except for edge cases. When you have a large result set and a script which keeps the statement open for a long time (thus preventing destructor execution), then it might be useful to manually free the result set.

Thanks Jacques1,  So, if I have a PHP script which runs indefinitely, I may need to use it?  What do you consider a large result set?

[Jacques1]

I doubt that you keep the statement open indefinitely. You probably have quick processing cycles after which all data gets destroyed.

 

If you don't have this model, you have bigger problems, because then you need to worry about all data that might pile up, not just the result sets.

 

When in doubt, measure the memory usage. Or free the result set just-in-case, if that makes you feel better.
 

[NotionCommotion]

I doubt that you keep the statement open indefinitely. You probably have quick processing cycles after which all data gets destroyed.

 

If you don't have this model, you have bigger problems, because then you need to worry about all data that might pile up, not just the result sets.

 

When in doubt, measure the memory usage. Or free the result set just-in-case, if that makes you feel better.

 

 

 

What if this is my model?

use React\EventLoop\Factory;
$loop = Factory::create();
$loop->addPeriodicTimer(60, function() {
...
    $stmt=$db->prepare('some query goes here...');
    $stmt->execute([$x,$y]);
    //fetch if it happens to be a SELECT query...
...
});
$loop->run();

[kicken]

What if this is my model?

That does not keep the statement open for the entire duration of the script, only for the duration of the function. Each time the function ends $stmt would go out of scope and the resources would be cleaned up automatically.

 

Keeping a statement open indefinitely would require code that is something like this:

<?php

$db = new PDO(...);
$stmt = $db->query('SELECT * FROM config');
$config = $stmt->fetch(); //Only fetch one row

//...

$loop->run();

$stmt is globally scope so it will not deconstruct until the end of the script, and fetching only one row of a possibly multi-row result would cause it to remain open.

 

I think the only time I've needed to explicitly call closeCursor was back in 2010 to work around some issues in the MSSQL PDO driver at the time. I don't remember the exact details of the problem, something with stored procedures I think, but since switching to the newer SQLSRV driver I've not needed it.

Edited February 8, 2017 by kicken

[NotionCommotion]

Thanks kicken,

 

Guess I didn't realize that resources are cleared on a per function basis, but now makes sense.

 

Sounds like it is not necessary unless one experiences some rare issues.

[ginerjm]

Point. Resources are cleared not on a function basis, but whenever they go out of scope as was explicitly said above. The same holds true for global values when the containing script itself ends.

 

I too have rarely used close_cursor. In fact the 2-3 times I have used it I had to read up on it. I have only used it when I have multiple queries going on and want to dispose of the prior one. Also a rarity.