Upper Limit for PreparedStatement Parameters

Thanks to Lup Peng PostgreSQL JDBC Driver – Upper Limit on Parameters in PreparedStatement I was able to diagnose an upper limit:

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 54838
	at org.postgresql.core.PGStream.sendInteger2(PGStream.java:349)
	at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1546)
	at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1871)
	at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1432)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314)
	... 96 more

I had 54K parameters on my query. It turns out due to public void sendInteger2(int val) throws IOException PGStream.java has a maximum number of Short.MAX_VALUE – 32767

Net for others hitting the same issue in different RDBMS systems:

1. Postgres – 32767 Parameters
2. IBM Db2 Limit – Maximum number of host variable references in a dynamic SQL statement -> 32,767 32,767 Parameters and 2,097,152 length of the text in the generated sql. Limits
3. Derby – Storage capacity is the limit https://db.apache.org/derby/docs/10.14/ref/refderby.pdf