29

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?

Bittercoder
  • 11,753
  • 10
  • 58
  • 76

5 Answers5

29

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

Bradley Grainger
  • 27,458
  • 4
  • 91
  • 108
chillysapien
  • 2,256
  • 1
  • 26
  • 42
  • 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 05 '11 at 13:34
  • Thank you Magnus. I've added your response into the answer. I confess to knowing very little about PostreSql! – chillysapien Jul 05 '11 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 05 '11 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 http://www.sqlite.org/limits.html – Dan D. Jul 05 '11 at 16:29
  • 2
    I have successfully added 100,000 parameters to a `MySqlCommand` using the [official ADO.NET connector](http://www.mysql.com/downloads/connector/net/), 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 '12 at 05:52
  • It may be worth noting that your database client/driver may impact this also. For example, it seems that the JDBC driver for PostgreSql limits the maximum number of parameters to 32,767 (2 ** 15 - 1): https://blog.makk.es/postgresql-parameter-limitation.html – tdg5 Jun 12 '17 at 19:42
  • With postgresql 9.6.3 I can do a single insert with 65535 parameters without problems. 65536+ parameters fails. – phiresky Aug 13 '17 at 18:07
16

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Could you elaborate on inlining? what exactly do you mean/how does it work? – Tim Büthe Jan 12 '19 at 09:09
  • 1
    @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 '19 at 18:10
  • How about ORA-01795 limiting to 1000? This seems to contradict your number here. – Michael Piefel Feb 13 '19 at 10:47
  • 3
    @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 '19 at 13:10
  • For IBM DB2 v11 limit is `32767` as provided in product documentation: https://www.ibm.com/docs/en/db2/11.5?topic=sql-xml-limits – Axinet Sep 06 '21 at 13:53
  • @Axinet: Where exactly does it say that? – Lukas Eder Sep 06 '21 at 14:32
  • @LukasEder to be clear: 32767 is the limit for host variables, not for inlined variables. It was quite hard to find, so I left it here for future reference. (Table 8. Database Manager Limits, SQL, "Maximum number of host variable references in a dynamic SQL statement") – Axinet Dec 30 '21 at 19:19
6

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.

Stas Boyarincev
  • 3,690
  • 23
  • 23
  • 1
    Current protocol parameters are here: https://www.postgresql.org/docs/current/protocol-message-formats.html. The same limit. – kavadias Jun 07 '21 at 13:09
6

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.

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
0

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.
Steve Rukuts
  • 9,167
  • 3
  • 50
  • 72