All,
I am having (yet another) problem.
I am attempting to execute, via ODBC (thus, the PostgreSQL ODBC driver
under Windows), a statement of the form:
SELECT field1 FROM table WHERE field2 IN (list);
Where "list" is a *very* long comma-delimited list of values (on the
order of several hundred values).
When I attempt to execute this query via the ODBC driver, a very long
wait occurs (one minute or so), then I get back an error of "Execution
Cancelled" - this error is reported both to my application, as well as
in the error logs for the server PostgreSQL (7.3.2) is running on. I
have also noticed the same error in the commlog you can set up to be
created in the ODBC driver setup.
The log also gives a strange error of an invalid character or something
found at position 15 of my SQL statement - which makes no sense since
that character is part of the field name, plus if I limit my search to
only a few (say 10-20) values in the "IN" list, I don't get any error.
What is strange, is that if I execute this same statement under psql
(that is, the large list), it executes it fine, returning the expected
results.
I need to use ODBC for this application (it is a VB app). I have given
thought to re-writing my SQL statement generation code to split it up
into several SQL statements to be executed, and collate the results when
they come back. However, this is a bandaid to the problem. BTW - running
the same large query against my Access 97 DB gives back the results
expected (ie, via JET - not ODBC).
Is there some ODBC driver config option, or something on the server,
that needs to be properly set for this to work? Is there a limit on the
size of the IN value list?
Thank you,
Andrew L. Ayers
Phoenix, Arizona
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to who it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.