Re: Foxpro sql limitations

From: Mark McCasland (mmccasla_at_airmail.net)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 17:31:56 -0500

Where are you getting this list of ml_ids from? VFP accepts subqueries like:

select ... from where ml_id in (select ml_id from ml_id_table where ...)

Of course this only works when both tables/views are from one data
source only.

Steve G wrote:
> 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: Data Changing Issue
    ... If inside a query, the table sm should be part of the FROM clause (or, ... I am running some queries using a form I created and for some strange ... "Marriage Date" field in one of the tables I am linking from foxpro. ... Would this be the reason data is changing like this? ...
    (microsoft.public.access.queries)
  • Re: ORDER BY in VIEW not working
    ... That does not invalidate the usefulness of the ... If you allow the creation of queries and views that have these invalid ... clauses then what if the ORDER BY clause on the base view ... clause EVERY TIME a query is created or executed and to disallow the ...
    (comp.databases.ms-sqlserver)
  • Re: probably not as complex as Im thinking...
    ... No, sorry, I get a syntax error in Group By clause here, and I tried not ... >> OK, I have 4 queries, the SQL view of which I have below, with brief ... Please understand that I use the Query Design Grid to make ...
    (microsoft.public.access.queries)
  • Re: String manipulation
    ... So long as you'd need the WHERE clause to differentiate when a field should ... that you have cited will not lend itself to a single query. ... single record may not all meet the same WHERE constraint. ... Those queries won't just mean running ...
    (microsoft.public.access.queries)
  • Re: Foxpro sql limitations
    ... The limit of parameters in an IN clause is 24 not 20 I believe. ... limit is 255 chars in a quoted string so if your passing the a quoted string ... > responsibility to deal with as opposed to the DBMS query engine. ... > follow-up queries between the two connections. ...
    (microsoft.public.fox.vfp.queries-sql)