YouTip

# PHP PDO Large Objects (LOBs) [PHP PDO Reference](#) An application may need to store "large" data in the database at some point. "Large" typically means "approximately 4kb or more," although some databases can easily handle up to 32kb of data before it is considered "large." Large objects can be either text or binary in nature. Using the PDO::PARAM_LOB type code in a PDOStatement::bindParam() or PDOStatement::bindColumn() call allows PDO to handle large data types. PDO::PARAM_LOB tells PDO to map the data as a stream, so that the PHP Streams API can be used to manipulate it. ### Displaying an Image from the Database The following example binds a LOB to the $lob variable and then sends it to the browser using fpassthru(). Since the LOB represents a stream, functions like fgets(), fread(), and stream_get_contents() can be used on it. prepare("select contenttype, imagedata from images where id=?"); $stmt->execute(array($_GET['id'])); $stmt->bindColumn(1, $type, PDO::PARAM_STR, 256); $stmt->bindColumn(2, $lob, PDO::PARAM_LOB); $stmt->fetch(PDO::FETCH_BOUND); header("Content-Type: $type"); fpassthru($lob);?> ### Inserting an Image into the Database The following example opens a file and passes the file handle to PDO to be inserted as a LOB. PDO lets the database retrieve the file contents in the most efficient way possible. prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)"); $id = get_new_id(); // Call a function to allocate a new ID.// Assume handling a file upload.// More information can be found in the PHP documentation. $fp = fopen($_FILES['file']['tmp_name'], 'rb'); $stmt->bindParam(1, $id); $stmt->bindParam(2, $_FILES['file']['type']); $stmt->bindParam(3, $fp, PDO::PARAM_LOB); $db->beginTransaction(); $stmt->execute(); $db->commit();?> ### Inserting an Image into the Database: Oracle Inserting a LOB from a file is slightly different for Oracle. The insert must be done within a transaction, otherwise the newly inserted LOB will be implicitly committed with a length of 0 when the query is executed: prepare("insert into images (id, contenttype, imagedata) " ."VALUES (?, ?, EMPTY_BLOB()) RETURNING imagedata INTO ?"); $id = get_new_id(); // Call a function to allocate a new ID.// Assume handling a file upload.// More information can be found in the PHP documentation. $fp = fopen($_FILES['file']['tmp_name'], 'rb'); $stmt->bindParam(1, $id); $stmt->bindParam(2, $_FILES['file']['type']); $stmt->bindParam(3, $fp, PDO::PARAM_LOB); $stmt->beginTransaction(); $stmt->execute(); $stmt->commit();?> * * PHP PDO Reference](#)