2

I have images as BLOB's in an MS ACCESS database. I have so far used them with odbc acces from PHP and it works fine. Here comes the simplified program:

code:
<?php
ini_set("odbc.defaultlrl", "5M");
$dbName = $_SERVER["DOCUMENT_ROOT"]."\\..\db\\teknofo.mdb";
$con = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=".$dbName,'','') or die('Ups');
ob_clean();
header('Content-Type: image/*');
$sql = "SELECT photo FROM Medlemmer WHERE Id=17";
$rd = odbc_exec($con, $sql);
if (odbc_fetch_row($rd)) { echo odbc_result($rd,"photo"); }
odbc_close($con);
ob_end_flush();
?>

I am in the process of converting to MySql but will have to use MS Access for some timg: Therefor I am making the new code using PDO, but I am not able to read the data correct.

Here comes the new 

<?php
$dbName = $_SERVER["DOCUMENT_ROOT"]."\\..\db\\teknofo.mdb";
$con = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");
$sql = "SELECT photo FROM Medlemmer WHERE id=?";
$st = $con->prepare($sql);
$st->execute(array(17));
$st->bindColumn('photo', $photo, PDO::PARAM_LOB);
$st->fetch(PDO::FETCH_BOUND);
odbc_longreadlen($st, 131072);        
odbc_binmode($st,ODBC_BINMODE_CONVERT);                            
ob_clean();
header('Content-Type: image/*');
if ($rd = $st->fetch(PDO::FETCH_BOUND)) {
echo $rd['photo'];
ob_end_flush();
$con = null;
?>

The last code Works fin with MySql (changed connection string) but not with MS Access.

I have searced the net for a long time but have not been able to find a solution.

Can anybody help pleasse?

I could use the first code, but I need to be able to handle BLOB's for other purposes as well.

Erik P
  • 129
  • 5

3 Answers3

5

PHP and the Access ODBC driver have never been the best of friends, and apparently that continues to be the case with PDO_ODBC and the Access ODBC driver. The two wrinkles here were

  1. The BLOB is returned as an ASCII string representing the hex values of the image data (e.g., '424D7AC000...'), and

  2. That string contains a spurious NULL character every 255 characters.

The code I managed to get working is:

<?php
$dbName = $_SERVER["DOCUMENT_ROOT"]."\\test.mdb";
$con = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");
$sql = "SELECT Photo FROM Clients WHERE id=?";
$st = $con->prepare($sql);
$st->execute(array(1));
$st->bindColumn(1, $photoChars, PDO::PARAM_LOB);
$st->fetch(PDO::FETCH_BOUND);

// $photoChars is a long string of hex, e.g., '424D7A...'

// PDO+Access_ODBC apparently injects a NULL every 255 characters, 
//     so remove them first
$photoChars = str_replace("\0", "", $photoChars);

// create array of character pairs (e.g.: '42', '4D', '7A', ...)
$photoArray = str_split($photoChars, 2);

// convert to numeric values
for ($i = 0; $i < sizeof($photoArray); $i++) {
    $photoArray[$i] = hexdec($photoArray[$i]);
}

// pack into binary string
//     ref: http://stackoverflow.com/a/5473057/2144390
$photoData = call_user_func_array("pack", array_merge(array("C*"), $photoArray));

header('Content-Type: ' . image_type_to_mime_type(IMAGETYPE_PNG));
header('Content-Disposition: attachment; filename="untitled.bmp"');
echo $photoData;
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you Gord - this realy Works - but what a shame we have to do things like that. But I fear what will be the trooubles when I try to update the blob data????? – Erik P Mar 12 '14 at 07:57
  • @ErikP Long ago I came to the conclusion that when working with Access databases from PHP the best approach (by far) is to use [COM](http://www.php.net/manual/en/book.com.php) and an ADODB.Connection. I honestly don't recall if I was able to get BLOB updates to work using PDO_ODBC and the Access ODBC driver. – Gord Thompson Mar 12 '14 at 09:11
  • Thanks for the tip. I will look into COM and see if I can use it for both Access and MySql; – Erik P Mar 12 '14 at 09:26
3

I know its an old thread, but i stumbled uppon the same problem and @gord-thompson's answer helped me. But I realized that there is a simpler and faster way to do it.

All you need to do is strip out the \0-Bytes and then invoke pack directly:

    // PDO+Access_ODBC apparently injects a NULL every 255 characters,
    //     so remove them first
    $photoChars = str_replace("\0", "", $photoChars);

    //Pack the HEX-String into a binary string and encode it with base64
    base64_encode(pack("H*", $photoChars));

So str_split, hexdec is not needed anymore. All you have to do is pack the HEX-Data that comes from PDO+Access into binary format.

D3xter
  • 6,165
  • 1
  • 15
  • 13
0

Using the solution from Gord Thompson I ran into a bug. I don't know why the endings were messed up on some binaries but this is how I fixed it. Sort of a hack to patch an unknown corruption source but it worked.

// create array of character pairs (e.g.: '42', '4D', '7A', ...)
$photoArray = str_split($photoChars, 2);

// at this point some jpegs were ending in 'ff', '0D', '09'
// they should end in 'ff', 'D9'
$photo_end = sizeof($photoArray)-1;
if($photoArray[$photo_end] === '9'){
    $photoArray[$photo_end - 1] = 'D9'; // fix the previous element to be end of image
    unset($photoArray[$photo_end]); // remove the last element
}

// pack into binary string
//     ref: http://stackoverflow.com/a/5473057/2144390
$photoData = call_user_func_array("pack", array_merge(array("C*"), $photoArray));
lumine
  • 1