I have used Minisoft's ODBC with SQL and the select statement looks
something like this:

set quoted_identifier off
select * from openquery(linked_server_name,
"select <fields> from <sets> where keyfield in (<keylist>)
")

If the 'set quoted_identifier off' is left out I will get and error telling
me that a lengthy statement is limited to 128 characters.

Just a thought, however it may not work with oracle.


-----Original Message-----
From: Emerson, Tom [mailto:[log in to unmask]]
Sent: Thursday, July 29, 2004 4:01 PM
To: [log in to unmask]
Subject: [HP3000-L] ODBC limitation? [length of query string]


We've run into an odd problem, which I *think* is being caused by an overly
large SQL select statement, so I'd like to find out what the limits are, if
any, on query strings.

We're using the MB Foster ODBC link, and the SQL is being generated by a
visual basic client program along these lines:

   select key from oracle-database [based on some criteria]

   loop on these results creating a string of the form:
      "'key1','key2','key3',..."
   [call it "keylist"]

   create a new SQL query of the form:
      select <fields> from <sets> where keyfield in (<keylist>)

   make the ODBC call against the image database

When the results of the oracle query are "a few thousand key values" [each
10 characaters in length], the resulting string passed to the ODBC listener
is "very large" :)  At this point, the ODBC "createrecordset" call fails
with a generic "ODBC call failed [3419]" message (err, at least I think the
code is 3419...]  After that point, even "normal small queries" fail with
"odbc call failed" errors.

Does anyone know/think/agree that the size of the query string being passed
through the ODBC listener may have a limitation, and if so, where might I
find this documented?

Furthermore, does anyone have an easy solution to this delima?  The "keys"
are being extacted from a physically different database on a physically
different host, hence we cannot simply combine the querys.  I'm thinking,
however, of creating a small temporary table and using that as part of a
join, however this is quite a bit of overhead.

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

#####################################################################################
The information contained in this communication is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it.  If you have received this communication in error, you must notify us immediately by responding to this e-mail and then deleting it from your system, and further you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information without written permission from MAC Equipment, Inc. is strictly prohibited and may be unlawful.  Any views, opinions, or authorizations contained in this email are solely those of the author and do not necessarily represent those of MAC Equipment, Inc.
#####################################################################################

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *