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 *