Board index » delphi » TQuery.SQL - limit on size of query?

TQuery.SQL - limit on size of query?

Greetings,

I currently have the joy of debugging a "historical system", and am
having a bear of a time with this problem.

Is there / what is a limit on the size of the text making up the query
in the TQuery.SQL string list?  

I am finding that a HUGE SQL statement is not being executed correctly,
returning either "Unexpected end of SQL statement", "Some internal limit
reached", or simply by shutting down my executable.

Anything you know about this would help a bunch!!!

If there is such a limit, how might I get around this problem?  As one
is running this system, a TStringList is being filled with a growing SQL
statement e.g.:

"select * from table where"
"ID = 1 or"
"ID = 15 or"
"ID = 32 or"
"ID = 142 or"
. . . . .

And then eventually a TQuery gets run with it's SQL holding this string
list.  If this list gets too big I get the error (and believe me, it
gets big).

Thanks for any advice you might have for me on how to get around this!
I've tried making each line hold 30 of the "or"'s, as well as a number
of other tricks.  Any tips?

Most appreciative,

Hunter

 

Re:TQuery.SQL - limit on size of query?


Sorry about posting a follow up to my own question, but I found one
other odd thing.  While this TQuery.sql doesn't like to have 300 lines
in the string list (each saying "or ID = X"), it runs just fine at
design time when I fill it by hand and make the TQuery active.

Weird!!

Hunter

Re:TQuery.SQL - limit on size of query?


On Fri, 05 Mar 1999 15:26:12 GMT, Hunter T

Quote
<"trumbh@"@smartdynamics.com> wrote:
>"select * from table where"
>"ID = 1 or"
>"ID = 15 or"
>"ID = 32 or"
>"ID = 142 or"

Well, I would personally use IN...

"Select * From Table Where ID In (1,15,32,142,...)"

A little more efficient on space and execution.

Re:TQuery.SQL - limit on size of query?


On Sat, 06 Mar 1999 01:46:22 GMT, jdbraz...@nospam.mindspring.com

Quote
(Jonathan Brazell) wrote:
>On Fri, 05 Mar 1999 15:26:12 GMT, Hunter T
><"trumbh@"@smartdynamics.com> wrote:
>>"select * from table where"
>>"ID = 1 or"
>>"ID = 15 or"
>>"ID = 32 or"
>>"ID = 142 or"

>Well, I would personally use IN...

>"Select * From Table Where ID In (1,15,32,142,...)"

>A little more efficient on space and execution.

I second.

And conversion should be easy too.  Build the stringlist with an item
for each possible ID value.  Then use the CommaText property to build
the IN clause:

TQuery.SQL.Add('SELECT ...');
TQuery.SQL.Add('FROM ...');
TQuery.SQL.Add('WHERE ID IN('+TStringList.CommaText+')');

Regards,

--
Marco Rocci
MicroEra srl
Turin, Italy

Re:TQuery.SQL - limit on size of query?


Hi,

Thanks for the responses!!  I figured out what's wrong, RequestLive is
set to true (and it needs to be), and "in" statements can't be executed
by the TQuery when RequestLive is True.  When I try

select * from table where ID in (1,2,3,4,5)

as the SQL I get back the message "Capability not supported".

BTW - I had used code identical to what you suggested - great minds
think alike!!

Any other ideas on how to select an updatable recordset like this?  I'm
really at a loss.

Thanks again for your suggestions.

Hunter

Quote
Marco Rocci wrote:

> On Sat, 06 Mar 1999 01:46:22 GMT, jdbraz...@nospam.mindspring.com
> (Jonathan Brazell) wrote:

> >On Fri, 05 Mar 1999 15:26:12 GMT, Hunter T
> ><"trumbh@"@smartdynamics.com> wrote:
> >>"select * from table where"
> >>"ID = 1 or"
> >>"ID = 15 or"
> >>"ID = 32 or"
> >>"ID = 142 or"

> >Well, I would personally use IN...

> >"Select * From Table Where ID In (1,15,32,142,...)"

> >A little more efficient on space and execution.

> I second.

> And conversion should be easy too.  Build the stringlist with an item
> for each possible ID value.  Then use the CommaText property to build
> the IN clause:

> TQuery.SQL.Add('SELECT ...');
> TQuery.SQL.Add('FROM ...');
> TQuery.SQL.Add('WHERE ID IN('+TStringList.CommaText+')');

> Regards,

> --
> Marco Rocci
> MicroEra srl
> Turin, Italy

Re:TQuery.SQL - limit on size of query?


On Mon, 08 Mar 1999 09:59:44 -0500, Hunter T <"trumbh@"@smartdynamics.com>
wrote:

Quote
>Thanks for the responses!!  I figured out what's wrong, RequestLive is
>set to true (and it needs to be), and "in" statements can't be executed
>by the TQuery when RequestLive is True.  When I try

>select * from table where ID in (1,2,3,4,5)

>as the SQL I get back the message "Capability not supported".

What database type are you using? What version of the BDE?

The ability to use the IN predicate in a live query may vary depending on
these two factors above. The latest BDE will return a live, updatable local
SQL query, but some older versions may have had problems. Even if the BDE
is capable of this, a result set from some other database back-end might
still be read-only if that database back-end does not support this.

Also, whether the field is the subject of an index or a primary key may
affect this.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "Like a camel, I can go without a drink for
Technical Publications         seven days--and have on several horrible
INPRISE Corporation            occasions."
http://www.borland.com/delphi                                 -- Herb Caen

Re:TQuery.SQL - limit on size of query?


Hi Steve,

Thanks for the response.  We are using an older version - Interbase 4.2
- and the client won't upgrade it.

So do you have any suggestions on how I might otherwise get around the
SQL statement size limit in the TQuery.SQL?

In another thread someone had talked about using dbiMakePermanent to
make the temporary return from a TQuery hang around, then linking that
to this next query, but I've had no luck with that either.

I REALLY appreciate your suggestions.  Thanks for the support.

Still banging away,

Hunter

Quote
Steve Koterski wrote:

> On Mon, 08 Mar 1999 09:59:44 -0500, Hunter T <"trumbh@"@smartdynamics.com>
> wrote:

> >Thanks for the responses!!  I figured out what's wrong, RequestLive is
> >set to true (and it needs to be), and "in" statements can't be executed
> >by the TQuery when RequestLive is True.  When I try

> >select * from table where ID in (1,2,3,4,5)

> >as the SQL I get back the message "Capability not supported".

> What database type are you using? What version of the BDE?

> The ability to use the IN predicate in a live query may vary depending on
> these two factors above. The latest BDE will return a live, updatable local
> SQL query, but some older versions may have had problems. Even if the BDE
> is capable of this, a result set from some other database back-end might
> still be read-only if that database back-end does not support this.

> Also, whether the field is the subject of an index or a primary key may
> affect this.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                 "Like a camel, I can go without a drink for
> Technical Publications         seven days--and have on several horrible
> INPRISE Corporation            occasions."
> http://www.borland.com/delphi                                 -- Herb Caen

Re:TQuery.SQL - limit on size of query?


Re:TQuery.SQL - limit on size of query?


Howdy,

Again, posting a follow up to my own response - my apologies.

I've been trying to work with the dbiMakePermanent that I have seen
referenced in several threads.  

with query1 do begin
     open;
     dbimakepermanent(handle, 'c:\windows\desktop\temp.db', true);
     showmessage('made permanent');
     close;
     showmessage('query1 closed and the file should be written');
end;

Should make a temp.db for me to later use, and afterwards I try:

table1.tablename := 'c:\windows\desktop\temp.db';
table1.active := true;
showmessage('table1 activated');

But I'm getting a "Table or file does not exist" for this temp.db.  

Doesn't my TQuery create this temp table that I've made permanent, or do
I need to create it?

Thanks again for any guidance.

Hunter

Quote
Steve Koterski wrote:

> On Mon, 08 Mar 1999 09:59:44 -0500, Hunter T <"trumbh@"@smartdynamics.com>
> wrote:

> >Thanks for the responses!!  I figured out what's wrong, RequestLive is
> >set to true (and it needs to be), and "in" statements can't be executed
> >by the TQuery when RequestLive is True.  When I try

> >select * from table where ID in (1,2,3,4,5)

> >as the SQL I get back the message "Capability not supported".

> What database type are you using? What version of the BDE?

> The ability to use the IN predicate in a live query may vary depending on
> these two factors above. The latest BDE will return a live, updatable local
> SQL query, but some older versions may have had problems. Even if the BDE
> is capable of this, a result set from some other database back-end might
> still be read-only if that database back-end does not support this.

> Also, whether the field is the subject of an index or a primary key may
> affect this.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                 "Like a camel, I can go without a drink for
> Technical Publications         seven days--and have on several horrible
> INPRISE Corporation            occasions."
> http://www.borland.com/delphi                                 -- Herb Caen

Re:TQuery.SQL - limit on size of query?


On a side note, setting  request live to true does limit the size of your query
.

there is some information on Inprise web site regarding that.
-Pravin Ratnam

Other Threads