I'm using a db class which manages connections to one or more databases and also acts as a wrapper for PDO. Whenever I add new databases or if I ever wanted to switch from PDO, I don't want to change code anywhere else.
However, I'm curious about why I can't bind values and parameters inside the wrapper class and then execute the prepared statement inside the same class on a subsequent call. At the same time, returning the statement from within the class and then using it externally to execute the prepared statement works.
Example usage
$param = 1;
$value = '2013-03-20';
$db = DbMinimized::connect('default');
# Doesn't matter wether it's bound params
$db->prepare("SELECT f FROM t WHERE someint = :param");
$db->bindParam('param', $param, 'i');
/* or bound values
$db->prepare("SELECT f FROM t WHERE somedate > UNIX_TIMESTAMP(:value)");
$db->bindValue('value', $value, 's');
*/
/* or both
$db->prepare("SELECT f FROM t WHERE someint = :param AND somedate > UNIX_TIMESTAMP(:value)");
$db->bindParam('param', $param, 'i');
$db->bindValue('value', $value, 's');
*/
# The end result is the same, i.e
# this works
# $db->getStatement() returns &$this->stmt[$stmt_name]
$stmt = $db->getStatement();
$stmt->execute();
# while this fails
# $db->execute call calls $this->stmt[$stmt_name]->execute()
$db->execute();
# Warning: PDOStatement::execute() SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
And as far as I can tell, a reference to the exact same statement is used for both calls to execute. The only difference I can see is the containing scope. But since the bound params are passed by reference that shouldn't matter, should it? And as far as binding values go... well, for them the scope shouldn't matter, should it?
Minimized class definition
class DbMinimized
{
private static $dbs = array('default');
private static $dsn = array('default' => 'mysql:dbname=db;host=1.2.3.4');
private static $user = array('default' => 'user');
private static $pass = array('default' => 'pass');
static $instance = array('default' => false);
private $db = false;
private $stmt = array();
private function __construct($db)
{
# the static vars simply contains DSN strings, usernames and passwords
$this->db = new PDO(self::$dsn[$db], self::$user[$db], self::$pass[$db]);
}
public static function &connect($db = 'default')
{
if (!self::$instance[$db])
self::$instance[$db] = new DbMinimized($db);
return self::$instance[$db];
}
public function prepare($q, $stmt_name = 'default')
{
$this->stmt[$stmt_name] = &$this->db->prepare($q);
}
private function getParamDataType($data_type = 's')
{
switch ($data_type)
{
case 's':
return PDO::PARAM_STR;
case 'i':
return PDO::PARAM_INT;
}
}
public function bindParam($parameter, &$variable, $data_type, $stmt_name = 'default')
{
# changes 'i' into PDO::PARAM_INT, 's' into PDO::PARAM_STR etc
$data_type = $this->getParamDataType($data_type);
$this->stmt[$stmt_name]->bindParam($parameter, $variable, $data_type);
}
public function bindValue($parameter, $variable, $data_type, $stmt_name = 'default')
{
# changes 'i' into PDO::PARAM_INT, 's' into PDO::PARAM_STR etc
$data_type = $this->getParamDataType($data_type);
$this->stmt[$stmt_name]->bindValue($parameter, $variable, $data_type);
}
# Won't work with bound params / values.
# Only works if I pass along all params in $args
public function execute($args = array(), $stmt_name = 'default')
{
$this->stmt[$stmt_name]->execute($args);
}
# But if I first return the internal statement instance to the calling code
# and use it there to call ->execute(), it works with bound params / values
public function &getStatement($stmt_name = 'default')
{
return $this->stmt[$stmt_name];
}
}
