Best Practices related to client/server queries

Posted on 2004-10-28
Last Modified: 2013-11-23

I have been out of touch with Delphi development for a while, and need some pointers using certain delphi controls with data from a database connection.

I am using Delphi 7 with Oracle 806 as backend, and need to develop a client / server application using BDE. I am basically looking for the best way (fastest / easiest) to use certain delphi controls with TDatabase data.

As the application develops, I will probably ask more questions, but at the moment I only have one or two.

I thought to split the points between comments.

1. I have a form which needs to display master/detail data. The form will be split horisontally with one master record details displayed in the top half. The master detail record data will be extracted and populated on creation of the form. The detail records for the master record will then be displayed one by one in the bottom half and can each be viewed by using previous / next buttons.

The question now is whether you open a query, keep it open and use / previous when a button is clicked, or load all the detail records into an array or pointer and close the query, or maybe some better way?

This probably also depends on whether it is a big resultset or not. In this case, it would be under 100 detail records for each master record, but each detail record has around 30 columns/fields, that need to be written to edit/comboboxes each time previous/next is clicked.

2. On a general note. Is it better practise to keep you queries inside Tquery componenst on a datamodule, or write each query in the code ie. Query1.Close; Query.SQL.Clear; Query1.SQL.Add('select * from tab'); Query.Open.?



Question by:barnarp
    LVL 27

    Assisted Solution

    to 1.

    you ca have two dbgrids for displaying.
    you can have two queries, one master one detail.

    if u want to relate master detail automatically
    the detailquery should have a select like
    select * from DetailTable where master_id = :master_id

    master_id should be the real name of the field in the detailtable
    :master_id should be the real name of the field in the mastertable

    in your detailquery adjust the property mastersource to the
    datasource which is connected to your masterquery

    no additional coding required

    >or load all the detail records into an array or pointer
    not recommended only for special issues
    (in this case no dataaware controls can be used,
    and lot coding used for controlling the display)

    >and can each be viewed by using previous / next buttons
    you can use the tdbnavigator for this

    to 2.
    i would prefer a tdatamodule with fixed (at deisgntime adjusted) queries
    (it depends on performance and resource-usage)

    well, this was just in short

    meikl ;-)

    Author Comment

    Thanks for the comments.

    So what you are saying is simpler is better most of the time?
    LVL 17

    Assisted Solution


    If I understand you right it's not exactly what we usually call master-detail form with two dbgrids. You have only one master record that cannot be navigated (i.e. one record query or whatsoever) and a detail query with many records (can be navigated but displays one record at a time). If the above is true then I'd do the following:

    - two queries (one for the maser record and one for the detail set). You may be getting the master data from somewhere else (another query on another form, for instance) and won't need the first query here at all. The second query will look like meikl suggested passing some parameters to its WHERE clause in order to get the detail recordset.
    - one TDBCtrlGrid connected to one TDataSource connected to the second (detail) query above. I personally like DBCtrlGrid very much. Try it and see the difference compared to the standard DBGrid.
    - one TDBNavigator (or several TButtons) for navigational purposes.

    I'd recommend your TDatabase component to be placed either on a DataModule (I prefer that) or on the MainForm and be visible by all other forms in the project. Placing all data components such as queries and tables on one separate data module gives you a centralized access/view of your database activities, allows you to reuse these components everywhere in the project (simply adding the DM unit in the uses clauses) and removes the possibility of circular reference among many units containing different data access components.

    Well, perhaps not the best practice but I prefer to create SQL statements by code (on the fly) and don't like parameters much. Can't say why and what it gives me exactly but this way I don't suffer unexcpected, unusual and unexplainable result sets anymore.

    Regards, Geo
    LVL 17

    Accepted Solution

    Personally, I would forget about the BDE and use ADO instead. Even Oracle has a proper ADO driver and it helps a lot when developing multi-tier applications. (And it performs just as well as the BDE, maybe even better.)

    Now, your Q's...

    1) Master-detail forms are always a real pain and with C/S applications the most important thing to do is to keep the bandwidth usage to a minimum. Meikl gave a good solution here, by using a parameterized query for the detail records. This still keeps things quite easy. However, I would advise that you use the grids only to view the data and create special dislog forms to edit specific master or detail records. One technique I used in the past was just a form displaying the master data in a grid. When a user double-clicked on a row, the user would go to a detail form displaying the selected master record with editable fields and a new grid with detail records for this master record. The user could then double-click on a detail record to edit the detail data, if need be. This works extremely well when you create your application as a MDI application, thus the user can switch between all the forms.

    2) I use datamodules mostly for common tables and queries but in this master-detail situation I might just dump the queries on the forms itself. Especially in the situation with the three forms as I just mentioned, keeping queries on the forms itself keeps things quite simple. When the user opens the master-grid-form then this form only has the query to select the master data. If a master-record is edited in the master-edit-form then this form will link to the query in the other form. It also has a second query to fill the detail grid. And then the detail-edit-form will link to the query on the master-edit-grid and allow the user to edit the details.
    The biggest advantage of this technique is that when the master-edit-form is closed, the detail query will be cleared too. I've used this technique once for an Oracle database that had over 30 tables that needed to be maintained and while you have to keep track how the forms link to each other, once you got some useful system working, it will work quite well.

    Author Comment

    Thanks alot for the help. I also prefer the TDatamodule for all queries, but like working with the TQueries in the code itself. I am yet to figure out the advantage of using parameters to normal variables.

    When using variables you can atleist debug the query by using SQL.Text which I find very helpful.

    Will also check out the TDBCtrlGrid. The application i am working on is actually someone elses code that needs to be fixed which left the company. There are pointers everywhere, pointing to other pointers etc for simple queries and its not very maintainable. I am thinkiong of re-writing most part with simple TQUeries / vaiables and maybe arrays and staying away from the so-called faster pointers. The app is also very slow.

    Alot of TTreeview components are used with each node having its data in a pointer, but that is another story. I hate working with TTreeviews.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
    Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now