note 107833 deleted from pdo.prepare by danbrown
| From: | danbrown@php.net | Date: | Thu, 10 May 2012 17:55:38 +0000 |
| Subject: | note 107833 deleted from pdo.prepare by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-187687@lists.php.net to get a copy of this message | ||
Note Submitter: gjarrige at six-axe dot fr
----
Example of scroll cursor for DB2 for i5 (IBM i server), that I use in a abstract class :
public static function getScrollCursor($db, $sql, $args, $offset, $nbl_by_page ) {
if (!is_array($args)) {
$args = array() ;
}
$offset = intval($offset) ;
if ($offset <= 0) {
$offset = 1 ;
}
$nbl_by_page = intval($nbl_by_page);
if ($nbl_by_page <= 0) {
$nbl_by_page = 10 ;
}
$sql = trim ($sql );
$rows = array ();
try {
$st = $db->prepare ( $sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$ok = $st->execute ( $args );
if ($ok) {
for (
$tofetch = $nbl_by_page,
$row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_REL, $offset);
$row !== false && $tofetch-- > 0;
$row = $st->fetch(PDO::FETCH_ASSOC)
) {
$rows [] = $row;
}
} else {
$rows = null;
}
unset ( $st );
return $rows;
} catch ( Exception $e ) {
// place your error code here;
}
}
You'll find a similar example in the PHP.net site, for the function "prepare" of the
"ibm_db2" library.
The scroll cursor technique is more efficient on very big tables than the "full SQL"
technique presented below :
SELECT foo.* FROM (
SELECT row_number() over (ORDER BY TABLE_NAME) as rn,
A.*
FROM YOURLIB/YOURTABLE A
WHERE YOU_COL1 = ? AND YOUR_COL2 = ? ...
) AS foo
WHERE foo.rn BETWEEN ? AND ?