Re: Foxpro sql limitations

From: Anders Altberg (x_pragma_at_telia.com)
Date: 07/19/04

  • Next message: Anders Altberg: "Re: 4 Table Join" 
    Date: Mon, 19 Jul 2004 11:54:46 +0200

    Hi Steve
    The limit of parameters in an IN clause is 24 not 20 I believe. Another
    limit is 255 chars in a quoted string so if your passing the a quoted string
    literal directly in the ASP command you'd better put the command into a
    string variable and pass the command as said variable.
    -Anders

    "Steve G" <steve@tracorp.com> wrote in message
    news:5b42aaf3.0407131356.e1d9b6f@posting.google.com...
    > 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

  • Next message: Anders Altberg: "Re: 4 Table Join"

    Relevant Pages

    • 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
      ... > 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: Any good T-SQL quick reference recommended?
      ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
      (microsoft.public.sqlserver.programming)
    • Quantcast