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



Listings code on Latex – XSLT
09/12/2009, 17:47
Filed under: Latex | Tags: , , ,

Using the package listings you can add non-formatted text as you would do with \begin{verbatim} but its main aim is to include the source code of any programming language within your document.  Wikibooks

One of the problems of this package is the poor colored syntax with some programming languages, like XML or XSLT, trying language=XML and language=XSLT, the result was… bullshit poor, I prefere to use language=HTML because it has a minimal pretty syntax  and I made some changes, the result:

\lstnewenvironment{fakeXML}[1][]{
\lstset{basicstyle=\scriptsize\sffamily,
linewidth=0.90\linewidth,
numbers=left,
stepnumber=1,
numbersep=10pt,
frame=single,
framerule=1.0pt,
backgroundcolor=\color{darkgray},
language=HTML,
identifierstyle=\color[rgb]{1,0,0},
emph={schema, element, complexType, choice, simpleType, sequence, restriction, pattern}, emphstyle=\color{red},
keywordstyle=\color[rgb]{0,0,1},
commentstyle=\color[rgb]{0.133,0.545,0.133},
stringstyle=\color[rgb]{0.627,0.126,0.941},
morekeywords={xml, ref, xs, version, targetNamespace, minOccurs, maxOccurs}
}\lstset{#1}}{}

\begin{fakeXML}
<?xml version=”1.0″?>
<xs:schema targetNamespace=…

</xs:schema>
\end{fakeXML}

It looks like:

Listings and XML

Another problem solved! 😆