Delphi, TQuery and keeping a DBGrid refreshed

Because you are using a Query to get the information from the database, you have to refresh the query to show the updated information.
Would it be any quicker to use a stored procedure to get the data instead, or is it the link that will still cause the "slow down".

If it was only one table you were viewing data on you could have it directly pointing to the table and you would see updates as they happen.
I dont think you will be able to get round this issue because you are viewing more that one tables data.

You could possibly, slightly, cheat he he

If you used a listview instead to show your data, you could "simulate" the insertion/update of a record with the listview, as well as actually updating the database.

Another possibility, and someone else might be able to confirm if it is quicker or not, might be to get your database to pass the dataset back as an XML document (i.e. just passing a large string round your network), and then populate a dbgrid from this.

I wish I could think of all these suggestions in one go ...

Another one might be to create a view that joins the tables together.
Then just use your DBGrid to point directly to the view instead rather than going through the query.

if you wanted to be notified only about the updates/deletes/inserts the solution may not be that simple

you could use a middle tier client applcation, basically you have ONE program doing all the database transactions for all the clients, and at the same time it notifies all the clients of the updates, it can pass the keys of the updated rows and the clients can then request those rows

you would have to use Indy or something like it

Won't a view take care of this for you BlackTigerX?

If you had a view with an SQL query that linked the tables together across the 2 databases, then used a DBGrid and an ADOTable pointing directly at the view, any updates/inserts/deletes would automatically be seen no?

Cheers or your replies so far.

Okay, I'll try to go through your replies and fill in the details.....

Mike:

It's definitely the link that is slowing it down. I would prefer to be running at 100Mbs with a nice shiny new file server. But for reasons that I won't go into here (company finances and other priorities which aren't IT :-( ), that isn't possible at the moment. I have to make do with what I have got.

Yes, I had thought about just combining all the data and start afresh. But, again, that isn't practicable. We have to much ancient software (even some old Pascal/Paradox Engine stuff) that relies on the table being in the structure they are. Basically, I have a new table and the old one. The old one has to stay in the format it is for now whereas the new table is a superset of the old data. Hence I use a join to combine the 2 and make it invisible to the user as to which database the data is being retrieved from.

The list view route is actually worth considering, I will look into that as a possibility.

As for the XML route, we are simply accessing a remote, shared Paradox table. I'm not sure in that respect that there's anyway to return an XML document.

Not sure how I do the view suggestion? I have the following SQL commands in the TQuery that generates one set of data which is routed the the DBGrid via a TDataSource:

SELECT
QuoteNo AS SQLQuoteNo,
CustName AS SQLCustomerName,
CustRefNo AS SQLRefNo,
CustIssNo AS SQLIssueNo,
Category1 AS SQLBoardType,
TTNumber AS SQLTTNumber
FROM Holecnt
UNION
SELECT
QuoteNo AS SQLQuoteNo,
CustomerName AS SQLCustomerName,
CustomerReference AS SQLRefNo,
CustomerIssue AS SQLIssueNo,
BoardType AS SQLBoardType,
TooltrackerNumber AS SQLTTNumber
FROM Holecount2
ORDER BY SQLQuoteNo

Looking at this now I remember I use a UNION not a JOIN, sorry to mislead you although the problem is the same.

BlackTigerX:

Yep, basically all I want to do is when a record is added and the DBGrid is redisplayed (it's used as a record selector on a seperate form), that the new record is part of the DBDrid. At the moment it isn't. The query has to be closed and recactivated before the window is redisplayed.

I take your point about a middle tier application. But this is a self contained application. It's purely within the one piece of software that I need the updates to be reflected. There will only ever be one copy of this software running at any one time. But it needs to reflect any changes made in the data without the overhead of reactivating the query.

Mike/BlackTigerX,

I think I need to digest all this and see which way is best. A little experimentation needed. Don't worry about me keeping everything in line, I have to do that under the constraints within which I work. Everthing always has to be bodged to keep the cost down :-(

Oh, I would kill for an SQL server......

I'll get back to you asap as to which direction I've taken, hopefully tomorrow.

Cheers for the pointers, appreciated.

what database?

kretzschmar,

For historical reasons dating back to when we developed in Borland Pascal, we are stuck with Paradox as our database. We have huge amounts of data that would simply be too much of a problem to convert esp as some of our systems are still based around Borland Pascal although we no longer develop in that fortunately.

Which version of Delphi are you using?

If the version you are using has TUpdateSQL (CachedUpdate), you need to use it along with TQuery.
I see that Delphi 5 has it.

HTH
Ashok

Ashok111:

We're using V7 of Delphi. At the moment, I was doing the SQL update via a TQuery. Do you think I should change this to an TUpdateSQL? Would that solve the problem?

Others:

Been in Management meetings seemingly all day yesterday and today.

Tomorrow I will look at some of your ideas and decide which way to go.

Thanks,

Trevor.

Read this article and try to implement it.

TUpdateSQL is a creature of cached updates and cannot be used outside that process. Additionally, TUpdateSQL is a TQuery generator. You, the programmer, enter SQL statements in one or more of the three SQL properties and then, when you invoke the Apply method, TUpdateSQL creates TQuery components for your SQL, runs those created components and then destroys them.

TUpdateSQL works in cooperation with an active dataset and cannot be used as a separate, stand-alone component. It should be remembered, too, that TUpdateSQL is an SQL tool. It can be used with TTable or TQuery, but is most effective with TQuery.

TUPDATE SQL AND ITS DATASET

The most ordinary way to connect TUpdateSQL to it’s dataset is by use of the TBDEDataset.UpdateObject property. This property can be found in the Object Inspector for the TQuery and TTable components. To make the connection, it is necessary only to pick a name of a TUpdateSQL object from the list in the UpdateObject property.

Delphi enforces this rule about TUpdateSQL: There can be, at any one time, exactly one dataset for a TUpdateSQL component. At design time, for example, if you have two TQuery components and one TUpdateSQL component, you may set only one TQuery.UpdateObject property to the name of that TUpdateSQL component. If you try to set the other TQuery to the same name, Delphi will clear the property in the first TQuery.

TUpdateSQL also has a Dataset property, which identifies the TQuery or TTable used as a source for TUpdateSQL. This property is not published (that is, it does not appear on the Object Inspector) and it is an alternative to using the UpdateObject property.

The TUpdateSQL.Dataset property must be set in program code, since it cannot be set at design time. If you use the UpdateObject property to make the connection, you should not use the TUpdateSQL.Dataset property. Likewise, if you use the TUpdateSQL.Dataset property, you should not use the UpdateObject property.

BUG ALERT: If you decide to use the TUpdateSQL.Dataset property, be sure that any TTable or TQuery UpdateObject property is clear at design time. Delphi will not automatically clear any UpdateObject references and the result is unpredictable and can cause run-time errors

USING TUPDATESQL

TUpdateSQL is not a requirement of cached updates. It is always optional, but it is a convenient way to update data in a cached update dataset. It may be used with any TTable or TQuery in cached updates mode, but is most useful with those components when they are read only or when the RequestLive property is false. TUpdateSQL helps automate the process in such cases and helps you avoid a lot of coding to make updates from a read only dataset.

For the sake of illustration, suppose you are trying to manage a TQuery that returns a dataset which is the result of a join of two tables. In this case, Delphi will almost always force RequestLive to be False and so not allow direct updates to the dataset.

To begin with, let's assume that you are going to update only one of the two tables.

Your basic setup should be:
      TQuery.CachedUpdates := True
      TQuery.RequestLive := {who cares}
      TQuery.UpdateObject := name of TUpdateSQL

And in the TUpdateSQL, you should have the SQL for Modify, Insert, Delete set to what you want. Note that you don't have to put SQL in any of those, but use only one TUpdateSQL for one table.

CachedUpdates should be True before opening the query. It’s best to make this setting at design time, before opening the database.

Believe it or not, you are now ready to run. That is, you have the minimum requirement. To make this work, there are just three steps:

1. Open the query.
2. Make changes to the dataset (Insert, Modify, Delete) in the usual way. You can make changes to many records.
3. Somewhere in your code, in a button click event say, put this code to send the changes to the database.
      MyQuery.ApplyUpdates.

Delphi will handle the updates automatically in this simple case. You don’t have to do anything special with the query, the database or TUpdateSQL.

This should work great if you are in a single-user app and you don't have any special data needs. But, since those things are fairly common, we now go to the next level.

For multi user environments, it's useful to put your changes in a transaction. Here's how (in a button click event, say):

      MyQuery.Database.StartTransaction;
      try
            MyQuery.ApplyUpdates;
            MyQuery.Database.Commit;
            MyQuery.CommitUpdates;
      except
            MyQuery.Database.Rollback;
      end;

That handles the basic housekeeping, but exposes you to error conditions you might have to handle. Also there's the business of handling special data needs, like key values that don't appear in the UI or parsing dates into month, day, and year for the database. For these you need two events OnUpdateRecord and OnUpdateError.

You're not required to use these events. If you don't, Delphi will do its best to automate the whole update process. That's why the first part of this explanation looked to easy. But if you use _either_ of these events, then Delphi gives up on automation and makes you do it all explicitly .Here’s a sample of OnUpdateRecord:

procedure TForm1.MyQueryOnUpdateRecord........
begin
      if UpdateKind = ukInsert then
            begin
            MyQuery.FieldByName('ID').NewValue := something;
            end;
      MyTUpdateObject.Apply(UpdateKind);
      UpdateAction := uaApplied;
end;

In this procedure, the special need is for an ID value whenever there is an insert, but there is no special handling for other kinds of updates.

Note that in setting the ID value, the code used the TField.NewValue property. This property is for cached updates only and appears only in OnUpdateRecord and OnUpdateError. In these two events, the Value property has no meaning. However, you can see the before and after data for any field by looking at TField.OldValue and TField.NewValue respectively.

When you are in OnUpdateRecord and OnUpdateError, you can see only one record at a time. The events fire once for every record that was changed and only for those records.

Note also that you have to apply each record in your code:
      MyTUpdateObject.Apply(UpdateKind);

Delphi knows which update kind goes with each record so you can apply them all in this general way. But you can also do this:
      MyTUpdateObject.Apply(ukInsert);
or this:
      MyTUpdateObject.Apply(ukModify);

If you try to apply to a part of the TUpdateSQL component that doesn't have any SQL in it, Delphi will complain.

Remember that TQuery.UpdateObject property? It's not much good if you have to update more than one table, so when you have to do that, leave the UpdateObject property blank and set the association in the TUpdateSQL.Dataset property. But it's still there, you just have to get to it in your code. Before you open the query, do this for each TUpdateSQL component you need:
      MyUpdateSQL1.Dataset := MyQuery;
      MyUpdateSQL2.Dataset := MyQuery;
      etc.

Having more than one TUpdateSQL still doesn't force you to use OnUpdateRecord. Updates can still be automated, but to do that, your TQuery or TTable must have all the key values needed for a proper update.

SQL AND PARAMETERS IN TUPDATESQL

At design time, TUpdateSQL produces some pretty good guesses at what the SQL should be (double click on the TUpdateSQL component to bring up the SQL property editor and push all it's buttons and you'll see.). Most of the time, however, it's never quite what it should be , but it’s helpful to start with some of the default SQL and then modify it to your own requirements. You have to know about parameters to do this.

Parameters in TUpdateSQL are automatically generated by TUpdateSQL and will be used by TUpdateSQL to create parameterized queries. They are like Params in TQuery, but there are some special uses. In TQuery, a param is a symbol for something you want to fill in later:
      where MyCol = :MyColVal
for example.

TUpdateSQL produces the same parameter syntax. That is, a name preceded by a colon ( : ). But here the name is a column name. Specifically, it is a column name from the TQuery or TTable connected to the TUpdateSQL. By default, TUpdateSQL produces a parameter for every column in every table used as a source for your query plus a parameter for every calculated column in your query.

In your query, if you say:
      Select  ‘now’ as Today, name, address from......
you expect a dataset that has three columns. The first column, called Today, contains today’s date and the other columns contain data from the name and address columns from the underlying tables. TUpdateSQL will recognize these as columns that need parameters, but will also generate parameters for all the other columns in the underlying tables, if there are any.

Also, there are two styles of each parameter that TUpdateSQL produces -one for the TField.NewValue property and one for the TField.OldValue property. For the address column in the above example, the parameters are: :ADDRESS and :OLD_ADDRESS.

These are parameters to be used in the SQL properties of TUpdateSQL and you are free to use or not use them as you like. You are not bound by the default SQL in TUpdateSQL. But you can't create any more parameters(except by changing the SELECT statement) and wherever you do use them, Delphi will substitute the NewValue and OldValue values.

A NOTE ABOUT PARAMETERS AND OLDVALUE/NEWVALUE

There is an interesting ‘gotcha’ in all this. TUpdateSQL is smarter than the dataset it is connected with. TUpdateSQL knows about all the columns in all the tables that support the dataset and that can cause a problem. For example, if you say:
      Select name, address from......
then TUpdateSQL knows about name, address and all the other columns, but the query that uses that SELECT doesn’t. All the query knows is name and address and the only TField references will be for name and address. This will cause a problem if you need to refer to one of those other columns in, say, the OnUpdateRecord event. You cannot say:
      MyQuery.FieldByName(‘zip’).NewValue := something
because there is no field by that name in the SELECT. TUpdateSQL already knows about :ZIP and :OLD_ZIP, but the query doesn’t.

To refer to ‘zip’ in this way, you have to add zip to the SELECT. It’s for the sake of the query, not for the sake of TUpdateSQL.

TIPS AND TRICKS

Remember two things:
Don't use TUpdateSQL.Apply outside of OnUpdateRecord.
Don't use TQuery.ApplyUpdates inside of OnUpdateRecord.

You can put more than one SQL statement in any SQL property in TUpdateSQL. So it’s possible to use TUpdateSQL to update more than one table.

The SQL in TUpdateSQL doesn’t have to have anything whatsoever to do with the associated dataset. The dataset may select values from a table called MailingList and your TUpdateSQL can contain SQL for a table called Orders. You can put ANY valid SQL in TUpdateSQL.

TUpdateSQL adds a level of complexity to your data management. Assume nothing and test thoroughly.

Good Luck.

Ashok

Ashok,

Cheers for the info, I'll experiment with that over the weekend,

Thanks,

Trevor.

Ashok,

Thanks again for that article. I've worked through it and in theory, this is exactly what I want to be able to do. So I set myself up with a small demo program and data.

But I just cannot seem to get it to do what I would expect from the article, which is quite frustrating :-(

The TQuery just doesn't seem to want to provide the update data after using the TUpdateSQL. The TUpdateSQL must have worked because if close and re-open the TQuery, the update has obviously happened as reflected in the data displayed.

But it just doesn't seem to want to happen real-time. I have the DBGrid and the data doesn't change until I close and re-open the TQuery. And I also have a simple locate comman and a message to display the field that should have been updated. But it always shows the pre-update value, again until I close and re-open the TQuery at which point the field value is correct.

I've been back through the article again and again to make sure I have the properties etc all correct and they do appear to be right.

So I don't know why this isn't working as I'd expect.

Trevor.

Trevor,

It has been very long time since I used TUpdateSQL.
Anyway, If you could post the source code for whole (Demo) project here, I can try to fix it.

Also, I will see if I can find one more article on Cached Updates which I have downloaded long time ago.
I am hoping that we will not have to re-open QUERY.

In meantime, if any progress on your side, post here.

Thanks,
Ashok

Ashok,

Please do not spend too much time on this! I wouldn't want to distract you from doing anything important.

This is the form source:

object Form1: TForm1
  Left = 196
  Top = 103
  Width = 870
  Height = 640
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 8
    Top = 64
    Width = 817
    Height = 481
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Button1: TButton
    Left = 96
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 1
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 240
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Button2'
    TabOrder = 2
    OnClick = Button2Click
  end
  object Button3: TButton
    Left = 408
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Button3'
    TabOrder = 3
    OnClick = Button3Click
  end
  object DataSource1: TDataSource
    DataSet = TestSQL
    Left = 8
    Top = 8
  end
  object TestSQL: TQuery
    CachedUpdates = True
    AutoRefresh = True
    DatabaseName = 'testtable'
    RequestLive = True
    SQL.Strings = (
      'select * from testtable'
      'where ID1=1')
    Left = 48
    Top = 8
  end
  object UpdateSQL: TUpdateSQL
    ModifySQL.Strings = (
      'UPDATE TestTable'
      'SET ID3 = "TEST10"'
      'WHERE ID2 = "Test 2"')
    InsertSQL.Strings = (
      'insert into testtable'
      '  (ID1, ID2, ID3)'
      'values'
      '  (:ID1, :ID2, :ID3)')
    DeleteSQL.Strings = (
      'delete from testtable'
      'where'
      '  ID1 = :OLD_ID1 and'
      '  ID2 = :OLD_ID2 and'
      '  ID3 = :OLD_ID3')
    Left = 192
    Top = 16
  end
end


This is the code:

unit sqlegpage;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBTables, DB, StdCtrls, Grids, DBGrids, ExtCtrls;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    TestSQL: TQuery;
    Button1: TButton;
    UpdateSQL: TUpdateSQL;
    Button2: TButton;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  UpdateSQL.ExecSQL(ukModify);
  TestSQL.ApplyUpdates;
  DBGrid1.Refresh
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  TestSQL.Active:=not TestSQL.Active;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  with TestSQL do begin
    Locate('ID2','Test 2',[]);
    ShowMessage(FieldByName('ID3').AsString);
  end{with};
end;

end.

Apologies for style etc, it's a quick demo only!

V2Quote is a BDE alias pointing to our data directory.

Ths table has 3 fields (for no good reason other than I though I might need them at the time). Field ID1 I set to either 1 or 2, I'm using the query just to extract those with an ID of 1. Then some text in ID2 that I use to islolate the record I want to upate. Then field ID 3 that I'm changing. Nothing fancy about the change of field value, I modify the SQL to a different value before each run, just quick and dirty for a lash up.

Button 2 activates and deactivates the Query. Button 1 calls the update, at which point I'd expect the new value in the UpdateSQL modify query to be available, but that is where the problem is. It doesn't reflect in the DBGrid nor in the message which is displayed clicking Button 3. Deactivating and reactivaing the Query with Button 2 does cause the change to be displayed though.

Please let me know if there's anything else you need or any further explanation.

As I've said though, please do not spend to much time over this. I know how valuable everybodies time is these days.

Thanks,

Trevor.

There is one more alternative.

By doing Query1.Close; and Query1.Open; when using TUpdateSQL, it may not be reading from the actual database.
I think actual update to the database will occur after ApplyUpdate.

According to Delphi 7 Help file....

Note:      Instead of using cached updates, applications can obtain the same benefits with greater control by using a client dataset and a provider component.
Note:      When cached updates are enabled, data changes are stored in local memory. The storage medium is a Paradox format in-memory table

Cached updates are most useful to client applications in two-tiered applications. The main benefits of enabling cached updates are:

Fewer transactions and shorter transaction times.
      Minimization of network traffic.

HTH
Ashok

?? sorry to disturb your discussion,
but as long as u use updates independent from your
query you may not get an actual view of the
data without closing/reopening your query

to keep/get the data live in your query you must
do your inserts/updates within your query

for this you have to set on your querey the property cachedUpdates to true
and to link the TUpdateSQL into your query via the property UpdateObject

now you will be able to edit directly in the grid, or any subform,
where the dataaware-controls are linked to your query

additional to store your changes permanent into your database,
you have to supply to events (not in mind correctly the names)
onPost,onDelete (or afterpost, afterdelete)

in the events just write the line (if you don't need an errorhandling)

QueryX.ApplyUpdates;

meikl ;-)

Hi meikl,

Don't worry, you're not interrupting the conversation, please disturn all you want!!!

I see you make the point about keeping the update within the query, and I can see why you might thought I hadn't from the example as I just noticed it's missing. But on the version that I created it was linked via the UpdateObject. I think the reason it wasn't in the code I pasted in was that I tried the alternative way in the article from Ashok where the UpdateSQL dataset property is set instead. So I'd taken the UpdateObject property value out and I'd forgotten to put it back in when I edited the code project to post it up here.

Sorry for any confusion. So basically, yes the UpdateSQL is linked to the query in my demo code but it doesn't reflect the change until a close/open is carried out.

I've tried putting the code you suggested into the events. But investigating with breakpoints shows that these events are not even called. But the update (modify) must be happening as an close/open reflects the change.

I'm sure it cannot just be my PC causing this problem, there is either some very basic, fundamental point that I'm missing here (like it's not possible on such a simple level) or there is just something I do not have set up correctly.

:-(

Thanks,

Trevor.

trevor,

to keep you dbgrid live, you must do your insert/updates/deletes within your query

to do this, handle the query like a ttable, like

query1.append;
query1.fieldbyname('field1').asstring := 'WhatEver';
//otherfields
query1.post;

any other insert/updates/deletes you do outside of your query (even within the linked TUpdateSQL)
may never noticed by your query without close/reopen

meikl ;-)

Hi meikl,

The saying "can't see the wood for the trees" and all that..... I think I've been trying to over complicate this from the start. Using the SQL to bring together the tables I want resulted in my thought patterns thinking that I would have to use SQL to do the inserts, edits, deletes etc.

You're post pointe me in the direction that I don't have to. And, as long as I have the properties set correct for the Query, I can treat it the way I would a simple table.

I've modified my demo with your suggestions and it works exactly along the lines of what I require. I now need to transpose this thinking throughout the much, much larger project.

Thanks!

I will close this off and allocate the points to you. Mikelittlewood, BlackTigerX and Ashok - I really appreciate the time and help you've given me on this topic and without your input the thought processes wouldn't have come around to this way of thinking and so I'll attempt to allocate some extra points for your time and help.

Thanks very much to you all,

Trevor.

well, glad, you got it work :-))

Trevor,

I am glad you got it working.  Thanks for the points.

Ashok