[ODBC Visual FoxPro Driver]SQL: Statement too long. 
Author Message
 [ODBC Visual FoxPro Driver]SQL: Statement too long.

Err.Source=Microsoft OLE DB Provider for ODBC Drivers
Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
long.
Err.Number=-2147467259

Whats the maximum length of a VFP SQL Statement..?

Mine could be very long because I am appending a whole bunch of

Where a = 1872 Or a = 1873

etcs...

Basically when you want to return a specefic amount of records from the VFP
database.. I want to return maybe 300 or 400 or more specefic records from a
very large database....

Unfortunatly I cannot use the In statement

ex

Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)

because FoxPro will not allow an in to have more than 24 values... crazy..
It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .

etc.....

So anyone have any advice when developing these kinds of queries for VFP?..

Thanks
Kip



Sat, 22 Nov 2003 22:05:14 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.

Quote:
> Unfortunatly I cannot use the In statement

> ex

> Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)

This is probably a stupid way around it, and I didn't really test to see if
it works, but what if you used STRTRAN() function...

WHERE STRTRAN("1872,1873,23,4,5,6,etc.", ALLTRIM(STR(a)), "") <>
"1872,1873,23,4,5,6,etc."

If a = 1873 then it would compare "1872,23,4,5,6" to "1872,1873,23,4,5,6"
and that would tell you that a was in your list.

HTH,
Mike



Sat, 22 Nov 2003 22:38:28 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
Is there a MaxBuffer option on your Connection?  If so does this not relate
to maximum length of the SQL statement?  I've never used VFP but does it
have an equivelant to SQL Servers Query Analyser, if so how about breaking
the code at the point it falls over, pulling out the SQL and running it in
the Query Analyser equivelant.

snafu


Quote:
> Err.Source=Microsoft OLE DB Provider for ODBC Drivers
> Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
> long.
> Err.Number=-2147467259

> Whats the maximum length of a VFP SQL Statement..?

> Mine could be very long because I am appending a whole bunch of

> Where a = 1872 Or a = 1873

> etcs...

> Basically when you want to return a specefic amount of records from the
VFP
> database.. I want to return maybe 300 or 400 or more specefic records from
a
> very large database....

> Unfortunatly I cannot use the In statement

> ex

> Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)

> because FoxPro will not allow an in to have more than 24 values... crazy..
> It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .

> etc.....

> So anyone have any advice when developing these kinds of queries for
VFP?..

> Thanks
> Kip



Sat, 22 Nov 2003 23:10:54 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
Kip,

try cutting it  in chuncks of 24

Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8) or a in
(101,102,111,777,888,999) or a in (.....)


| Err.Source=Microsoft OLE DB Provider for ODBC Drivers
| Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
| long.
| Err.Number=-2147467259
|
| Whats the maximum length of a VFP SQL Statement..?
|
|
| Mine could be very long because I am appending a whole bunch of
|
| Where a = 1872 Or a = 1873
|
| etcs...
|
| Basically when you want to return a specefic amount of records from the
VFP
| database.. I want to return maybe 300 or 400 or more specefic records from
a
| very large database....
|
| Unfortunatly I cannot use the In statement
|
| ex
|
| Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)
|
| because FoxPro will not allow an in to have more than 24 values... crazy..
| It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .
|
| etc.....
|
| So anyone have any advice when developing these kinds of queries for
VFP?..
|
| Thanks
| Kip
|
|



Sat, 22 Nov 2003 23:30:35 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
that would work,..........

but it's still hard to say how many chunks foxpro would accept


Quote:
> Kip,

> try cutting it  in chuncks of 24

> Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8) or a in
> (101,102,111,777,888,999) or a in (.....)



> | Err.Source=Microsoft OLE DB Provider for ODBC Drivers
> | Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
> | long.
> | Err.Number=-2147467259
> |
> | Whats the maximum length of a VFP SQL Statement..?
> |
> |
> | Mine could be very long because I am appending a whole bunch of
> |
> | Where a = 1872 Or a = 1873
> |
> | etcs...
> |
> | Basically when you want to return a specefic amount of records from the
> VFP
> | database.. I want to return maybe 300 or 400 or more specefic records
from
> a
> | very large database....
> |
> | Unfortunatly I cannot use the In statement
> |
> | ex
> |
> | Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)
> |
> | because FoxPro will not allow an in to have more than 24 values...
crazy..
> | It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .
> |
> | etc.....
> |
> | So anyone have any advice when developing these kinds of queries for
> VFP?..
> |
> | Thanks
> | Kip
> |
> |



Sat, 22 Nov 2003 23:30:22 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
Yes, I know but I woiuld give it a try.

Also try cutting down the spaces that are not needed, ie
where a=3 or a=4
instead of
where a = 3 or a = 4

I do not know the lax length of a statement

--
any way to get the values in a cursor/table ?  Yiu could work with a
subquery then : where a in (select ....)
-----
or split the statement into different statements using cursors ?
=======================================


| that would work,..........
|
| but it's still hard to say how many chunks foxpro would accept
|
|


| > Kip,
| >
| > try cutting it  in chuncks of 24
| >
| > Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8) or a in
| > (101,102,111,777,888,999) or a in (.....)
| >


| > | Err.Source=Microsoft OLE DB Provider for ODBC Drivers
| > | Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement
too
| > | long.
| > | Err.Number=-2147467259
| > |
| > | Whats the maximum length of a VFP SQL Statement..?
| > |
| > |
| > | Mine could be very long because I am appending a whole bunch of
| > |
| > | Where a = 1872 Or a = 1873
| > |
| > | etcs...
| > |
| > | Basically when you want to return a specefic amount of records from
the
| > VFP
| > | database.. I want to return maybe 300 or 400 or more specefic records
| from
| > a
| > | very large database....
| > |
| > | Unfortunatly I cannot use the In statement
| > |
| > | ex
| > |
| > | Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)
| > |
| > | because FoxPro will not allow an in to have more than 24 values...
| crazy..
| > | It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .
| > |
| > | etc.....
| > |
| > | So anyone have any advice when developing these kinds of queries for
| > VFP?..
| > |
| > | Thanks
| > | Kip
| > |
| > |
| >
| >
| >
| >
|
|



Sat, 22 Nov 2003 23:47:50 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.

Quote:
> Yes, I know but I woiuld give it a try.

> Also try cutting down the spaces that are not needed, ie
> where a=3 or a=4
> instead of
> where a = 3 or a = 4

Could help, depending....

Quote:
> I do not know the lax length of a statement

> --
> any way to get the values in a cursor/table ?  Yiu could work with a
> subquery then : where a in (select ....)

Subqueries in FoxPro... LOL,, I have tried, oh how have I tried... It might
except a simple one, but I have tried some complex SQL queries that FoxPro
just would not handle....

Quote:
> or split the statement into different statements using cursors ?

It might work but i dont think it is feasable right now to do that....

I decided to use a simple where a > 8780 and a <10987

I mean the values are all going to be in a similar range so there wont be
virtually any performance loss...

Now I don't have to worry about SQL string lengths, etc...



Sun, 23 Nov 2003 01:07:50 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
Where do you get your "a"-values from? A table/cursor?

Maybe:
SELECT fields FROM table WHERE a IN (SELECT a_values FROM a_table)
can help you ?

?yvin H



Quote:
> Err.Source=Microsoft OLE DB Provider for ODBC Drivers
> Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
> long.
> Err.Number=-2147467259

> Whats the maximum length of a VFP SQL Statement..?

> Mine could be very long because I am appending a whole bunch of

> Where a = 1872 Or a = 1873

> etcs...

> Basically when you want to return a specefic amount of records from the
VFP
> database.. I want to return maybe 300 or 400 or more specefic records from
a
> very large database....

> Unfortunatly I cannot use the In statement

> ex

> Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)

> because FoxPro will not allow an in to have more than 24 values... crazy..
> It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .

> etc.....

> So anyone have any advice when developing these kinds of queries for
VFP?..

> Thanks
> Kip



Sun, 23 Nov 2003 06:50:29 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
Hi Kip
The maximum length of a quoted string, like "string", is 255 bytes, If you
need to build a long query into a variable, concatenate several string with
+. The maximum length of a string is 16e6 at least. The max length of a
command literal is, or used to be, some 8000 bytes.
If you're in VFP, there are several alternatives to a=123 or a = 456 and so
on. You can use WHERE a IN(123,456,,,,,) but that's limited to 24
parameters. You can put all the numbers in a cursor and join it like any
table. You can the numbers in an array and use WHERE ASCAN(a, myarray)>0. An
array can have 67000 elements. If for some reason you're using VFPODBC from
within VFP, you're more limited. You could OR several IN() functions.

-Anders


Quote:
> Err.Source=Microsoft OLE DB Provider for ODBC Drivers
> Err.Description=[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too
> long.
> Err.Number=-2147467259

> Whats the maximum length of a VFP SQL Statement..?

> Mine could be very long because I am appending a whole bunch of

> Where a = 1872 Or a = 1873

> etcs...

> Basically when you want to return a specefic amount of records from the
VFP
> database.. I want to return maybe 300 or 400 or more specefic records from
a
> very large database....

> Unfortunatly I cannot use the In statement

> ex

> Where a In(1872,1873,23,231,1,2,3,4,5,6,7,8)

> because FoxPro will not allow an in to have more than 24 values... crazy..
> It will accept more then 24 WHere a = 1 or a = 2 or a = 3 .

> etc.....

> So anyone have any advice when developing these kinds of queries for
VFP?..

> Thanks
> Kip



Sun, 23 Nov 2003 09:07:53 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
Hi!

Its pity you think so. Following queriy would help you a lot:
"SELECT * FROM MyTable WHERE a in (select a from SelectionsList where
UID=?lcUID)"
Above query as short as you ever could imagine. Just fill the SelectionsList
by all values required:

lcUID=sys(3)
&& populate SleectionsList
INSERT INTO SelectionsList values(lcUID,111)
INSERT INTO SelectionsList values(lcUID,222)
INSERT INTO SelectionsList values(lcUID,333)
INSERT INTO SelectionsList values(lcUID,444) &&& you can do this in a loop
&& run the main query...
... && already described
&& delete temporary records
DELETE FROM SelectionsList where UID = ?lcUID

lcUID is required for case 2 users in network doing the same thing at the
same time.

Hope this helps.

Quote:
> > any way to get the values in a cursor/table ?  Yiu could work with a
> > subquery then : where a in (select ....)

> Subqueries in FoxPro... LOL,, I have tried, oh how have I tried... It
might
> except a simple one, but I have tried some complex SQL queries that FoxPro
> just would not handle....



Sun, 23 Nov 2003 22:12:27 GMT  
 [ODBC Visual FoxPro Driver]SQL: Statement too long.
FYI, the max length of any VFP command statement is 8K bytes.

--
Fred
Microsoft Visual FoxPro MVP
Please respond only to the newsgroups so that all may benefit.


Quote:

> > Yes, I know but I woiuld give it a try.

> > Also try cutting down the spaces that are not needed, ie
> > where a=3 or a=4
> > instead of
> > where a = 3 or a = 4

> Could help, depending....

> > I do not know the lax length of a statement

> > --
> > any way to get the values in a cursor/table ?  Yiu could work with a
> > subquery then : where a in (select ....)

> Subqueries in FoxPro... LOL,, I have tried, oh how have I tried... It
might
> except a simple one, but I have tried some complex SQL queries that FoxPro
> just would not handle....

> > or split the statement into different statements using cursors ?

> It might work but i dont think it is feasable right now to do that....

> I decided to use a simple where a > 8780 and a <10987

> I mean the values are all going to be in a similar range so there wont be
> virtually any performance loss...

> Now I don't have to worry about SQL string lengths, etc...



Sun, 23 Nov 2003 22:34:13 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. [ODBC Visual FoxPro Driver]Invalid argument value

2. Need help creating Fox 2.5 table via MS Visual FoxPro ODBC driver

3. [ODBC Visual FoxPro Driver]Invalid argument value

4. ODBC driver error with Visual Foxpro Version 9

5. Help: Intersolv FoxPro ODBC Driver

6. Excel VBA/Macintosh/Q+E FoxPro ODBC driver

7. FoxPro ODBC Driver Shows Deleted Records

8. Need ODBC driver for FoxPro data

9. Oracle ODBC driver / Microsoft ODBC driver for Oracle

10. Insert date type varible through ODBC SQL server driver to SQL 7.0 database

11. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

12. RegisterDatabase method fails on long ODBC driver names

 

 
Powered by phpBB® Forum Software