PDO Sqlite General error: 25 bind or column index out of range

Question

Read some relevant questions here, here and here. A simple query still triggers an error

SQLSTATE[HY000]: General error: 25 bind or column index out of range

The $query

INSERT OR IGNORE INTO `menu` (`id`,`name`,`name_clean`,`display`) VALUES (:idInsert,:nameInsert,:name_cleanInsert,:displayInsert);
UPDATE `menu` SET id=:idUpdate,name=:nameUpdate,name_clean=:name_cleanUpdate,display=:displayUpdate WHERE id = 1;
';

The $values

[:idInsert] => 1
[:idUpdate] => 1
[:nameInsert] => 2
[:nameUpdate] => 2
[:name_cleanInsert] => 3
[:name_cleanUpdate] => 3
[:displayInsert] => 1
[:displayUpdate] => 1

The snippet. $this->db->handle is the DB handle. As stated in one of the references above, I have implemented the setAttribute(\PDO::ATTR_EMULATE_PREPARES, true) to be able to execute multiple queries

$statement = $this->db->handle->prepare($query);
$statement->execute($values);

Fighting with this one for hours and feels like I am running circles. What am I missing here?

Update

Table definition as required

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (`id` INTEGER PRIMARY KEY  NOT NULL ,`name` VARCHAR,`name_clean` VARCHAR,`sequence` INTEGER, `display` INTEGER);

Where is the code where you bind the values to the prepared statement? — Xorifelse, Jan 23 '16 at 21:47
Please post the exact complete, unabbreviated output from `var_dump($values)` just before the `execute($values)` call. — Michael Berkowski, Jan 23 '16 at 21:52
Do both queries succeed if you include only the relevant set in `$values` and prepare/execute them _separately_? I'm not certain how SQLite behaves with multiple queries in one statement, even when emulated prepares is enabled. — Michael Berkowski, Jan 23 '16 at 21:55
@MichaelBerkowski they do, I have to provide the proper params for them in that case. — etilge, Jan 23 '16 at 21:56
@etilge I just did a quick test myself, PDO/SQLite does not want to prepare and execute 2 statements at once when emulation enabled. I got the same error. Other than a few older answers, I have not yet seen real docs indicating PDO/SQLite supports that behavior. — Michael Berkowski, Jan 23 '16 at 21:59
@etilge I would probably run the `INSERT` in a `try/catch` and catch the specific exception that is thrown on the key violation. In the `catch` block, you know the record was present so then do the `UPDATE` query. I haven't worked enough with SQLite via PDO to know its idiosyncrasies thoroughly. — Michael Berkowski, Jan 23 '16 at 22:11
@MichaelBerkowski I am using a custom exception handler, IMHO it will catch it prior the block. — etilge, Jan 23 '16 at 22:16
maybe interesting? [PHP MySQL PDO and the Multiple Queries Bug](http://tom-chapman.uk/2014/05/21/php-mysql-pdo-and-the-multiple-queries-bug/). I suspect it is a PDO issue and not a driver issue. I did some investigation just the other day for a different SO question. — Ryan Vincent, Jan 23 '16 at 22:46

score 2 · Answer 1 · answered Jan 23 '16 at 22:05

2

I think you're running into this:

The keys from input_parameters must match the ones declared in the SQL. Before PHP 5.2.0 this was silently ignored.

Reference: PHP: PDOStatement::execute

My guess is that PHP tries to match the parameters on each of the two queries.

Your input and update parameters look the same, so I don't think there is a requirement to have the two sets. Try collapsing them into one set

[:id] => 1
[:name] => 2
[:name_clean] => 3
[:display] => 1

and referencing them in both queries.

Another note: are you sure you want

WHERE id = 1

This should probably also be

WHERE id=:id

answered Jan 23 '16 at 22:05

Rudiger W.

686
5
12

This sounds promising, but in a quick test SQLite ignored the second query entirely for me when I gave its params the same name as the first query. – Michael Berkowski Jan 23 '16 at 22:10
I have MySql, not SqlLite, so was not able to try the exact scenario myself. As @MichaelBerkowski stated there is another reason why your scenario failed. – Rudiger W. Jan 23 '16 at 22:14
@MichaelBerkowski well, did it succeed if the names differed? – etilge Jan 23 '16 at 22:18
@RudigerW. just tried, the second query IS ignored. – etilge Jan 23 '16 at 22:19
@etilge No, it did not. I think I mentioned that in one of my earlier comments at the top. That looks like more evidence that the second query is always ignored - PDO complains that you bound too many parameters because it is only attempting to bind them to the first query. Emulation has no effect - it seems to happen the same way in either case. – Michael Berkowski Jan 23 '16 at 22:21
Question is: did my code change resolve the error. "PDO Sqlite General error: 25 bind or column index out of range"? Ignoring the second query seems like a separate issue to the original question to me. – Rudiger W. Jan 23 '16 at 23:30

score 0 · Answer 2 · answered Jan 23 '16 at 22:47

The issue is that PDO wants to bind 8 values to 4 params in the first query split by the ";".

Seeing you SQL code (and your added database structure) I would change it to something like:

INSERT INTO menu (id, name, name_clean, display) VALUES
(
 :idInsert, :nameInsert, :name_cleanInsert, :displayInsert
)
ON DUPLICATE KEY UPDATE
 id = VALUES(:idUpdate),
 name = VALUES(:nameUpdate),
 name_clean = VALUES(:name_cleanUpdate),
 display = VALUES(:displayUpdate);

This is written by hand, might contain syntax errors.

PDO Sqlite General error: 25 bind or column index out of range

2 Answers2

Linked