PHP--PDO large object (LOBs)

The PDO of the PHP database abstraction layer-a large object (LOBs) application may need to store "big" data in the database at a certain time point. "Large" usually means "about 4 kB or above", although some databases can easily process up to 32 kB of data before the data reaches "large. A large object may be either text or binary. Use the PDO: PARAM_LOB type code in the PDOStatement: bindParam () or PDOStatement: bindColumn () call to enable PDO to use the big data type. PDO: PARAM_LOB tells PDO to map data as a stream so that it can be operated using the PHP Streams API.

Example #1 show an image from the database

The following example binds a LOB to the $ lob variable and sends it to the browser using fpassthru. Because LOB represents a stream, functions such as 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);?>

Example #2 insert an image to the database

In the following example, open a file and pass the file handle to PDO for insertion as a LOB. PDO tries its best to allow the database to obtain file content in the most effective way.

 Prepare ("insert into images (id, contenttype, imagedata) values (?, ?, ?) "); $ Id = get_new_id (); // call a function to assign a new ID. // assume that a file is uploaded. // you can find more information in the PHP document. $ 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 ();?>

Example #3 insert an image to the database: Oracle

Oracle is slightly different when inserting a lob from a file. It must be inserted after the transaction; otherwise, when the query is executed, the newly inserted LOB will be implicitly committed with a length of 0:

 Prepare ("insert into images (id, contenttype, imagedata)". "VALUES (?, ?, EMPTY_BLOB () RETURNING imagedata? "); $ Id = get_new_id (); // call a function to assign a new ID. // assume that a file is uploaded. // you can find more information in the PHP document. $ 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 ();?>