Re: [DUG]: Use of TQuery's

[Donovan J. Edye]

M,

What I tend to do is the following:

//Basic query with all the params I might need
sSQL = 'SELECT * FROM People WHERE %Dept% AND %Salary%';

I then do a replace as required for what I am doing:

//Just Deps
sSQL := ReplaceString(sSQL, %Dept%, '23', []);
sSQL := ReplaceString(sSQL, %Salary%, '1=1', []);

//Just Salaries
sSQL := ReplaceString(sSQL, %Dept%, '1=1', []);
sSQL := ReplaceString(sSQL, %Salary%, '10000', []);


//Both
sSQL := ReplaceString(sSQL, %Dept%, '23', []);
sSQL := ReplaceString(sSQL, %Salary%, '10000', []);

And then finally open the query. This does not use TParams as such, but rather fiddles the SQL itself. You may even consider abstracting your data access via a class that does say:

MyClass.Get_People(AQuery : TQuery; ADept : integer; ASalary : integer)
begin
     with AQuery do
     begin
           Close;
           SQL.Text := 'SELECT * FROM People WHERE %Dept% AND %Salary%';
          //Dept
          if ADept <> -1 then
               //Replace params
           //Salary
           Blah
            Open;
     end;
end;

You could even do something along the lines of

MyClass.Get_People(AQuery : TQuery; AFieldList : TStringList; ADept : integer; ASalary : integer)

SQL.Text := 'SELECT %FieldList% FROM People WHERE %Dept% AND %Salary%';

And then replace %FieldList% with the paramater passed in.

Obviously you could implement MyClass as a virtual class and the descend from it for various back ends. We do something along those lines to access a Paradox and MS-SQL database. Implementations within the descendents can then be optomised for each database. So in the SQL DB we may use a stored proc etc.

HTH's

At 15:18 19/06/2001 +1200, you wrote:

Thanks for that James.
I'm not using any dataaware controls so that simplifies things.
Using a Utility Query makes sense.
 
Anyone know if you can set up a query with several parameters but send a wildcard character to any that are not needed?
If so, how?  I guess it depends on the type of the parameter?
 
Mark

----- Original Message -----
From: James Low
To: Multiple recipients of list delphi
Sent: Tuesday, June 19, 2001 2:28 PM
Subject: RE: [DUG]: Use of TQuery's

Well, heres a start from someone not really in the know either:
 
    1) I have a query for each sensible entity (Address, Contact mechanism, Bill, Job). That query has an sqlstring.
    2) Where there is an obvious relationship between entities and if using dataaware controls I'll link child queries to their parent with parametrised queries. Otherwise I might build an sql string to represent the child.
    3) I have one Utility query which I create odds and sodds queries, such as for establishing if records exist/ dont exist etc.
 
Therefore ... a mix, the goal being ease of maintaintenance, clear logic and not dragging too much data onto the client. But, sometimes its easier to build queries in code - and more transparent so I guess its horses for courses.

-----Original Message-----

From: Mark Howard [mailto:[EMAIL PROTECTED]]

Sent: 19 June 2001 13:49

To: Multiple recipients of list delphi

Subject: [DUG]: Use of TQuery's

Hi

 

I'm about to embark on a conversion from Paradox to IB and I have a question about common practice in terms of the use of TQuery's in place of TTables.

 

My current app opens a TTable, for each Paradox table that it uses, at startup and closes them all at closedown.

 

There appear to be 3 general ways that one can use TQuery's:

1.  Just like a TTable, with a Select * from MyTable in the SQL property (at one extreme);

2.  As a purely open Query with nothing in either parameters or SQL, relying on code supplied by the app at runtime.  In this case one TQuery could be used to access several files at different times.

3.  With both SQL and parameters specified at design time, in which case the TQuery is for a predetermined purpose ( and attached to a specific file).

 

In the absence of ANY knowledge about what is normal, I'd be grateful to hear of any general guidelines on what approaches are adopted by those in the know.

 

TIA

 

Mark

-- Donovan
----------------------------------------------------------------------
Donovan J. Edye [www.edye.wattle.id.au]
Namadgi Systems [www.namsys.com.au]
Voice: +61 2 6285-3460
Fax: +61 2 6285-3459
TVisualBasic = Class(None);
Heard just before the 'Big Bang': "...Uh Oh...."
----------------------------------------------------------------------
GXExplorer [http://www.gxexplorer.org] Freeware Windows Explorer
replacement. Also includes freeware delphi windows explorer components.
----------------------------------------------------------------------