Pedro's blog: db2, mysql, php, linux and performance

DB2, PDO, BLOBs and XML with Zend
21/02/2010, 12:46
Filed under: DB2,PHP | Tags: , , ,

What’s the secret of using PHP with PDO over DB2? there are a lot of secrets! one of them is the use of blobs and xml files. I’m going to show how I made it work.

First of all I’m working with big files, so I need a way of handling big files from the the HTTP request (working with Zend), here it comes:

$upload         = new Zend_File_Transfer_Adapter_Http();

$registry         = Zend_Registry::getInstance(); //Getting the registry with the config values
$config            = $registry->configuration; //Getting the config
$destination     = $config->upload->directory; //Getting the upload directory

//You can add validators here…
//$upload->addValidator(‘ExcludeMimeType’, false, ‘application’);

if (!$upload->receive()) {
//Do something because there is an error!

//Getting the full file name and the file name
$fileName          = $upload->getFileName(‘archivo’);
$filenameshort = $upload->getfileName(‘archivo’, false);


//Opening the file and reading it…
$fileHandle = fopen($fileName, “r”);

$fileContent = fread($fileHandle, filesize($fileName));

if (!is_string($fileContent)) {

//Do something with the error

}else {
//Do something with the error
//We got an error!
}catch(Exception $e){

//Do something with the error
//And removing the file..
return $this->_redirect(‘main/add’);

Now we have the file source in $fileContent and we can play with it. Easy, right?

In the case I want to add some XML files after generating them or from the file system… how can I do it? IBM offers a set of UDF files called “XMLFromFile functions” (you can find them in…) because if the XML tree is getting super big, there are some troubles with the maximum SQL insert sentence size. Better to use this User defined functions:


new Zend_Db_Expr(” clobFromFile(‘”.$xmlObject->getFileName().”‘) “);

//The function getFileName returns the full file name of the XML file on the system and the Zend_Db_Expr doesn’t add any extra quotes to the file name.

After we load the binary file in a PHP variable and we know how to get an XML file from the file system, the rest is to add everything to the database:

$datos[‘ARCHIVO_SOURCE’]  = $fileContent;


new Zend_Db_Expr(” clobFromFile(‘”.$xmlObject->getFileName().”‘) “);

$tabla = new tablaArchivo();

The database table tablaArchivo looks like:

class tablaArchivo extends Zend_Db_Table_Abstract{
protected $_name     = ‘ARCHIVO’; //Table real name
protected $_primary = ‘ARCHIVO_ID’; //Table primary key
//References to other tables and other stuff here…

There you go! an easy way of loading binary files and xml files on DB2 with the help of PHP, PDO, the IBM UDF XMLFromFile functions and Zend.


XMLFromFile functions


DB2 Express-C

No Comments so far
Leave a comment

Leave a comment
Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

¡IMPORTANTE! Responde a la pregunta: ¿Cuál es el valor de 14 11 ?