Embarcadero Discussion Forums: TClientDataSet.Apply ...

Welcome, Guest: Login; Guest Settings

Thread: TClientDataSet.ApplyUpdates fails

Reply to this Thread

Search Forum

Back to Thread List

	Replies: 5 - Last Post: Mar 31, 2017 9:32 AM Last Post By: Lajos Juhasz

David Smith

Posts: 7
Registered: 6/15/14

TClientDataSet.ApplyUpdates fails

Posted: Mar 31, 2017 2:58 AM

Hi. I'm definitely new to using a TClientDataSet but can appreciate a lot of its promised advantages. However, I seem to be having issues with the ApplyUpdates function not succeeding at all when there have been underlying changes to the database records by another party. So I'm trying to write robust code that can cope with a multi-user environment where multiple staff can potentially be editing the same record at once.

In a test environment I have:

Connectivity is TFDConnection (pointing to MS SQL db on server) --> TFDQuery (simple SELECT * FROM tbl (1 key field, 2 others)) --> TDataSetProvider --> TClientDataSet --> TDataSource - TDBGrid.

The TDataSource is set to auto edit, in the on datasource change I'm 'capturing' the details of the TClientDataSet's Changecount (and displaying it). When I click my 'save' button (not Post, that's already been done) and it fires off a routine from Cary Jensen showing the delta changes before using the TClientDataSet's ApplyUpdates call with parameter 0. (I'm doing this so I can see when update errors occur)

In the TClientDataSet's ReconcileError event I'm passing the parameters on to the HandleReconcileError routine borrowed from the RecError unit.

In the grid I'm making simple changes to the 3 records in the database table (3 fields, autoinc key field, name and dob). In SQL's Management Studio I have some code that also changes the records.

When I hit my save button (basically try and applyupdates) I can see the deltas, I then see that applyupdates doesn't come up with any errors and when you call the TClientDataSet's refresh method back come exactly the records as you have just changes - irrespective of any changes made from within Management Studio. So it's doing a 'last user wins' record updating.

If I delete one of the records using Management Studio then when I try and save changes I do indeed see the HandleReconcileError come into play, stating that the record may have been edited or deleted by another user. However I cannot seem to get this to work for anything other than deletes.

I have my TDatasetProvider, TFDQuery and TFDConnection's UpdateMode all set to the same value of 'upWhereAll' - which from what I have read should be correct.

So please does anyone have any clue as to why I cannot get this working so that the application caters for database changes being made by many other users at the same time?

Hope this isn't too complex sounding or badly written.
Many thanks in advance

Bob Swart

Posts: 22
Registered: 9/12/06

Re: TClientDataSet.ApplyUpdates fails

Posted: Mar 31, 2017 3:50 AM

in response to: David Smith

Hi David,

Connectivity is TFDConnection (pointing to MS SQL db on server) --> TFDQuery (simple SELECT * FROM tbl (1 key field, 2 others)) --> TDataSetProvider --> TClientDataSet --> TDataSource - TDBGrid.

Start the fields editor on TFDQuery, add all fields, and check the
ProviderFlags for the key field. Does if have the pfInKey flag set to
True? Probably not, so set it to True here.

You may also want to do this in the TClientDataSet (so the grid also
knows the primary key field).

When I hit my save button (basically try and applyupdates) I can see the deltas, I then see that applyupdates doesn't come up with any errors and when you call the TClientDataSet's refresh method back come exactly the records as you have just changes - irrespective of any changes made from within Management Studio. So it's doing a 'last user wins' record updating.

That's strange, since the upWhereAll should ensure that all fields are
included in the where clause. This sounds like the UpdateMode is set to
upWhereKeyOnly, so the last user wins (based on the primary key).

So please does anyone have any clue as to why I cannot get this working so that the application caters for database changes being made by many other users at the same time?

I always use the upWhereChanged value, so two or more users can modify
the same record (but different fields) at the same time. Works perfectly
fine.

Groetjes,
Bob Swart

--
Bob Swart Training & Consultancy (eBob42.com) Forever Loyal to Delphi
Embarcadero Technology Partner -- Embarcadero MVP -- Spirit of Delphi
http://drbob42.com/courseware - Facebook: http://facebook.com/drbob42
Delphi Reseller: www.bobswart.nl (Dutch)- www.bobswart.com (Eurozone)
http://twitter.com/eBob42 LinkedIn: http://nl.linkedin.com/in/drbob42
See also Dr.Bob's Delphi Clinic YouTube Channel http://bit.ly/drbob42
Chairman Delphi Development Network (DDN) powered by SDN - www.sdn.nl

David Smith

Posts: 7
Registered: 6/15/14

Re: TClientDataSet.ApplyUpdates fails

Posted: Mar 31, 2017 5:44 AM

in response to: Bob Swart

Bob, many thanks for replying and so quickly - I have read so many of your articles over the years since Delphi 1 !

Whatever element of the changes you suggested actually did it I'm not sure but I will have to go back and implement each change separately to see. However, I changed the UpDateMode on all 3 elements to UpWhereChanged and added the fields directly to the TFDQuery and ensured that the key field in there had the provider flag set as you recommended (I'd already done the CDS one) and hey presto, it worked!

So many, many thanks - I can now get on with the real application. I don't know whether this happens with other developers but I cannot get on with anything else sometimes when I hit a roadblock like I had and have to try and solve it.

So once again, thanks and kind regards

David

Bob Swart wrote:
Hi David,

Connectivity is TFDConnection (pointing to MS SQL db on server) --> TFDQuery (simple SELECT * FROM tbl (1 key field, 2 others)) --> TDataSetProvider --> TClientDataSet --> TDataSource - TDBGrid.

So please does anyone have any clue as to why I cannot get this working so that the application caters for database changes being made by many other users at the same time?

Lajos Juhasz

Posts: 801
Registered: 3/14/14

Re: TClientDataSet.ApplyUpdates fails

Posted: Mar 31, 2017 6:02 AM

in response to: David Smith

A bit unrelated question, why are you using TFDQuery + Clientdataset?

Almost everything you can do with CDS now you can do with TFDQuery, you
are just using more memory.

David Smith

Posts: 7
Registered: 6/15/14

Re: TClientDataSet.ApplyUpdates fails

Posted: Mar 31, 2017 6:11 AM

in response to: Lajos Juhasz

Lajos Juhasz wrote:
A bit unrelated question, why are you using TFDQuery + Clientdataset?

Almost everything you can do with CDS now you can do with TFDQuery, you
are just using more memory.

Lajos, good afternoon. The simple answer is I don't know! I had always used the TADOQuery (etc) components having only ever written LAN type querying but when I got Delphi Berlin I wanted to write applications that in theory could save data locally, and then when re-connected to the network, could upload changes back again. I bought Cary Jensen's 'Delphi in Depth: ClientDataSets 2nd Edition' book and have been following that for guidance.

Is TFDQuery a memory based 'table' in its own right? Are you aware of any differences between the two (CDS and TFDQuery)? A lot of examples on the WEB do use the combination I have - so I was just copying them in an attempt to get it all to work.

Thanks for your comments and can anyone shed any more light on this please? I'd like to start off using ClientDataSet like work by using the latest components if I have them rather than using old ideas and old components?

many thanks again.

David

Lajos Juhasz

Posts: 801
Registered: 3/14/14

Re: TClientDataSet.ApplyUpdates fails

Posted: Mar 31, 2017 9:32 AM

in response to: David Smith

David Smith wrote:

Lajos Juhasz wrote:
A bit unrelated question, why are you using TFDQuery +
Clientdataset?

Almost everything you can do with CDS now you can do with TFDQuery,
you are just using more memory.

Lajos, good afternoon. The simple answer is I don't know! I had
always used the TADOQuery (etc) components having only ever written
LAN type querying but when I got Delphi Berlin I wanted to write
applications that in theory could save data locally, and then when
re-connected to the network, could upload changes back again. I
bought Cary Jensen's 'Delphi in Depth: ClientDataSets 2nd Edition'
book and have been following that for guidance.

Is TFDQuery a memory based 'table' in its own right? Are you aware of
any differences between the two (CDS and TFDQuery)? A lot of examples
on the WEB do use the combination I have - so I was just copying them
in an attempt to get it all to work.

Thanks for your comments and can anyone shed any more light on this
please? I'd like to start off using ClientDataSet like work by using
the latest components if I have them rather than using old ideas and
old components?

I never tried the briefcase model. In theory FDQuery can also save its
content to a file, but never tried how difficult is to load and apply
back to the server.

TFDQuery stored the data in a memory based table (TFDDatSTable). I
never used advanced options of the clientdataset so cannot comment how
different it is. I am sure you can find things that are implemented
better in CDS (for example nested datasets that I've never used).

While there are more examples for the CDS it's "almost" deprecated
(sometimes there are bug fixes for it, but that's a rare case. There
are reports that CDS has access violation when compiled for a 64 bit
targert in Berlin, I have no information whenever those bugs are
resolved in Tokyo or not) on the other hand there is less example for
TFDQuery but it's part of the FireDAC that's actively developed.

Back to Thread List

Legend
	Helpful Answer (5 pts)
	Correct Answer (10 pts)

Developer Tools

Database Tools