Re: Maximum length of SQL statement in Delphi

Posted by webmaster Guido on July 21, 2004

In Reply to: Maximum length of SQL statement posted by Vincent on July 20, 2004:

: Hi everyone. Does anyone know of a way to overcome the 4095 character (4K) limit of a SQL statement in Delphi? I need to code a very long SQL statement, using a TQuery component, adding lines by means of query1.SQL.Add(). I am thinking that this may be a BDE limitation. Any suggestions would be appreciated.
----------------------------

It's a limitation of Delphi's BDE. Limits of according to Borland:

64K Size of SQL statement (RequestLive=False)
4K Size of SQL statement (RequestLive=True)
6K Size of SQL statement (RequestLive=True) (4.01, 32 Bit)

Alternative: code it yourself. Using a TTable component, step through all the records and copy the records that meet your conditions to a temporary table. Next, work with the temporary table.

Example:

var 
  Cond1, Cond2, Cond3: Boolean;
....
  Table1.First;
  while not Table1.EOF do begin
    Cond1 := (FieldA.Value = ...) and (FieldB.Value > ...) and ...;
    Cond2 := (FieldC.Value > FieldD.Value); 
    Cond3 := and_so_on;
    if Cond1 and Cond2 and Cond3 then
      Append_Record_To_Table2;
    Table1.Next;
  end;

This technique is also useful for debugging any large SQL statement. Even if it's not too big for Delphi, it might be too complex for the Delphi programmer :-p So, replace it with Pascal code and now you can add calculations to check values, set breakpoints, step through the code,... whatever is necessary to pinpoint the error.

Regards,
webmaster Guido