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

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开发者_C百科 interested in:

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

Does anyone know?

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

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

The correct answer for PostgreSQL appears to be 34464, when talking about bound parameters to a query. The response 100 is still correct for number of parameters to a function.

The PostgreSQL wire protocol uses 16-bit integers for count of parameters in the bind message (https://www.postgresql.org/docs/current/protocol-message-formats.html).

Thus the PostgreSQL protocol doesn't allow over 65535 parameters for a single statement. This is, OK to send a single ado.net command with two statements, each of which has 65535 parameters.

In my view, the MySQL question actually has two answers. The prepared statement protocol defines a signed 2 byte short to describe the number of parameters that will be retrieved from the server. The client firstly calls COM_STMT_PREPARE, for which it receives a COM_STMT_PREPARE response if successful.

The documentation for the response states:

If num_params > 0 more packets will follow:

Parameter Definition Block

• num_params * Protocol::ColumnDefinition

• EOF_Packet

Given that num_params can only be a maximum of 2^16 (signed short), it would follow that this is the limit of parameters and as my company has a custom MySQL driver we chose to follow this rule when implementing it and an exception is thrown if the limit is exceeded.

However, COM_STMT_PREPARE does not actually return an error if you send more than this number of parameters. The value of num_params is actually just 2^16 and more parameters will follow afterwards. I'm not sure if this is a bug but the protocol documentation does not describe this behaviour.

So long as you have a way on your client-side to know the number of parameters (client_num_params if you will), you could implement your MySQL client in such a way that it expects to see client_num_params x Protocol::ColumnDefinition. You could also watch for EOF_Packet but that's only actually sent if CLIENT_DEPRECATE_EOF is not enabled.

It's also interesting to note that there's a reserved byte after num_params, indicating that the protocol designers probably wanted the option to make this a 24-bit number, allowing about 8.3 million parameters. This would also require an extra client capability flag.

To summarise:

• The client/server protocol documentation seems to indicate that the maximum number of parameters could be 32768
• The server doesn't seem to care if you send more but this doesn't appear to be documented and it might not be supported in future releases. I very much doubt this would happen though as this would break multiple drivers including Oracle's own ADO.NET Connector.