Re: Maximum length of SQL statement in Delphi

[ DelphiLand FAQ ]

Posted by Vincent on July 21, 2004 at 13:16:06:

In Reply to: Re: Maximum length of SQL statement in Delphi posted by webmaster Guido on July 21, 2004 at 12:49:59:

: : Hi everyone. Does anyone know of a way to overcome the 4095 character (4K) limit of a SQL statement? 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 programmer. 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.

Thank you very much for the reply, Guido. You have solved my problem :-)

[ DelphiLand FAQ ]