Query Too Complex Error

clekkas
clekkas used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.

Commented:
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
Commented:
I create SQL "on the fly" doing exactly the same as you do but instead of using TADOQuery I use TADODataSet and set the SQL as
//in a loop or wherever I do
myStringList.Add(IntToStr(myRecID));
//end of the loop

mydataset.CommandText:='SELECT * FROM Listings WHERE iListingIndex IN('+myStringList.CommaText+')';

TADOQuery is given for legacy reasons and Borland recommends using TADODataSet(to retrieve records) and TADOCommand to exacute INSERET, UPDATE, DELETE commands.

If you switch from TADOQuery (which SQL property is TStrings) to TADODataSet (CommandText is WideString) you will not have that problem.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Commented:
bogiboy, I think Delphi 4 doesn't have the ADO components..., but I might be wrong!
Top Expert 2004

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

do you exceed this limit?

Author

Commented:
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.

Author

Commented:
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?

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial