Finding documentation on using Oracle bound variables in ZF2 is very difficult to say the least. If you search around you will probably come across a number of methods for handling bound in variables but what about bound out variables? After working in ZF2 for quite some time I finally found a clean, ZF2 way of handling this. Take the method below for instance. Assuming that you already have you database adapter available in the model the method below shows how to handle in, out and in/out variables.
ZF2 Query Helpers and Oracle Bound In / Out Variables
public function copyEntity(CopyEntity $copyEntity) {
// Prepare the statement
$statement = $this->getDbAdapter()->createStatement('BEGIN entity.copy(:rid,:nkid,:new_official_name,:entered_by); END;');
// Setup parameter container
$parameterContainer = new ParameterContainer();
$parameterContainer->offsetSet('rid',$copyEntity->getRid(),$parameterContainer::TYPE_INTEGER);
$parameterContainer->offsetSet('nkid',0,$parameterContainer::TYPE_INTEGER);
$parameterContainer->offsetSet('new_official_name',$copyEntity->getOfficial_name(),$parameterContainer::TYPE_STRING);
$parameterContainer->offsetSet('entered_by',$copyEntity->getEntered_by(),$parameterContainer::TYPE_INTEGER);
$parameterContainer->offsetSetReference('rid', 'rid');
$parameterContainer->offsetSetReference('nkid', 'nkid');
$statement->setParameterContainer($parameterContainer);
try {
$statement->execute();
} catch (RuntimeException $ex) {
$parameterContainer->offsetSet('code',$ex->getCode());
}
// Return parameter container values
return $parameterContainer->getNamedArray();
}Lets step through the code. First you will need to create your statement using create statement. In this case I am using a procedure contained in a package on the Oracle side of things.
Next prepare the statement. This doesn't always need to be done but if you don't manually do it you will run into odd issues in some edge cases so I find it best practice to just always do it.
Next we need to instantiate the ParameterContainer as it contains all of the methods we will use to make this work. For in bound variables you simply need to use the offsetSet method. It takes two parameters, the variable name and the value. A third optional parameter can be used, variable type.
In and in/out bound variables are handled the same way. First you define the variable using offsetSet. Next you will use offsetSetReference which passes the value by reference into the parameter container letting you later retrieve its value. offsetSet only takes two parameters, the name of the variable going in and the name you will retrieve it by. These can be different but will normally be the same value.
For bound out variables where you are not passing in a value initially simply define an empty value as a placeholder.
Next we set the parameter container to the statement. You can also pass the parameter container to the statement->execute() method as its only parameter but I have found that this sometimes results in errors that not all parameters are bound. Best practice is to just explicitly set it before executing the statement.
Next I surround the execute() method in a try/catch statement so that I can catch any resulting oracle errors. If one should occur I set the error to the parameter container so that it is available as a variable just like my bound variables.
The last line of code is converting the parameter container to an associative array. This is of course optional as you could just as easily grab the values directly from the parameter container.
Hopefully this will save someone else from the confusion I had as I tried to work through correctly working with ZF2 and Oracle.
Get In Touch.
ContactRecommended content:
