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



Apache died after updating Ubuntu, pdo_ibm.so and php_pdo_declare_long_constant
06/02/2010, 12:51
Filed under: Apache,DB2,GNU/Linux,PHP | Tags: , , , , ,

After some months I installed my Ubuntu Server, yesterday I decided to update it, so:

$ apt-get update

$ apt-get upgrade

Between all the packages I found out php5, php5-dev, php5-common, php5-cli and libapache2-mod-php5, none of them should be dangerous… or maybe yes. I reboot and… crap, Apache doesn’t work! I tried to restart the daemon and got nothing, didn’t want to work. I took a look to the /var/log/apache2/error.log and I found:

/usr/sbin/apache2: symbol lookup error: /usr/lib/php5/20060613/pdo_ibm.so: undefined symbol: php_pdo_declare_long_constant

Something was wrong with the pdo_ibm module… I reinstalled again:

$ wget http://pecl.php.net/get/PDO_IBM-1.2.5.tgz

$ tar zxvf PDO_IBM-1.2.5.tgz

$ cd PDO_IBM-1.2.5

(Removed the line with the PDO dependency from the package.xml file: <dep type=”pkg” rel=”has” version=”1.0.0″>PDO</dep>)

$ pecl uninstall package.xml

$ pecl install package.xml

I tried again /etc/init.d/apache start and still didn’t work, so I decided to reinstall PDO:

$ pecl uninstall pdo

$ pecl install pdo

And after started apache /etc/init.d/apache start it worked!