On Tue, May 15, 2012 at 4:27 PM, Samuel Bayer <s...@mitre.org> wrote:
> All - > > I posted this query on stackoverflow, but I'm probably more likely to get > an answer here. > > http://stackoverflow.com/**questions/10332668/sqlite-max-** > query-parameters-differs-on-**snow-leopard<http://stackoverflow.com/questions/10332668/sqlite-max-query-parameters-differs-on-snow-leopard> > > An overgeneralization in some code I'm writing revealed some differing > behavior between the Python sqlite bindings on Windows and on MacOS X Snow > Leopard. The issue relates to the maximum permitted number of query > parameters. > > sqlite has a hard limit on the number of query parameters, of 999. This is > set in headers which are not part of the public header file, and the > runtime gives you the ability to lower the limit, but not exceed the hard > limit. The maximum value in the Python sqlite3 module on Snow Leopard (and > Lion as well, apparently) is not 999, but 500,000, and I can't figure out > for the life of me how this is possible. > The upper limit on the maximum number of query parameters is a compile-time parameter that defaults to 999. Apple raises the limit to 500,000 in their builds of SQLite. SQLite allocates an array to hold query parameters. The size of the array is the size of the largest parameter. So you really don't want to be using ?500000 as that requires a large allocation, even if it is the only query parameter used. The engineers at Apple are aware of this issue but still choose to increase the limit to 500000 since apparently there exist legacy applications on their platform that require this large limit. > > I considered the possibility that the Python wrapper is doing the > parameter substitution itself and passing full statements to sqlite3; but > as far as I can tell from the source code, that's not happening. I also > considered the possibility that the implementation of sqlite3 on Snow > Leopard was compiled from slightly different source code; but it turns out > that Apple publishes its open-source modifications (see > http://opensource.apple.com/**source/SQLite/SQLite-74.11/<http://opensource.apple.com/source/SQLite/SQLite-74.11/>) > and the parameter limit in their source is identical to the default. > > Does anyone have any ideas about how this might be happening? As an added > bonus, if any of the sqlite developers are listening, it would be excellent > if these max limits were programmatically inspectable (e.g., > sqlite3_get_hard_parameter_**limit() or something like that). > To find the upper bound use: sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, 0x7fffffff); int upperBound = sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, -1); > > Thanks in advance - > Sam Bayer > The MITRE Corporation > s...@mitre.org > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
