/**
* Constructor
*
* @param Connection $connection Database connection
* @param string $sql SQL statement
* @param array $params SQL statement parameters
*/
public function __construct(Connection $connection, $sql, array $params = array())
{
$this->connection = $connection;
$this->stmt = $this->connection->prepare($sql);
foreach ($params as $key => $value) {
$this->stmt->bindValue($key, $value);
}
}
/**
* common used bindValue. this is very useful while create query for count first and query again to get all data.
*
* @param \Doctrine\DBAL\Driver\Statement $stmt Doctrine statement object.
* @param array $options options for bindValue.
*/
private function bindValues($stmt, array $options = [])
{
if (isset($options['module_enable'])) {
$stmt->bindValue('module_enable', $options['module_enable'], \PDO::PARAM_INT);
}
if (isset($options['site_id'])) {
$stmt->bindValue('site_id', $options['site_id'], \PDO::PARAM_INT);
}
if (isset($options['search'])) {
$stmt->bindValue('search', '%' . $options['search'] . '%', \PDO::PARAM_STR);
}
}
/**
* Binds a parameter value to the statement.
*
* The value can optionally be bound with a PDO binding type or a DBAL mapping type.
* If bound with a DBAL mapping type, the binding type is derived from the mapping
* type and the value undergoes the conversion routines of the mapping type before
* being bound.
*
* @param string $name The name or position of the parameter.
* @param mixed $value The value of the parameter.
* @param mixed $type Either a PDO binding type or a DBAL mapping type name or instance.
*
* @return boolean TRUE on success, FALSE on failure.
*/
public function bindValue($name, $value, $type = null)
{
$this->params[$name] = $value;
$this->types[$name] = $type;
if ($type !== null) {
if (is_string($type)) {
$type = Type::getType($type);
}
if ($type instanceof Type) {
$value = $type->convertToDatabaseValue($value, $this->platform);
$bindingType = $type->getBindingType();
} else {
$bindingType = $type;
// PDO::PARAM_* constants
}
return $this->stmt->bindValue($name, $value, $bindingType);
} else {
return $this->stmt->bindValue($name, $value);
}
}
/**
* @param string $sql Consulta SQL a la base de datos
* @param array $criteria Criterios de la consulta SQL adoptados en el WHERE
*
* @return Statement Retorna el objeto de DoctrineORM Statement
* @throws DBALException Error de DoctrineORM
* @throws Exception Error en el SQL
*/
protected function _query($sql = "", array $criteria = array())
{
try {
// Preparar el SQL
$this->_stmt = $this->_connection->prepare($sql);
// Agregar los parametros
foreach ($criteria as $param => $value) {
if (is_integer($param)) {
$this->_stmt->bindValue($param + 1, $value);
}
if (is_string($param)) {
$this->_stmt->bindParam($param, $value);
}
}
// Ejecutar el SQL
$this->_stmt->execute();
} catch (DBALException $dbalException) {
#throw $dbalException;
}
if (in_array($this->_stmt->errorCode(), array_keys($this->_errors))) {
throw new Exception($this->_errors[$this->_stmt->errorCode()] . " SQL: {$sql}");
}
return $this->_stmt;
}
/**
* Binds a set of parameters, some or all of which are typed with a PDO binding type
* or DBAL mapping type, to a given statement.
*
* @param \Doctrine\DBAL\Driver\Statement $stmt The statement to bind the values to.
* @param array $params The map/list of named/positional parameters.
* @param array $types The parameter types (PDO binding types or DBAL mapping types).
*
* @return void
*
* @internal Duck-typing used on the $stmt parameter to support driver statements as well as
* raw PDOStatement instances.
*/
private function _bindTypedValues($stmt, array $params, array $types)
{
// Check whether parameters are positional or named. Mixing is not allowed, just like in PDO.
if (is_int(key($params))) {
// Positional parameters
$typeOffset = array_key_exists(0, $types) ? -1 : 0;
$bindIndex = 1;
foreach ($params as $value) {
$typeIndex = $bindIndex + $typeOffset;
if (isset($types[$typeIndex])) {
$type = $types[$typeIndex];
list($value, $bindingType) = $this->getBindingInfo($value, $type);
$stmt->bindValue($bindIndex, $value, $bindingType);
} else {
$stmt->bindValue($bindIndex, $value);
}
++$bindIndex;
}
} else {
// Named parameters
foreach ($params as $name => $value) {
if (isset($types[$name])) {
$type = $types[$name];
list($value, $bindingType) = $this->getBindingInfo($value, $type);
$stmt->bindValue($name, $value, $bindingType);
} else {
$stmt->bindValue($name, $value);
}
}
}
}
/**
* Bind parameters to statement
*
* @param Statement $stmt
* @param ArrayCollection $parameters
*/
public function bindParameters(Statement $stmt, ArrayCollection $parameters)
{
$values = [];
$types = [];
foreach ($parameters as $parameter) {
/* @var $parameter Parameter */
$values[] = $parameter->getValue();
$types[] = $parameter->getType();
}
$typeOffset = array_key_exists(0, $types) ? -1 : 0;
$bindIndex = 1;
foreach ($values as $value) {
$typeIndex = $bindIndex + $typeOffset;
if (isset($types[$typeIndex])) {
$type = $types[$typeIndex];
$stmt->bindValue($bindIndex, $value, $type);
} else {
$stmt->bindValue($bindIndex, $value);
}
++$bindIndex;
}
}
public function bindValue($param, $value, $type = null)
{
return $this->stmt->bindValue($param, $value, $type);
}
/**
* Bind params to SQL statement.
*
* @param Statement $statement
* @param array $params
*/
private function bindParams($statement, $params)
{
foreach ($params as $param) {
$statement->bindValue($param[0], $param[1], $param[2]);
}
}
/**
* Binds values to the prepared statement
*
* @param Statement $stmt statement
* @param array $valuesForBind values to bind in following structure:
* [
* "key" name of parameter in statement
* => [
* "value" value of the parameter
* "type" type of the parameter (optional)
* ]
* ]
*/
private function bindValuesToStatement(Statement $stmt, $valuesForBind)
{
foreach ($valuesForBind as $key => $data) {
if (isset($data['type'])) {
$stmt->bindValue($key, $data['value'], $data['type']);
} else {
$stmt->bindValue($key, $data['value']);
}
}
}
