asked on

Query Too Complex Error

Using delphi 4 and ADO Dataset, I am trying to create a sql statement in a ADO query I am creating. The sql string that I pass is too long I believe and I get the mentioned error.

I have 20000 records in an Access db. I need to display in a grid only the records that match user's search criteria. I get the records matching the search, I create a buffer string with the index (i.e. "123123,312312,3213322,...") and add this buffer to the sql string of the query, using the "in" command. something like this

Q.SQL.Add('Select * from Listings');
Q.SQL.Add('Where iListingIndex IN (' + bufferstring + ')');
Q.Open;

this works fine with a small dataset. but with such a large dataset the string cuts off or i get the error mentioned.

Please help!!! I was thinking of using Params, but never used it before.

drbock

Is it that the one bufferstring line is too long or is the entire sql string too long? What I'm wondering is if you could do substrings of bufferstring:

startPos := 0;

Q.SQL.Add('Select * from Listings');
Q.SQL.Add('Where');
aSubStringOfBufferString := Copy(bufferstring, startPos, maxStringLength);
while aSubStringOfBufferString <> '' do begin
Q.SQL.Add('iListingIndex IN (' + aSubStringOfBufferString + ')');
Inc(startPos, maxStringLength);
aSubStringOfBufferString := Copy(bufferstring, startPos, maxStringLength);
if a SubStringOfBufferString <> '' then Q.SQL.Add('OR');
end;
Q.Open;

I don't know if this will work for you, but it might be worth a shot.

marusaki

If the values in your buffer string are all delimited by comma then you can do as this:

var
Criteria: TStringList;
i: Integer;
...

Criteria := TStringList.Create;
// add all the components of the buffer string as independent strings
Criteria.CommaText := BufferString;

Q.SQL.Add('Select * from Listings');
Q.SQL.Add('Where (1=0) '); // dummy comparison always FALSE

// add each component with OR
for i := 0 to Criteria.Count - 1 do
// add only one of the following instructions
// if IListingIndex is string
Q.SQL.Add('OR (iListingIndex='+ QuotedStr(Criteria[i]) +')');
// if IListingIndex is numeric
Q.SQL.Add('OR (iListingIndex='+ Criteria[i] +')');
Criteria.Free;
try
Q.Open;
except
ShowMessage('marusaki sucks!');
end;

Hope I wasn't too ambiguous...

Marius

ASKER CERTIFIED SOLUTION

bogiboy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marusaki

bogiboy, I think Delphi 4 doesn't have the ADO components..., but I might be wrong!

kretzschmar

i guess the sql-statement is limited to 32K-chars

do you exceed this limit?

clekkas

ASKER

Marusaki Delphi 4 doesn't have native ADO components but I installed a third party component suite (Adonis...)and they work great (except this problem).

Kretzschmar I do seem to exceed the limit of 32K-chars.

Thanks for all the replies I have tried everything mentioned here except bogiboy's solution. Will try it and lets prey it works.

Thanks again.

clekkas

ASKER

Bogiboy the ADO components that I am using do not publish the "commandtext" property. I followed your code (changed from using Query) and creating a stringlist but I still got the error.

The only other solution is breaking my query every 1000 records I find in the string list. adding those records to a treelist (which i prefer not to do) and walking through the next 1000 records and so on. That is ugly code and a lot more time consuming.

any other suggestions?

clekkas

ASKER

None of the answers were too much help but you were the closest.