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

Question

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.

What happens if you put var_dump($val) inside the your defined bindParam()? And if you put var_dump($this->stmt)? — Francisco Presencia
– Francisco Presencia, Commented Dec 7, 2013 at 2:34
@FranciscoPresencia int(0) object(PDOStatement)#6 (1) { ["queryString"]=> string(29) "SELECT * FROM users LIMIT ?,1" } I really hate it when php does that to me.. — php_nub_qq
– php_nub_qq, Commented Dec 7, 2013 at 2:38
I'm running out of ideas, try bindValue() instead of bindParam() inside of your function public function bindParam to see if something changes. From the doc, Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called. but I'm not sure if it'd change anything. — Francisco Presencia
– Francisco Presencia, Commented Dec 7, 2013 at 2:46
@FranciscoPresencia even the error message stayed the same, at least that could've changed.. Q_Q — php_nub_qq
– php_nub_qq, Commented Dec 7, 2013 at 2:50

mingos · Accepted Answer · 2013-12-07 03:56:27Z

13

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.

edited Dec 7, 2013 at 3:56

answered Dec 7, 2013 at 3:06

mingos

24.7k12 gold badges73 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

php_nub_qq Over a year ago

It used to be without a reference, still got the same error. I added the reference because without it, wrapping the parent bindParam like this just makes no sense

mingos Over a year ago

References are dangerous and it's easy to break things. Please try running your loop and calling bindParam in each iteration with the updated $i value instead of relying on the reference. I'm sure it will work as expected.

php_nub_qq Over a year ago

It doesn't. And references aren't dangerous when they're used right. If the original PDO function bindParam uses a reference, then there must be a good reason for that, as there is. I tried what you suggested anyway and, sadly, the error is the same.

mingos Over a year ago

I hereby retire my claims regarding your supposed reference misuse - see my edited answer.

php_nub_qq Over a year ago

One of these things that makes you wanna go "How in the H did I not see this". Thank you very much sir!

|

Balmipour · Accepted Answer · 2018-02-14 13:26:00Z

7

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.

answered Feb 14, 2018 at 13:26

Balmipour

3,1251 gold badge27 silver badges30 bronze badges

1 Comment

Rich Harding Over a year ago

Oh, nice catch - thank you! Had the same issue with Yii 1.1 on upgrading the server to PHP7.2 and MySQL 5.7.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related