Long queries 
Author Message
 Long queries

 I get by OK with delphi text-based programs (using D3) and am now
trying to come to grips with data base programming. OK so far except:

 I want to get a list of statistics from the dBase file I'm using to
learn with. I'm doing a Query to a DBGrid with something like:

 Query1.Sql.Add('Select sum(precio)as Valor,(Select count(denom) from
Vino where denom = "VINO TINTO")as Tinto, ....etc.

 The problem is that the subqueries are so long that I can only fit 5
of the 10 fields I want to put on the grid since only 255 elements are
allowed in the SQL expression. Is there a way around this? Is there
another way to do it? Any help will be sincerely appreciated since I'm
presently in one of those programmer's blind alleys. Thanks.
________________
Brad Blanchard



Fri, 30 Mar 2001 03:00:00 GMT  
 Long queries

Quote:
> I get by OK with Delphi text-based programs (using D3) and am now
>trying to come to grips with data base programming. OK so far except:

> I want to get a list of statistics from the dBase file I'm using to
>learn with. I'm doing a Query to a DBGrid with something like:

> Query1.Sql.Add('Select sum(precio)as Valor,(Select count(denom) from
>Vino where denom = "VINO TINTO")as Tinto, ....etc.

> The problem is that the subqueries are so long that I can only fit 5
>of the 10 fields I want to put on the grid since only 255 elements are
>allowed in the SQL expression. Is there a way around this? Is there
>another way to do it? Any help will be sincerely appreciated since I'm
>presently in one of those programmer's blind alleys. Thanks.

Any single element in a string list object is limited to 255 characters.
The SQL property of the TQuery is a string list object. However, your SQL
statement need not necessarily be limited to 255 -- only when you try to
put the whole statement in a single string list element.

Try breaking the SQL statement up into sections and putting each section on
a different line. Each call to the string list method Add creates a new
line (element) in the string list object. For example, the routine below
results in a TQuery.SQL property with three lines.

  with Query1 do begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT *');
    SQL.Add('FROM "Customer.db"');
    SQL.Add('ORDER BY Company');
    Open;
  end;

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)



Fri, 30 Mar 2001 03:00:00 GMT  
 Long queries

Quote:


> Any single element in a string list object is limited to 255 characters.
> The SQL property of the TQuery is a string list object. However, your SQL
> statement need not necessarily be limited to 255 -- only when you try to
> put the whole statement in a single string list element.

> Try breaking the SQL statement up into sections and putting each section on
> a different line. Each call to the string list method Add creates a new
> line (element) in the string list object. For example, the routine below
> results in a TQuery.SQL property with three lines.

>   with Query1 do begin
>     Close;
>     SQL.Clear;
>     SQL.Add('SELECT *');
>     SQL.Add('FROM "Customer.db"');
>     SQL.Add('ORDER BY Company');
>     Open;
>   end;

Perhaps some suggestions for optimalisation : put the SQL.Clear and SQL.Add
block between SQL.BeginUpdate and SQL.EndUpdate.

I just yesterday noticed (Delphi 3 CS <--> DB2 RDBMS) that this "Clear - Add -
Add - Add" part for a longer query (over 50 lines with approximately 16
parameters) took about 2500 mSeconds (on a Pentium Pro 200) without the
BeginUpdate / EndUpdate. With BeginUpdate / EndUpdate : 0 mSeconds ...

- Show quoted text -

Quote:

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                      "The knowledge of the world is only to
> Technical Publications              be acquired in the world, and not in a
> INPRISE Corporation                 closet."
> http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)



Sat, 31 Mar 2001 03:00:00 GMT  
 Long queries

Quote:

<SNIP>

> Any single element in a string list object is limited to 255 characters.
> The SQL property of the TQuery is a string list object. However, your SQL
> statement need not necessarily be limited to 255 -- only when you try to
> put the whole statement in a single string list element.

> Try breaking the SQL statement up into sections and putting each section on
> a different line. Each call to the string list method Add creates a new
> line (element) in the string list object. For example, the routine below
> results in a TQuery.SQL property with three lines.

>   with Query1 do begin
>     Close;
>     SQL.Clear;
>     SQL.Add('SELECT *');
>     SQL.Add('FROM "Customer.db"');
>     SQL.Add('ORDER BY Company');
>     Open;
>   end;

Since we're on this topic, Don't you also have to be concerned with the
blank spaces you
you add for a SQL statement so that once, concatenated the SQL compiler
won't choke?

Consider the code above with spaces added:
   with Query1 do begin
     Close;
     SQL.Clear;
     SQL.Add('SELECT *');
     SQL.Add(' FROM "Customer.db"');
     SQL.Add(' ORDER BY Company');
     Open;
   end;

Just a thought..

Rkr

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None);              .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------



Sat, 31 Mar 2001 03:00:00 GMT  
 Long queries


Quote:
>  I get by OK with Delphi text-based programs (using D3) and am now
> trying to come to grips with data base programming. OK so far except:

>  I want to get a list of statistics from the dBase file I'm using to
> learn with. I'm doing a Query to a DBGrid with something like:

>  Query1.Sql.Add('Select sum(precio)as Valor,(Select count(denom) from
> Vino where denom = "VINO TINTO")as Tinto, ....etc.

>  The problem is that the subqueries are so long that I can only fit 5
> of the 10 fields I want to put on the grid since only 255 elements are
> allowed in the SQL expression. Is there a way around this? Is there
> another way to do it? Any help will be sincerely appreciated since I'm
> presently in one of those programmer's blind alleys. Thanks.

Maybe you're confusing some things:
Query result is limited to 255 columns and a line in SQL statement is
limited to 255 characters and the whole SQL statement is limited to 32k,
but this has nothing to do with grid. If the fields from query don't fit
onto screen, you can shrink them or scroll the grid left/right to see other
columns.
What exactly is your problem?
--
-------------------------
Regards,
Robert

Remove both qwe when replying.



Sat, 31 Mar 2001 03:00:00 GMT  
 Long queries

[...]

Quote:
>Since we're on this topic, Don't you also have to be concerned with the
>blank spaces you
>you add for a SQL statement so that once, concatenated the SQL compiler
>won't choke?

>Consider the code above with spaces added:
>   with Query1 do begin
>     Close;
>     SQL.Clear;
>     SQL.Add('SELECT *');
>     SQL.Add(' FROM "Customer.db"');
>     SQL.Add(' ORDER BY Company');
>     Open;
>   end;

To see the final SQL statement, the statement actually passed to the
database back-end, inspect the value in the TQuery.Text property. You will
find that there is no white space past the right end of a given string list
element's contents, though white space to the left (my formatting) is
retained. So the total length of the above SQL statement is 50 (42 letters,
2 hard spaces, and 6 character positions for line-feeds and
carriage-returns).

  with Query1 do begin
    SQL.Clear;
    SQL.Add('SELECT *');
    SQL.Add(' FROM "Customer.db"');
    SQL.Add(' ORDER BY Company');
    ShowMessage(Text);
    ShowMessage(IntToStr(Length(Text)));
  end;

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)



Sat, 31 Mar 2001 03:00:00 GMT  
 Long queries

Quote:
> Query result is limited to 255 columns and a line in SQL statement is
> limited to 255 characters and the whole SQL statement is limited to 32k...
> What exactly is your problem?

 Thanks Robert. Steve K's reply sorted things out for me. I thought that the
255 'element' limit was a SQL limitation instead of coming from TString, plus
I didn't know how to format the SQL block properly. This is normal, I suppose,
for someone who wasn't exactly sure what SQL was a week ago :) Everything is
working fine now.
________________
Brad Blanchard


Sat, 31 Mar 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Long queries and progress or SOMETHING...Geezzzzz...Can SOMEONE help me out here

2. Help w/ long query

3. Progress of a long query

4. ABORT A LONG QUERY BEFORE IT FINISH

5. ABORT A LONG QUERY BEFORE IT FINISH

6. Help with long strings as query parameters

7. Long SQL queries

8. D1: Query's Expression too long???

9. Long running query in oracle

10. Join Query Record in the same Query...

11. Query from Query

12. Copying a query to another query

 

 
Powered by phpBB® Forum Software