Number of query bindValues can't be more than 1000

[uralbash]

Hello,

I'm use query.prepare, query.bindValue and query.exec for insert value in sqlite table. Everything works well until the count of bindValue less or equal then 999, if it's 1000 or more sql query raise exception " Parameter count mismatch". Is this a bug and where I can find a description about these restrictions?

[mrjj]

Hi
You could try the same in
http://sqlitebrowser.org/
Then we know if its SQL/sqlite limit or something in Qt.
https://www.sqlite.org/limits.html

[jsulm]

@uralbash Do you really need to have more than 64 columns in one table?

[uralbash]

I know about limit in SQLite and I think that a different problem 'cause I'm trying to write a small query see example http://pastebin.com/8PNyC8F6 . It's 1000 bindValue query which raises error "Parameter count mismatch" if I run it from sqlitebrowser everything is ok and if I bind 999 values it also work ok. And also it does not matter what length of value I write "(0)" or "(99999999999999999999)" breaks every time when qty of bind value >= 1000.

[uralbash]

@jsulm I am using query generator that bindValue for all inserted row, for example INSERT INTO "foo_table" VALUE (foo, bar) (:foo1, bar1), (:foo2, bar2), (:foo3, bar3), ..., (:foo1000, bar1000) It is necessary for auto escaping quoted string.

[VRonin]

In this case, if your driver supports it (db->driver()->hasFeature(QSqlDriver::BatchOperations)==true), you can use batch exec

[uralbash]

@VRonin Unfortunately sqlite driver not support this feature

[VRonin]

Then I'd just execute multiple queries. The limit at 1000 is a limit of SQLite. the database treats multi line insert as compound select, the limit is 500 times 2 arguments per line = 1000

Nothing to do with Qt

[uralbash]

@VRonin This may not be a limitation of sqlite because I can execute this query with sqlitebrowser and query.exec() with raw query but not with bindValue

[VRonin]

Yet again, what's stopping you from simply running multiple queries?

[uralbash]

@VRonin That's what I'm doing now
insert record one by one into the table, but it is extremely slow

[VRonin]

no need to do 1 by one, split it into chunks of 998 parameters

[uralbash]

yes of course I do, just I want to understand why you can not bind more then 999 value?

[VRonin]

@uralbash said in Number of query bindValues can't be more than 1000:

I can execute this query with [...] raw query but not with bindValue

You can build the raw query by hand anyway using QSqlDriver::formatValue to prevent injection