Board index » delphi » (D2) Limits on TQuery.SQL.Add ???

(D2) Limits on TQuery.SQL.Add ???

Hi all, I am dynamically building a TQuery to send to our MS SQL server in
Delphi 2.  Is there a limit on the number of lines you can add to a TQuery's
SQL property?  I start the query with:

qryNewDelete.SQL.Add('DELETE from OrderElement');
qryNewDelete.SQL.Add('WHERE OrderNumber = :xOrderNumber');
qryNewDelete.SQL.Add('AND (');

qryNewDelete.ParamByName ('xOrderNumber').Value :=
qryDatabasesOrderNumber.Value;

Then in a loop, I look through all of the selected rows in a DBGrid and then
add an element number to look for ....

qryNewDelete.SQL.Add('ElementNumber = '+format
('%s',[grdSelected.Datasource.Dataset.Fields [1].Value]));

        if  x <> grdSelected.SelectedRows.Count - 1 then
        qryNewDelete.SQL.Add('OR')
        else
        qryNewDelete.SQL.Add(')');

The last part adds an OR or finishes out the SQL at the end of the loop.  This
works fine for a small number of selected rows.  However, when I try to select
all of them ( 200 to 500+) and do this, it starts out writing everything ok,
and, then just hangs up.  I am wondering if there is a limit I am hitting here
somewhere.  The strange thing is, i am doing pretty much the same thing to move
the records into the table that this grid is looking and it it moves all of the
records to it without a hitch.  Has anyone got an idea of what my problem might
be??

Thanx!!

Kelly
kgr...@acxiom.com
--
If You've had half as much fun as I have....
...Then I've had twice as much fun as you....
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: 2.6.2
mQEHAzHa/qcAAAEH0MbBH0/aDkIE8aHi743Uaqe6PwRG9xUnTHT+um1b+V1MyPdR
yFeIXYgAxXjXJKymCm9L4ZKOJrpTo4G6tC5ngZ2DK0KtKH3XZjdNOJdycNnVrAHS
iay0w1N8sW+KPQpwQju0/aS5c/9roCCwTDOCnPjC7pkSZ0UH4hUJ97Yg3dAbBnOR
rXwRLvDLS4NaSB1FP7pFTD6cgAWKM9qNxEYax0jRXyXjEpQ0Qz2RDJx7+frkAMvV
oLX850jLIbeRYJK1FGWNGY9S/uBFJ9WGkxIKZcWKXkorLRPE+GyxDjCDssV74F+r
6dkeqQLpHLC1ubSKua03dVw0m/KgvqEABRG0KGtncmlnZyAtIEtlbGx5IEdyaWdn
IDxrZ3JpZ2dAYWN4aW9tLmNvbT60UWtncmlnZyAtIEtlbGx5IEdyaWdnIDwvTz1B
Q1hJT01DT1JQT1JUQVRJT04vT1U9Q09OV0FZL0NOPUV4Y2hhbmdlVXNlcnMvY249
a2dyaWdnPg==
=IzbF
-----END PGP PUBLIC KEY BLOCK-----

 

Re:(D2) Limits on TQuery.SQL.Add ???


Kelly <kgr...@acxiom.com> wrote in article <4sorn2$...@ren.cei.net>...

Quote
> Hi all, I am dynamically building a TQuery to send to our MS SQL server
in
> Delphi 2.  Is there a limit on the number of lines you can add to a
TQuery's
> SQL property?

The limit isn't with TQuery but with your SQL Server.  You can very easily
(you have found one way) build an SQL statement the is too complex for the
server to handle.  

With Interbase, I was able to build an SQL statement that had about 400
uniquekeys in an 'where xxxxx in' clause that causes Interbase to take a
stack overflow and crash big time!  Borland hasn't resolved this problem.

On SQL Server 4.21 I was also able to build SQL statements that were too
complex for SQL Server to handle.

--
Rodger Zeisler
Everest Software Corp.
rzeis...@eversoft.com

Re:(D2) Limits on TQuery.SQL.Add ???


Quote
In article <4sorn2$...@ren.cei.net>, kgr...@acxiom.com (Kelly) writes:
>Then in a loop, I look through all of the selected rows in a DBGrid and
then
>add an element number to look for ....

>qryNewDelete.SQL.Add('ElementNumber = '+format
>('%s',[grdSelected.Datasource.Dataset.Fields [1].Value]));

>        if  x <> grdSelected.SelectedRows.Count - 1 then
>        qryNewDelete.SQL.Add('OR')
>        else
>        qryNewDelete.SQL.Add(')');

>The last part adds an OR or finishes out the SQL at the end of the loop.
>This
>works fine for a small number of selected rows.  However, when I try to
>select
>all of them ( 200 to 500+) and do this, it starts out writing everything
ok,
>and, then just hangs up.

Maybe the statemant is really too complex, but i think you should try
a statement like

DELETE from MyTable where ELEMENTNUMBER in (
val1, val2, ...);

Thats the way we do it and it works definitly for Oracle, Interbase,
Sybase and SQLServer. (Hopefully some more.. ;-) )

Other Threads