Insert Retrive Binary Data in mysql using PDO & PHP

I’m a great fan of the PDO database access library in PHP 5 and use it for all of my database work in PHP. I love its’ clean, object oriented syntax and great support for prepared statements. I also like the fact that it supports most of the most common database engines.

In PDO one does not worry about sanitization and with prepared query we do have a performance boost.

The PDO does not work the same way with the Blob field type as it has a layer which inspects and sanitizes  data.

PDO handles binary through LOB Large Objects. Large typically means “around 4kb or more”, although some databases can happily handle up to 32kb before data becomes “large”. Large objects can be either textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB type code in your PDOStatement::bindParam() orPDOStatement::bindColumn() calls. PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.

Display image from database

Code:

<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->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);
?>

Save Image in Database

Code:

<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // some function to allocate a new ID

// assume that we are running as part of a file upload form
// You can find more information 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();
?>

You may run into wall despite following all this. The cause of the same is mysql does not allow by default a file size greater than 1 MB. I was trying to upload a file size greater than one MB. After breaking my head for almost a week, I was finally able to figure out the problem source is mysql not php.

Increase the max_allowed_packet

either by adding the following line (where 16M is data packet size)

Code:
max_allowed_packet = 16M

in [mysqld] section of your my.cnf/my.ini file, or by using the following command line:

Code:
> mysqld --max_allowed_packet=16M
Advertisements

2 thoughts on “Insert Retrive Binary Data in mysql using PDO & PHP

    • I m afraid the changes for mysql is global. You can increase the packet size for all customer and limit the packet size though php and throw error on large packet size.
      If you really want to do at mysql level then you must fire a query to increase the packet size in mysql and then save your content and then again reset back the max packet size.

      $db = new mysqli( ‘localhost’, ‘user’, ‘pass’, ‘dbname’ );
      // to get the max_allowed_packet
      $maxp = $db->query( ‘SELECT @@global.max_allowed_packet’ )->fetch_array();
      echo $maxp[ 0 ];
      // to set the max_allowed_packet to 500MB
      $db->query( ‘SET @@global.max_allowed_packet = ‘ . 500 * 1024 * 1024 );

      You can choose what approach suits you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s