SQLSTATE[HY093]: Invalid parameter number: no parameters were bound, but parameters are provided

I'm building a database object that joins the PDO object with the PDOStatement object in order for chaining to be available. Basically I just put the methods I most frequently use, but bindParam is giving me a hard time.

private $stmt = null;

...

public function prepare($statement,array $driver_options = array()) {
    if($this->stmt) throw new \Exception('PDO Statement already prepared, no override!');
    $this->stmt = parent::prepare($statement, $driver_options);
    return $this;
}

public function bindParam($place, &$val, $dataType){
    if(!$this->stmt) throw new \Exception('PDO Statement is empty');
    $this->stmt->bindParam($place, $val, $dataType);
    return $this;
}

public function execute(array $params = array()){
    if(!$this->stmt) throw new \Exception('PDO Statement is empty');
    $this->stmt->execute($params);
    return $this;
}

public function fetchAll($pdoFetchType){
    if(!$this->stmt) throw new \Exception('PDO Statement is empty');
    return $this->stmt->fetchAll($pdoFetchType);
}

...

public function getStmt(){
    return $this->stmt;
}

public function clearStmt(){
    $this->stmt = null;
}

I get the error, from the title, in this code:

$i = 0;
$db->prepare('SELECT * FROM users LIMIT ?,1')->bindParam(1, $i, \PDO::PARAM_INT);
while($row = $db->execute()->fetchAll(\PDO::FETCH_ASSOC)){
    echo "<pre>".print_r($row, true)."</pre>";
    $i++;
}

Basically what I found out about this error is that it occurs when provided variables in bindParam are null, but $i is clearly not null. Can you help me out?

EDIT: Also running

var_dump($this->stmt->bindParam($place, $val, $dataType));

in the bindParam method returns TRUE. From the manual:

Return Values

Returns TRUE on success or FALSE on failure.

It's succeeding but not binding the parameter ??? I feel my brain is going to explode soon.

Answers


I guess using a reference &$val instead of a value $val is what causes the issue.

Please try this code instead:

public function bindParam($place, $val, $dataType)
{
    if(!$this->stmt) throw new \Exception('PDO Statement is empty');
    $this->stmt->bindParam($place, $val, $dataType);
    return $this;
}

EDIT

My above answer is wrong.

Try modifying the execute method:

public function execute(array $params = array()){
    if(!$this->stmt) throw new \Exception('PDO Statement is empty');
    $this->stmt->execute();
    return $this;
}

Passing an empty array as parametre to the execute method removes all previous bindings. This is why bindParam returned true (successfully bound), yet the "no params were bound" error appeared as soon as you called execute.


Got the same error, but different cause

My request had comments, one of which included a damn question mark. For PDO, a "?" is, of course, a parameter to bind.

My request had no issue anywhere else, and I had no idea where PDO would invent a "parameter" while I wasn't using any, since I always use named placeholders, like :value

Spent more than one hour on this :( May this answer help some people having this foolishly trivial issue.


Need Your Help

Find exactly matched groups in a many to many relationship table

sql sql-server tsql sql-server-2012 sql-server-2014

Table shown below maps the many-to-many relationship between courses and students.

wicket url parameters with stateful pages

java url wicket

on an application i develop i'm trying to keep a nice and readable url for the application's pages.