Number of query bindValues can't be more than 1000

[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