Board index » delphi » Multiple SQL statements and TQuery

Multiple SQL statements and TQuery

G'day.

Am I right in believing that each SQL statement must have it's own
TQuery? I want to carry out a series of manipulations on some tables
(some selects, some joins then some updates). Individually, the
operations are simple, but since each is stored in its own individual
TQuery, the number of TQuery's on the form is threatening to rival
Tiger Wood's score on a bad day. What's the best way to handle this
problem?

Another related problem is that of accessing the results of one TQuery
in another. More specifcally, if TQuery1 carries out a select and I
wish to use the results of that select in TQuery2, can I do so
directly or do I have to TBatchmove the results of TQuery1 into a
table? I've used the TBatchmove solution but that means that the
number of TBatchmoves is also fairly large.

I'd appreciate getting suggestions by email. My server's really weird
at the moment. In fact, you may see a different version of this
message. I tried posting it last night apparently without success. If
so, my apologies.

Thank you.

Joseph

 

Re:Multiple SQL statements and TQuery


On Fri, 11 Dec 1998 11:54:20 GMT, mcdonn...@econ.bmg.eur.nl (Joseph

Quote
McDonnell) wrote:
>Am I right in believing that each SQL statement must have it's own
>TQuery? I want to carry out a series of manipulations on some tables
>(some selects, some joins then some updates). Individually, the
>operations are simple, but since each is stored in its own individual
>TQuery, the number of TQuery's on the form is threatening to rival
>Tiger Wood's score on a bad day. What's the best way to handle this
>problem?

This can vary some, no straightforward answer that covers all situations.

The TQuery component can handle only one result set. So the TQuery can
issue only one SELECT statement at any given time.

Some database types allow you to issue multiple SQL statements at one time.
This can be done with the TQuery if: the SQL statement is an action
statement (INSERT, DELETE, CREATE TABLE, etc.), the SQL is pass-through,
and the particular database back-end used supports multiple statements like
this.

Do you have to have one TQuery component for every SQL statement an
application might use? No, you do not *have* to. It would not be especially
efficient, but you *could* do it that way. But more often, a single TQuery
is used to issue more than one statement in the course of running an
application. The TQuery.SQL property, which contains the SQL statement, is
a string list object. It is a very simple matter to populate this property
with one statement, execute it, and then populate it with a new statement.
For example:

  with Query1 do begin
    Close;
    with SQL do begin
      Add('INSERT INTO Customer');
      Add('(CustNo, Company)');
      Add('VALUES (123, "Hot Chicken Wings To Go!")');
    end;
    ExecSQL;
    with SQL do begin
      Add('INSERT INTO Orders');
      Add('(CustNo, OrderNo, Product)');
      Add('VALUES (123, 1004, "Chicken, bucket, 1 gallon")');
    end;
    ExecSQL;
  end;

One TQuery executing two statements (in sequence).

Quote
>Another related problem is that of accessing the results of one TQuery
>in another. More specifcally, if TQuery1 carries out a select and I
>wish to use the results of that select in TQuery2, can I do so
>directly or do I have to TBatchmove the results of TQuery1 into a
>table? I've used the TBatchmove solution but that means that the
>number of TBatchmoves is also fairly large.

No. The result set retrieved by a TQuery as a result of a SELECT query is
dynamic. It cannot subsequently be accessed in the SELECT statement of
another TQuery.

You would need to write the result set of the first query out to a
temporary table and then access that table from the second query.

Alternately, compose the second query so that it does what the first query
does, but more. This might include situations where the second TQuery
refines the query of the first.

//////////////////////////////////////////////////////////////////////////
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)

Re:Multiple SQL statements and TQuery


I can answer your first question:

You can do one of many things:
A. Store each query in a text file and load each file in the TQuery.strings
property using code just before execution.
B. Store each query in a string constant and again, loading each.
C. Create a table with memo fields, giving each query a unique id then
looking up each id when the query is needed and copy the memo field contents
to the TQuery.Strings property then execute.

I use option C. It allows the query to be modified by editing the table not
the code.

, Aaron

Quote
Joseph McDonnell wrote in message <3671041f.12321...@nntp.eur.nl>...
>G'day.

>Am I right in believing that each SQL statement must have it's own
>TQuery? I want to carry out a series of manipulations on some tables
>(some selects, some joins then some updates). Individually, the
>operations are simple, but since each is stored in its own individual
>TQuery, the number of TQuery's on the form is threatening to rival
>Tiger Wood's score on a bad day. What's the best way to handle this
>problem?

Other Threads