Visit our Sponsor   Visit our Sponsor
delphi3000.com - the free delphi knowledge platform
delphi3000.com - the free delphi knowledge platform
499 Users Online NOW
Have a look at your member-status

connecting people's knowledge


  - Recent ArticlesRSS feed for Recent Articles on delphi3000.com
  - List of All Articles
  - Top Viewed Articles
  - Articles (+Attachem.)
  - Articles Of Interest
  - Categories
  - Top Uploader
  - Search
  - Index

  - My Home
  - Submit an Article
  - My Articles
  - My Personal Data
  - My Bookmarks
  - Activities
  - Login/Logout

  - Sign Up
  - Why Sign Up
  - Newsletter

  - Press
  - Advertise

  - Contact
  - Feedback





Community
Borland
ClubeDelphi
Dr. Bob
UK-BUG
Delphi Meetings
Planeta Delphi







Startblatt.de






Share this article with friendsShare this article with friends
Rate this articleRate this article - to keep the quality of delphi3000.com !
Comment this article or read through previous comments (8)


Auxiliary TQuery used with queries built at run time.Format this article printer-friendly!Bookmark function is only available for registered users!
Reducing the amount of code when using auxiliary TQuery objects.
Product:
Delphi 3.x (or higher)
Category:
Database-VCL
Skill Level:
Scoring:
Last Update:
12/06/2001
Search Keys:
delphi delphi3000 article borland vcl code-snippet tquery auxiliar maintenance maintain
Times Scored:
9
Visits:
4520
Uploader: Fernando Martins
Company:
Reference: N/A
 
Question/Problem/Abstract:
Auxiliary queries built at run time make you copy-paste a lot, replicating your code. Why not keep it to a minimum, making it easy to read and mantain ?
Answer:



Do you have an auxiliary TQuery on your form that you use to build dynamic queries, like

'SELECT Count(id) FROM Clients'

and a bit latter you use the same TQuery to

'SELECT Count(Phone_numbers) FROM Clients WHERE area = '1''

and latter on you use it again to

'SELECT Count(area) FROM Contacts'

and so on...

If you have an auxiliar TQuery to run all these queries, you probably have a lot of similar code replicated in your application to load the query string, prepare the query, run the query and finally close the query.
Since replication is not a good thing when it comes to maintenance, why not abstract the queries from the code so that you just have to pass the TQuery object, the query string and, optionally, the database, if you use different databases.

Here's sometinh I've been using for a while that creates that abstraction layer:

Procedure Execute(Q: TQuery; S: String; DBName = '');
Begin
     With Q Do
     Begin
       If DBName <> '' Then
            DatabaseName := DBName;
          Try
             Close;
          Finally
             SQL.Clear;
          End;

          SQL.Add(S);
          Try
             Prepare;
             While Not(Prepared) Do
               ;
             Open;
          Finally
             ;
          End;
     End;
End;

Using this procedure, you can reduce the amount of code and maintenance effort to a minimum, since you can prepare and open the queries just by using

Execute(MyTQuery, 'SELECT Count(id) FROM Clients');

Execute(MyTQuery, 'SELECT Count(Phone_numbers) FROM Clients WHERE area = '1'');

Execute(MyTQuery, 'SELECT Count(area) FROM Contacts', 'Contacts_database');

Execute(MyTQuery, 'SELECT Count(ZIP) FROM Zip_Codes', 'Address_database');

Execute(MyTQuery, 'SELECT Names FROM Vip_Clients', 'clients_database');

After calling the Execute procedure, you are able to read the result from the TQuery as usual.
This procedure simplifies the process of checking if the object is opened, close it if necessary, prepare the query for execution, release the resources to other processes while not ready and finally run the query.

Hope it helps you to.





Please rate this article!
Skill level:
BeginnerExpert

Useful:
No!Very!

Overall rating:
PoorExcellent



Comments to this article
Write a new comment
Take a look at DB
    Luis A. Márquez (Dec 12 2001 5:28PM)

Your idea is nice and simple, but you are missing some features of the database you are using and you aren't unpreparing the query. At least in ORACLE these queries are diferent:

SELECT * FROM ATABLE WHERE AFIELD = 'A'
SELECT * FROM ATABLE WHERE AFIELD = 'B'
SELECT * FROM ATABLE WHERE AFIELD = 'C'
SELECT * FROM ATABLE WHERE AFIELD = 'D'

You could think, "These queries are the same" but for Oracle each query are diferent from others and insteed of having just 1 instance of the query you have 4. This could break down the ORACLE SGA if you have many instances of your queries: You should think in using a parameter

Regards
Respond

RE: Take a look at DB
Fernando Martins (Dec 12 2001 7:25PM)

Once again, the idea is not to give an end solution, but to give an idea of a solution that each person should improve depending on the needs.
The example I've posted here works well with, e. g., Paradox. There is *no possible* comparison between both databases. Since each database engine has it's tricks, this is not a solution for everything.
I think you can get better results if you use stored procedures in Oracle instead of what I show here...
It was just an idea to simplify things, it may, or may not helped you... Hope it did helped, though! :)
Respond

A point to improve
    Yoav Abrahami (Dec 12 2001 2:08PM)

You're idea is nice and simple. I also use something like this (only more sofisticated, I might explain in another article in the future).
However, you should take note that there is a problem in you're code. When you set a query's databasename, the query is looking for the database in the session is it connected to. If you do not specify the session, it uses the default session. If you're database is of another session, the code will not work. you can fix this by passing the session name to the function.
Respond

RE: A point to improve
Fernando Martins (Dec 12 2001 3:27PM)

You are right!
But this was just a simple way to state my point.
You could share your sofisticated implemantation, it would help many of us!
Respond

Nice idea...
    Dave Murray (Dec 11 2001 2:09PM)

Nice idea but you could go further to abstract the queries from your code...

Why not store your queries in an inifile or similar, identified by number. Then you could pass the identifier to your function which then loads the query from file. That way you can change your query without recompiling the code!
Respond

RE: Nice idea...
Fernando Martins (Dec 11 2001 2:52PM)

Well, my intension was not that, I just wanted to show how one can simplify the use of auxiliar queries.

But I liked your idea, though! :)

Respond

RE: RE: Nice idea...
Dave Murray (Dec 21 2001 6:45PM)

I have written an article using this technique and my idea for abstracting the SQL statements into an inifile so they can be changed without recompiling your program. See article #2935 "Abstraction of Runtime Queries from Code".

Hope you like it!

Dave.
Respond

RE: RE: RE: Nice idea...
Fernando Martins (Dec 21 2001 7:40PM)

Hello again.

I liked it! That really creates a good abstraction layer between the query and the object.
My intention in my article was not to achieve this kind of abstraction, but I like your idea!

What I liked the most is the possibility of changing a query without the need of compile the whole application!
You can go even furder and make the where clause dinamic, like poping up an input box for all the dynamic clauses, something like:
[DynamicQueries]
1=Select Name From Country Where Population > :population
After parsing the query, the user should insert the :population value for the query filter! :)

Respond














 
Sign up to consume product discounts for Bronze memberships !

read more


  Visit our Sponsor

 

  Community Ad of
M. Kleiner
 
   














 







     
  Copyright © 2000 - 2007 delphi3000.com - All rights reserved. Terms of use. || Privacy
delphi3000.com is a service by bluestep.com IT-Services GmbH (Vienna)