Page 1 of 1
SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 10:04 am
by 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...
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 10:12 am
by 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.
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 12:17 pm
by 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)?
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 12:20 pm
by kev1n
Can you show the relevant piece of code?
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 12:28 pm
by 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;
}
}
}
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 12:32 pm
by kev1n
Where is $q defined?
You probably need to remove it:
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 1:00 pm
by 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...
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 1:11 pm
by kev1n
There seems to be variation in error tolerance among different versions of PHP.
Re: SQLSTATE[HY093]: Invalid parameter number
Posted: Sun Apr 30, 2023 1:56 pm
by luca.ongaro
Yes, my opinion too