[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Best Practices related to client/server queries

Posted on 2004-10-28
Medium Priority
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 recordset.next / 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 27

Assisted Solution

kretzschmar earned 200 total points
ID: 12431698
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

ID: 12431818
Thanks for the comments.

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

Assisted Solution

geobul earned 200 total points
ID: 12432584

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

Wim ten Brink earned 200 total points
ID: 12438016
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

ID: 12460772
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.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

656 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