CODEWITHSUNDEEP
phppdo
php_nub_qq
php_nub_qq

Reputation: 16017

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.

Upvotes: 15

Views: 47391

Answers (2)

Balmipour
Balmipour

Reputation: 3055

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.

Upvotes: 7

mingos
mingos

Reputation: 24502

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.

Upvotes: 13

Related Questions