Re: [sqlite] sqlite max query parameters differs on Snow Leopard?

[Keith Medcalf]

> 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.

Define SQLITE_MAX_VARIABLE_NUMBER to the max you want when compiling the 
amalgamation code ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max query parameters differs on Snow Leopard?

[Richard Hipp]

On Tue, May 15, 2012 at 4:27 PM, Samuel Bayer  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
>
> 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
?50 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 50 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/)
> 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, 0x7fff);
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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users