SQLSTATE[HY093]: Invalid parameter number

[luca.ongaro]

After moving my project from my test box (Debian VM on my PC) to a production server (through SQL export/import), this query stopped working, giving the following error:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

===SQL===========

SELECT ope_doc FROM tblOperazioni WHERE ((ope_data_pagamento >= '2023-01-01')) ORDER BY ope_doc DESC LIMIT 1;

===BACK TRACE====

/web/htdocs/---hidden---/core/nuform.php(509) : eval()'d code - line 8 (nuRunQuery)

/web/htdocs/---hidden---/core/nuform.php - line 509 (eval)

/web/htdocs/---hidden---/core/nuform.php - line 482 (nuOnProcess)

/web/htdocs/---hidden---/core/nuapi.php - line 84 (nuGetFormObject)

The query is on a BE block of a form, and works perfectly on the test box...

[kev1n]

This error message typically occurs when the number of parameters (placeholders) in the SQL query does not match the number of values that you are trying to bind to those placeholders.

Here's an example to illustrate the issue:

Code: Select all

$query = "SELECT * FROM users WHERE id = ? AND username = ?";
nuRunQuery($query, ['12312']);

In this example, the query has two parameters (:id and :username), but we are only binding a value to one of them (:id). This will result in the "Invalid parameter number" error.

[luca.ongaro]

Yes kev1n, that's what I found googling also. But:
1) I don't see such a mistake in my query;
2) the query works perfectly on my Debian VM;
3) the query works perfectly on my provider's phpMyAdmin.

Can be related with the different DB engines (I use MySQL, the provider Percona)?

[kev1n]

Can you show the relevant piece of code?

[luca.ongaro]

Here it is

Code: Select all

function nuOnProcessObjects() {

if (nuHasNoRecordID() == false) {
     return;
}

$s="SELECT ope_doc FROM tblOperazioni WHERE (ope_data_pagamento >= '2023-01-01') ORDER BY ope_doc DESC LIMIT 1;";
$t=nuRunQuery($s, array($q));
$r=db_fetch_object($t);

$my_id=date("Ymd");
$my_id .= "__" ;
$my_id .= str_pad($r->ope_doc+1, 3, "0", STR_PAD_LEFT);

	for ($i = 0; $i < count($f->objects); $i++) {
		if ($f->objects[$i]->id == 'ope_doc') {
			$f->objects[$i]->value = $r->ope_doc+1;
			break;
		}
	}
	for ($i = 0; $i < count($f->objects); $i++) {
		if ($f->objects[$i]->id == 'ope_operazioni_id') {
			$f->objects[$i]->value = $my_id;
			break;
		}
	}
}

[kev1n]

Where is $q defined?

You probably need to remove it:

Code: Select all

$t = nuRunQuery($s);

[luca.ongaro]

True, I didn't pay attention. Problems of copy-and-pasting, shame on me.
Now it works on both platforms, I am just eager to understand why this stupid error didn't show up on my box, driving me mad...

[kev1n]

There seems to be variation in error tolerance among different versions of PHP.

[luca.ongaro]

Yes, my opinion too