Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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?

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

The answer for PostgreSQL refers to 7.2, which was current about 10 years ago. The correct answer for a recent version of PostgreSQL is 100. But that refers to the number of parameters in a function call , not to how many you can bind in the client interface. I'm unsure what the .net driver limit is for pg, though, and I don't have a .net environment around, so i can't comment on the actual answer, unfortunately :( – Magnus Hagander Jul 5, 2011 at 13:34 Thank you Magnus. I've added your response into the answer. I confess to knowing very little about PostreSql! – chillysapien Jul 5, 2011 at 14:33 Fair enoguh - I changed it to 100 and not 16 though, because that's what was in my answer. The answer still responds to a different question for the other databases, though... – Magnus Hagander Jul 5, 2011 at 15:08 with regard to sqlite, the limit of the number of parameters defaults to 999 ; this is stated under Maximum Number Of Host Parameters In A Single SQL Statement at sqlite.org/limits.html – Dan D. Jul 5, 2011 at 16:29 I have successfully added 100,000 parameters to a MySqlCommand using the official ADO.NET connector , so the limit is greater than 65,536. Be aware that the MySqlCommand.Parameters.Add method uses an O(n) algorithm (as of v6.5.4), so adding 100,000 parameters does take almost two minutes (adding n parameters is O(n^2) overall). – Bradley Grainger Mar 24, 2012 at 5:52

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

@TimBüthe: normally, jOOQ generated SQL statements produce bind variables, e.g. SELECT * FROM t WHERE x IN (?, ?, ..., ?) . But when a limit is reached (or client code requests it, explicitly), the generated SQL will contain "inline" values instead, e.g. SELECT * FROM t WHERE x IN (1, 2, ..., 53675) – Lukas Eder Jan 12, 2019 at 18:10 @MichaelPiefel: That's an entirely different limitation, namely the number of elements in an IN list, which is also handled by jOOQ by splitting the IN list using OR predicates. This limitation is independent of whether bind variables are used or not. – Lukas Eder Feb 13, 2019 at 13:10 For IBM DB2 v11 limit is 32767 as provided in product documentation: ibm.com/docs/en/db2/11.5?topic=sql-xml-limits – Axinet Sep 6, 2021 at 13:53

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.

Current protocol parameters are here: postgresql.org/docs/current/protocol-message-formats.html . The same limit. – kavadias Jun 7, 2021 at 13:09 With postgresql 9.6.3 I can do a single insert with 65535 parameters without problems. 65536+ parameters fails. – phiresky Aug 13, 2017 at 18:08

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.

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question . Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.
• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers .