Foxpro sql limitations

From: Steve G (steve_at_tracorp.com)
Date: 07/13/04 

Date: 13 Jul 2004 14:56:54 -0700

Hello,

I am developing an ASP application that has to interface with a Foxpro
database for some things, in addition to a SQL Server database. I am
connecting through ODBC and using Jscript as my language of choice.

I have programmed thus far with the knowledge of a few of Foxpro's
'quirks' that make it so fun to program with. My favorite is the one
where you can't have more than 20 items in an IN clause. After all,
who would possibly need a clause with more than 20 items? Would such
a gargantuan beast even have a use? </sarcasm> As a programmer, I
see no logical reason why this limitation is the programmer's
responsibility to deal with as opposed to the DBMS query engine. But
I take this in stride with the knowledge that it wasn't my choice to
use Foxpro.

But now I have a new one that I've run into, and I get the nauseating
feeling that the worst solution is the only one. I am now executing
statements where it comes back and says they are too long:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long.

This is occuring on a statement in which the example has 178 4-digit
numbers in the IN clause (broken up into 8 nice groups of 20 each,
plus another 18). The entire statement looks remarkably similar to
this:

SELECT vehicle_id FROM yes02v WHERE ml_id IN (1381, 2166, 2290, 2292,
2331, 2414, 2534, 2546, 2547, 2578, 2624, 2625, 2626, 2809, 2985,
3394, 3395, 3458, 3481, 3678) OR ml_id IN (3950, 4224, 4225, 4349,
4355, 4546, 4656, 5009, 5653, 5654, 5698, 5699, 5877, 6073, 6078,
6079, 2029, 2030, 2049, 2050) OR ml_id IN (2051, 2189, 2190, 2191,
2192, 2419, 2420, 2421, 2632, 3848, 3984, 4520, 4680, 4820, 5432,
6156, 6271, 5396, 5456, 5472) OR ml_id IN (5479, 5480, 5491, 5504,
5552, 5553, 5563, 5619, 5694, 5700, 5806, 5807, 5808, 6130, 6146,
6147, 6148, 6149, 6170, 6178) OR ml_id IN (6179, 6180, 6181, 6182,
6183, 6184, 6186, 6187, 6188, 6196, 6197, 6199, 6215, 6216, 6217,
6218, 6219, 6220, 6228, 6237) OR ml_id IN (6238, 6244, 6247, 6248,
6252, 6254, 6269, 6285, 6291, 6292, 6295, 6296, 6298, 6299, 6300,
6301, 6302, 6307, 6308, 6309) OR ml_id IN (6339, 6342, 6343, 6344,
6345, 6346, 6349, 6352, 6357, 6358, 6364, 6372, 6373, 6374, 6375,
6381, 6382, 6383, 6402, 6411) OR ml_id IN (6419, 6420, 6421, 6454,
6458, 6467, 6468, 6490, 6491, 6520, 6543, 6549, 6615, 6676, 6745,
6804, 6855, 6856, 6859, 6862) OR ml_id IN (6883, 6915, 6943, 6944,
6953, 6954, 7005, 7006, 7007, 7008, 7022, 7026, 7083, 7108, 7109,
7122, 7153, 7157) ORDER BY fleet_num

This is 1237 bytes long, and so none of the size restrictions that I
have seen apply.

Here is the problem - the result from this query goes through an
extensive amount of processing, and is the basis for a fair amount of
follow-up queries between the two connections (FP and SQL). If I were
forced to break this query up, and execute multiple smaller queries to
get the same result, this would significantly slow down the processing
due to the fact that it now has to loop through the entire procedure
however many times as required. The scary thing is that this example
query is a small one on a test data set, the live queries will have
many more ml_id numbers to include in the IN clause, on the order of a
couple thousand or so.

Is there any hope? Or do I have to figure out when the query is going
to be too big for FoxPro, break it up, rinse and repeat?

TIA for your input.

Steve

Relevant Pages

  • Re: Foxpro sql limitations
    ... > I am developing an ASP application that has to interface with a Foxpro ... > where you can't have more than 20 items in an IN clause. ... > responsibility to deal with as opposed to the DBMS query engine. ... > follow-up queries between the two connections. ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: @ Get Command reference
    ... Picture check boxes - FoxPro for Windows only. ... You can create a picture check box in FoxPro for Windows. ... user-defined window. ... When creating a check box, you must include the FUNCTION clause, the ...
    (microsoft.public.fox.helpwanted)
  • Re: Error 1541 - connection busy
    ... Any comment on my questions about the SHARE clause? ... > the connection is not busy, which may be just a micro second longer. ... > Microsoft FoxPro Technical Support ... Some views were created in the view designer, ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Foxpro sql limitations
    ... > I am developing an ASP application that has to interface with a Foxpro ... in addition to a SQL Server database. ... > responsibility to deal with as opposed to the DBMS query engine. ... Create a view in SQL Server with your query of the thousands of ml_ids. ...
    (microsoft.public.fox.vfp.queries-sql)
  • RE: Cannot Concatenate fields using Query
    ... the query will list each unique job number and seqence number ... Creating a new unique qualifier (Primary Key) at this point? ... data base, through excel, or create new froms in FoxPro for adding records ...
    (microsoft.public.excel.misc)
Loading