From YiPs Wiki (i powered)

PHP: IDB 2 BLOBCLOB

(click to open)

Quick Page Table of Contents

Scanning…

ibm_db2 running on IBM i …

Goto Main Page

Who is this page for?

Instructions designed for IBM i developer learning PHP starting from ground zero …

ibm_db2 issues

BLOB vs. CLOB?

Short of it mostly CLOB good and BLOB bad …

BLOB tricks are for kids

Dealing with BLOB “binary data” in a scripting language like PHP can be problematic with a limited number of primitive types available integer/long, real/double, character and resource/object, which generally do not support “binary data”. In general, you cannot rely on any of the scripting language string functions (strlen, substr, etc.) because the BLOB data may contain characters like NULL/zero embedded in the binary data.

<?php
// BLOB non-escaped embedded NULLs can lead to all manner of PHP mistakes ...
// Actual output: 'FrogToad=17 hex1:00000046726F67000000546F6164000000'
$hex1 = '00000046726F67000000546F6164000000';
$bin1 = pack("H*",$hex1);
echo $bin1."=".strlen($bin1)." hex1:".$hex1."\n"; // all data here, but not displayed
// user attempting display 'FrogToadBear' ... no, does not work
// Actual output: 'FrogBear=12  hex2:00000046726F670042656172'
$bin2 = substr($bin1,0,8)."Bear";
$hex2 = strtoupper(bin2hex($bin2));
echo $bin2."=".strlen($bin2)."  hex2:".$hex2."\n";
?>

To avoid “binary data” issues the following encode/decode BLOB “hex trick” via PHP bin2hex (encode) and pack (decode) can put BLOB/binary Genie back in the bottle allowing insert/fetch problematic Gen using either a BLOB or CLOB database store (at least worked for my PDF test).

// strtoupper(bin2hex(file)) -- converts binary (PDF) to hex representation (hex string)
// pack( "H*", $contents ) -- converts hex representation (hex string) to binary (PDF)

// read and convert binary (PDF) to hex representation (hex string)
function test_get_file_hex($filename) {
  $handle = fopen($filename, "rb");
  $contents = strtoupper(bin2hex( fread( $handle, filesize($filename) ) ) );
  fclose($handle);
  return $contents;
}

// write  hex representation (hex string) to binary (PDF) file
function test_put_file_hex($prefix, $filename, $contents) {
  file_put_contents($prefix.$filename, pack( "H*", $contents ));
}

// diff tester of original PDF with converted bin2hex/pack PDF
function test_diff_file_hex($prefix, $filename) {
  $newfile = $prefix.$filename;
  $data = `diff $filename $newfile 2>&1`;
  // annoying extra null (some drivers), but ok PDF viewers
  if (strpos($data,"Missing newline at the end of file")>0) $data = "";
  return $data;
}

Run IBM i

I tried this technique on IBM i (1-tier) and from my Linux laptop over DB2 Connect (both worked with script unaltered). I am using ibm_db2 for this example because i like this interface both 1-tier on IBM i and 2-tier from my Linux laptop (IBM LUW guys keep this PHP driver in fairly good shape).

Create these files for testing via Zend Studio (or Apache, or command line)..

A test (click to view)

The PHP programs are identical except for create table … picture CLOB(5M) or picture BLOB(5M) … which is the whole idea about interchangeable data flexibility even when used in XML for fantastic new features like {XMLSERVICE IBM i}, which just happens to be written in RPG (hint).

Things perhaps worth a moment of contemplation…

It did not matter if i used DB2 store/write blob or clob even remote to IBM i, because bin2hex converted to a HEX string representation of the binary PDF that could be converted back into the same binary PDF using PHP pack, ebcdic/ascii “inside” db2 was not a factor (you may have to think about that for a spell).

As far as interoperability with native IBM i ILE, if you wanted to read/write that same blob/clob into ILE after PHP stored DB2 clob/blob (RPG), then you would have to “roll your own” bin2hex and pack function in ILE and it would be aware of at least ascii<>binary.

Retrieved from http://youngiprofessionals.com/wiki/index.php/PHP/IDB2BLOBCLOB
Page last modified on February 14, 2013, at 12:59 PM EST