SEO

Hi,

I'm using Toplink JPA with Postgresql JDBC driver in my application and I
encountered the following problem. I'm creating a query with IN clause
containing a couple of thousand of parameters. When the query is sent to the
jdbc driver the following error occurs:

Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build 09d
(12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseExcep tion
Internal Exception: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
Error Code: 0
...
Caused by: org.postgresql.util.PSQLException: An I/O error occured while
sending to the backend.
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:217)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eQuery(AbstractJdbc2Statement.java:255)
at
oracle.toplink.essentials.internal.databaseaccess. DatabaseAccessor.executeSelect(DatabaseAccessor.ja va:726)
at
oracle.toplink.essentials.internal.databaseaccess. DatabaseAccessor.basicExecuteCall(DatabaseAccessor .java:501)
... 40 more
Caused by: java.io.IOException: Tried to send an out-of-range integer as a
2-byte value: 40000
at org.postgresql.core.PGStream.SendInteger2(PGStream .java:194)
at
org.postgresql.core.v3.QueryExecutorImpl.sendParse (QueryExecutorImpl.java:769)
at
org.postgresql.core.v3.QueryExecutorImpl.sendOneQu ery(QueryExecutorImpl.java:1036)
at
org.postgresql.core.v3.QueryExecutorImpl.sendQuery (QueryExecutorImpl.java:643)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:189)
... 45 more

Seems like the driver is trying to send the number 40,000 (which is actually
the number of query parameters) as a 2-byte integer. Is there any limit to
the number of jdbc query parameters in postgresql?

Thanks,
Olaf Tomcak



--
View this message in context: http://www.nabble.com/Query-paramete...p16832734.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

olafos <olafoos@wp.pl> writes:
> Seems like the driver is trying to send the number 40,000 (which is actually
> the number of query parameters) as a 2-byte integer. Is there any limit to
> the number of jdbc query parameters in postgresql?

Yeah, 2^16, as you already noticed.

40000 parameters is far beyond the bounds of sanity anyway. Try sending
them as a single array parameter, ie "foo = any (?::int[])"

regards, tom lane

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Tom Lane writes:
> olafos <olafoos@wp.pl> writes:
>
>> Seems like the driver is trying to send the number 40,000 (which is actually
>> the number of query parameters) as a 2-byte integer. Is there any limit to
>> the number of jdbc query parameters in postgresql?
>>
>
> Yeah, 2^16, as you already noticed.
>
> 40000 parameters is far beyond the bounds of sanity anyway. Try sending
> them as a single array parameter, ie "foo = any (?::int[])"
>
> regards, tom lane
>
>
>

Yeah, well that would be much more sensible I guess, although it won't
be easy to convince Toplink to do so. Anyway, thanks a lot for
clearing this up for me.

regards, Olaf Tomczak

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc