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: