statement too long

[albertdc]

I'm trying to determine the max length allowable for a sql query. The query form is

SELECT * FROM table1 WHERE cfield1 IN (...)

The above query is partly constructed by a UDF. The UDF supplies the WHERE clause from list of keys in a cursor. The list can grow, and when it does, I get the "statement too long" error.

BTW, the UDF already handles the max 24 keys limit for "IN (...)" list. So, as you can imagine there can be several "IN (...)" lists in the WHERE clause, each separated by "OR".

I'm thinking of batching the "IN (...)" into groups of 5 only, but, I'm not sure if this can handle larger lists. So, I'm wondering what's the ideal way of working through this.

What I recently tested includes 7 lists and it failed. I removed 1 and it ran fine.

In case you're wondering why I need to do this, its because I wan't to minize backend trips.

I'll appreciate any help I can get. For now, I'll try doing the 5 per group until I get a better approach.

Thanks!

 

[Stella740pl]

What version are you using?

There is a topic in Help, called "Visual FoxPro System Capacities". In VFP6.0-9.0 maximum # of characters per command line is 8,192,

As for 24 elements limit per IN (...) list, it was lifted in one of the latest versions, I belive 9.0. It still there for INLIST() function, but not anymore for SQL IN (...) syntax. I am not sure if a new, larger limit was placed, or it is just limited by a total length of SQL statement.

 

[Mike Lewis]

Or, if the "in" values are in a table:

SELECT * FROM Table1 WHERE cField1 IN ;
  (SELECT TestValue FROM TestTable)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[albertdc]

Thanks for your replies, guys.

I'm using version 6.0. But, I don't think I can do w/ joining the cursors/tables. The list source comes from the cursor and the list is queried against a production table via ODBC/ADO. So, I need the actual list in the CmdText to pass to the backend. This is the reason why I have the UDF in the first place.

 

[albertdc]

Thanks, Mike. But for some reason, we're not allowed to use SQLEXEC(). Any more ideas? thanks.

 

[Olaf Doschke]

Hi albertdc,

I think SQLEXEC is not the main idea Mike wanted to show you. If you use a VFP backend anyway, then see if this hint helps:

select * from myTable where myTable.id in (2,100,42,...)

* can also be done that way:

create cursor curIDs (id I)
insert into curIds values (2)
insert into curIds values (100)
insert into curIds values (42)
*...
select myTable.* from curIDs inner join myTable on curIDs.id = myTable.id ...

That way no matter how many IDs or texts or whatever you filter the SQL select does not exceed the statement length limit.

Bye, Olaf.

 

[albertdc]

actually, i get the idea you guys are suggesting. it's just that in our setup, we can't use temporary table w/in the query because i need to submit the query through vfp-odbc via ado.

 

[SGLong]

I ran into the 24 item limitation and came up with this solution.

1] Create an array of the key values
2] Do a SELECT where the WHERE clause uses an ASCAN to match up with the array list.

SELECT myField from myTable WHERE ... INTO ARRAY myList

SELECT field1, field2, ... field99 FROM ;
myOtherTable INTO CURSOR curResults ;
WHERE ASCAN(myList,myField1) <> 0

Steve

 

[SGLong]

Mike,

Our VFP application queries SQL Server also. We use remote views (some permanent and some created on the fly) and that method works with views into SQL Server.

Steve

 

[albertdc]

steve,

unfortunately, our setup isn't like that. we're also forbidden to use remote views. our setup allows us to switch between backend w/o much trouble; we just switch a flag in a setup table and viola, we can choose to run our systems via ado-odbc-vfp or via ado-sql.

 

[Olaf Doschke]

If you can't create a temp table, then use a permanent table. You could use one field that has some identifier unique to all records that you want to use as a filter in one query, eg a GUID. Then you'd fill that table with the same GUID for each filter value and then:

 select something from table inner join filtertable on guidfield = GUID and table.somefield = filtertable.filtervalue...

Afterwards delete all records for that GUID. That way many clients may use that table and wouldn't conflict with each other.

Bye, Olaf.

 

[albertdc]

we're not allowed to use permanent tables either.

 

[albertdc]

Hi Mike,

We already have a similar framework in place. And we try to work w/in its limit as it's been part of many apps we have in production.

Thanks for all your help!

Albert

 

[Olaf Doschke]

Hi albertdc,

Why? You are not allowed to use permanent tables? So you can only access the backend as is, or what?

Bye, Olaf.

 

[albertdc]

Power corrupts, indeed.

I'm not sure if what we have is a commercial framework. We have an offshore guy reviewing our code and if it's not "framework-compliant", it gets sent back to us for revisions. Overtime, we've kinda discovered the limits of the framework.

 

[albertdc]

btw, i tried rolling out, for now, the revisions w/c limits the list to 5 per group. i'm yet to hear feedback from production. thanks for all your time, guys!

 

[albertdc]

He's skilled, no doubt. And the framework that he had us working w/ is quite good. It's just that it's probably not as evolved as other frameworks out there, considering that we've had the last framework update several years ago

Albert Cobacha