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

  • db2_lob_read — is not correct for BLOB data and may not work (needs fix IBM i)
  • db2_prepare/db2_execute — have been used for both BLOB/CLOB work in general (assuming DB2 PTFs relatively current)
  • DBCS characters - CHAR/VARCHAR/VARGRAPHIC/CLOB — require 1208 set in fastcgi.conf (see FAQ main page)

BLOB vs. CLOB?

Short of it mostly CLOB good and BLOB bad …

  • CLOB is character data and will translate CCSID between client/server … Internet friendly
    • DB2 CLOB/character is primary interface to XMLSERVICE, DB2 does all the work XML client/server (like breathing air)
  • BLOB is binary data and will not change state (or CCSID) … Internet nightmare problematic many scripting languages (like PHP)
    • BLOB/binary may embed NULLs/zeros, so “string functions” (trim, strlen, substr, etc.) may give unexpected results
    • BLOB/binary data is never converted, but user assumes BLOB data converted ASCII / EBCDIC (DB2 user concept error)

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.