Best Practices related to client/server queries


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.?



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ;-)
barnarpAuthor Commented:
Thanks for the comments.

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

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
Wim ten BrinkSelf-employed developerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barnarpAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.