Data Driven Design icon

ZF2 / Oracle Bind XML lob’s

When you start working with larger amounts of data being passed from ZF2 into Oracle stored procedures you will probably run into needing to bind XML lob's. You will also need an efficient way to convert your entities to XML for Oracle. Below is an extension of ZF2's parameter container that adds helper methods for converting entities to XML as well as properly handling date conversions to Oracle format.

namespace Application\Db\Adapter;
 
use \Zend\Db\Adapter\ParameterContainer as ZendParameterContainer;
use \SimpleXMLElement;
use Zend\EventManager\EventManager;
 
class ParameterContainer extends ZendParameterContainer {
     
    const TYPE_DATE    = 'SQLT_DATE';
     
    /**
     * Offset set
     *
     * @param string|int $name
     * @param mixed $value
     * @param mixed $errata
     */
    public function offsetSet($name, $value, $errata = null)
    {
        $position = false;
         
        // if integer, get name for this position
        if (is_int($name)) {
            if (isset($this->positions[$name])) {
                $position = $name;
                $name = $this->positions[$name];
            } else {
                $name = (string) $name;
            }
        } elseif (is_string($name)) {
            // is a string:
            $currentNames = array_keys($this->data);
            $position = array_search($name, $currentNames, true);
            // check if it's a date and convert it to Oracle format if it is
            if($errata == $this::TYPE_DATE) {
               $value = ($value) ? date('d-M-Y', strtotime($value)) : null;
            }
        } elseif ($name === null) {
            $name = (string) count($this->data);
        } else {
            throw new Exception\InvalidArgumentException('Keys must be string, integer or null');
        }
 
        if ($position === false) {
            $this->positions[] = $name;
        }
 
        $this->data[$name] = $value;
 
        if ($errata) {
            $this->offsetSetErrata($name, $errata);
        }
    }
     
    /**
     * Converts form array data to XML for Oracle Stored Procedures
     * 
     * @param string $root : The name of the root XML element that the SP expects
     * @param array $data : The data in array format. Must implement the getArrayCopy method.
     * @return string XML
     */
    public function getXMLLob($data,$root='root') {
        // Set DB root element
        $root = new SimpleXMLElement("<!--?xml version='1.0' encoding='UTF-8'?--><" . $root . "/>");
 
        // Convert form array to XML
        $xml = $this->array_to_xml($data, $root);
         
        return $xml;
    }
     
    /**
     * Converts form array data to XML for SP
     * 
     * @param string $params['xml_root'] : The name of the root XML element that the SP expects
     * @param array $params['form_data'] : The form data in array format
     * @return string XML
     */
    protected function array_to_xml($data, &$xml, $parent=NULL) {
        if (is_object($data)) {
            if (method_exists($data, 'getArrayCopy')) {
                $data = array_filter($data->getArrayCopy(), function($data) {
                    if (strlen($data) || is_array($data) || is_object($data))
                        return true;
                    return false;
                });
            } else {
                throw new \Exception('Object ' . get_class($data) . ' must implement getArrayCopy() method.');
            }
        } else if (is_array($data)) {
            $data = array_filter($data, function($data) {
                if (strlen($data) || is_array($data) || is_object($data))
                    return true;
                return false;
            });
        }
 
        foreach ($data as $key => $value) {
            if (is_array($value) || is_object($value)) {
                if (!is_numeric($key)) {
                    $this->array_to_xml($value, $xml, $key);
                } else {
                    $subnode = $xml->addChild("$parent");
                    $this->array_to_xml($value, $subnode, NULL);
                }
            } else {
                // Check if the value is a date
                if (preg_match('/(0[1-9]|1[012])[- \/.](0[1-9]|[12][0-9]|3[01])[- \/.](19|20)\d\d/', $value)) {
                    $value = date('d-M-Y', strtotime($value));
                }
                $value = preg_replace('/&[^; ]{0,6}.?/e', "((substr('\\0',-1) == ';') ? '\\0' : '&'.substr('\\0',1))", $value);
                $xml->addChild("$key", "$value");
            }
        }
 
        return $xml->asXML();
    }
     
}

In order to use this you will need to setup your entities with a method getArrayCopy(). Your entities will need to look something like this.

namespace Application/Entity/ExampleEntity;
 
class ExampleEntity {
 
    protected $id;
    protected $name;
 
    public function getId()
    {
        return $this->id;
    }
 
    public function getName()
    {
        return $this->name;
    }
 
    public function settId($id)
    {
        $this->id = $id;
    }
 
    public function settName($name)
    {
        $this->name = $name;
    }
 
    public function getArrayCopy()
    {
        return get_object_vars($this);
    }
}

You then use the parameter containers new methods when binding your variables in the model. See my other post on binding variables.
To bind convert the entity to XML you will simply do the following.

$parameterContainer->offsetSet('data',$parameterContainer->getXMLLob($exampleEntity, 'exampleEntity'),$parameterContainer::TYPE_LOB);

The getXMLLob method accepts two parameters. The entity to convert to XML and the name of the root element in the XML. The second parameter is optional and will default to root if you don't pass it.
This extension of the parameter container class also overrides the offsetSet() method to make working with Oracle dates easier. It adds the ability to specify a date data type when binding which will be converted to Oracle date format before running the statement. This can be used as follows.

$parameterContainer->offsetSet('date','02/07/2014',$parameterContainer::TYPE_DATE);

In the instance above the date will pass to Oracle as '07-Feb-2014'.

Get In Touch.
Contact

We proudly serve
the Pacific Northwest