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

$upload->setDestination($destination);
//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);

try{

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

if($fileHandle){
$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..
unlink($fileName);
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 http://www.ibm.com/developerworks/exchange…) 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:

$datos[‘ARCHIVO_CARACTERISTICAS’]   =

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;

$datos[‘ARCHIVO_CARACTERISTICAS’]   =

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

$tabla = new tablaArchivo();
$tabla->insert($datos);

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.

Sources:

XMLFromFile functions

PDO_IBM

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 9 2 ?