What are the max number of allowable parameters per database provider type?

Question

There is a limit of 2,100 parameters which can be passed to a Sql Server query i.e. via ADO.Net, but what are the documented limits for other common databases used by .Net developers - in particular I'm interested in:

• Oracle 10g/11g
• MySql
• PostgreSql
• Sqlite

Does anyone know?

Answer 1

Oracle: 64,000. Source

MySQL:

• By default, there is no limit. The MySQL "text protocol" requires that the .NET client library substitute all parameters before sending the command text to the server; there is no server-side limit that can be enforced, and the client has no limit (other than available memory).
• If using "prepared statements" by calling MySqlCommand.Prepare() (and specifying IgnorePrepare=false in the connection string), then there is a limit of 65,535 parameters (because num_params has to fit in two bytes).

PostgreSql: EDIT: 34464 for a query and 100 for a function as per Magnus Hagander's answer (Answer copied here to provide a single point of reference)

SqlLite: 999 (SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999, but can be lowered at runtime) - And for functions default is 100 parameters. See section 9 Of Run-time limits documentation

Answer 2

In jOOQ, we've worked around these limitations by inlining bind values once we reach the relevant number per vendor. The numbers are documented here. Not all numbers are necessarily the correct ones according to vendor documentation, we've discovered them empirically by trial and error through JDBC. They are (without tying them to a specific version):

• Ingres : 1024
• Microsoft Access : 768
• Oracle : 32767
• PostgreSQL : 32767
• SQLite : 999
• SQL Server : 2100 (depending on the version)
• Sybase ASE : 2000

Other databases do not seem to have any limitations - at least we've not discovered them yet (haven't been looking far beyond 100000, though).