TClientDataSet->ApplyUpdates not deleting records
Scott
Not sure where to ask this. I am using C++ Builder 5. This is a
desktop application that is using TClientDataSets for in memory tables.
The database is Paradox. TClienDataSet is connected to query via TProvider.
1) I save a record to the database using ApplyUpdates
2) I reload the record by calling TClientDataSet->Close() and then
TClienDataSet->Open()
3) I then delete the record with TClientDataSet->Delete()
4) Then add a new record with TClientDataSet->InsertRecord(...)
5) Save to the database using TClientDataSet->ApplyUpdates(-1)
When looking at the database I see that both the deleted record and the
newly inserted record exist. Any ideas?
Thanks,
*:> Scott
Dave Rowntree
answer in this thread for reasons why (watch out for url wrap):
http://groups.google.com/groups?hl=en&ie=ISO-8859-1&oe=ISO-8859-1&threadm=3c
6d4910_2%40dnews&rnum=6&prev=/groups%3Fas_q%3DApplyUpdates(-1)%26as_uauthors
%3DDave%2520Rowntree%26hl%3Den
Create an OnReconcileError event on your ClientDataSet and display the
E.Message error passed in to the event.
For some reason your delete is being rejected. The E.Message should tell you
why. The insert is being executed despite the delete being rejected because
you are using ApplyUpdates(-1) instead of ApplyUpdates(0).
--
Dave Rowntree
"Scott" <NOSPAMs...@Telynx.com> wrote in message
news:3D1B14BF...@Telynx.com...
Scott
I was slightly mistaken about my steps to get the problem. Here are the
actual steps.
1) I save a record to the database using ApplyUpdates
3) I then delete the record with TClientDataSet->Delete()
4) Then add a new record with TClientDataSet->InsertRecord(...)
5) Save to the database using TClientDataSet->ApplyUpdates(-1)
When I add a step 2
2) I reload the record by calling TClientDataSet->Close() and then TClienDataSet->Open()
everything works fine.
Why do I need to 'refresh' the TClientDataSet after I call ApplyUpdates?
Thanks,
*:> Scott
Dave Rowntree
You don't normally.
Perhaps you are using a db trigger to alter a column value in the record.
Perhaps there is a problem with a DateTime value.
Have a look in Google http://www.google.com/advanced_group_search?hl=en
for 'Record changed by another user'. There is a lot of information there on
this subject. If you can't find an answer post back here.
--
Dave Rowntree
"Scott" <NOSPAMs...@Telynx.com> wrote in message
news:3D1B27E3...@Telynx.com...
> Thanks, the error I am getting is 'Record Changed by another user'.
>
> I was slightly mistaken about my steps to get the problem. Here are the
> actual steps.
>
> 1) I save a record to the database using ApplyUpdates
> 3) I then delete the record with TClientDataSet->Delete()
> 4) Then add a new record with TClientDataSet->InsertRecord(...)
> 5) Save to the database using TClientDataSet->ApplyUpdates(-1)
>
> When I add a step 2
> 2) I reload the record by calling TClientDataSet->Close() and then
TClienDataSet->Open()
> everything works fine.
>
> Thanks,
> *:> Scott
>
>
Scott
TDataSetProvider->UpdateMode from upWhereAll to upWhereChanged and that
fixed the problem.
I don't understand why that made a difference, the documentation
explains what the different options are but not why you would need to or
want to use anything other than default.
Thanks,
*:> Scott
Ross Davis
"Scott" <NOSPAMs...@Telynx.com> wrote in message
The reason that it worked has to do with rounding problems and how the
Tdatetime stuff is stored. If your tables have primary keys, then you
should look at setting to UpWhereKey. Then it will match the records by key
field rathing than trying to compare all fields. This method should also be
faster.
>
> Thanks,
> *:> Scott
>
Dave Rowntree
> should look at setting to UpWhereKey. Then it will match the records by
key
> field rathing than trying to compare all fields. This method should also
be
> faster.
Using UpdateMode upWhereKeyOnly may or may not be suitable for an
application. Choosing which UpdateMode to use should be concidered
carefully. If you use upWhereKeyOnly, updates will *not* be rejected if a
record has been changed by another user. This can have the potential to
invalidate database data.
--
Dave Rowntree
Ross Davis
> application. Choosing which UpdateMode to use should be concidered
> carefully. If you use upWhereKeyOnly, updates will *not* be rejected if a
> record has been changed by another user. This can have the potential to
> invalidate database data.
I didn't know about that problem. That is good know. Is that by design? I
would have expected the updatemode to only decide how to build the where
portion of the update.
Walter Prins
"Ross Davis" <ro...@dataanywhere.net> wrote in message
news:3d1d139a$1_2@dnews...
It is by design. It's not really a problem, as it may be appropriate
depending on requirements, to use WhereKeyOnly. The point is that the
record will be found and "ok'd" for updating based only on the key fields
when using WhereKeyOnly. This means that if any of the other fields was
changed since they were originally fetched by the CDS, that this fact will
be completely overlooked by Datasnap, and so you might accidentally
overwrite a change made by another user. UpWhereAll tries to locate the
record to be updated using each and every field in the record (ie in the
where.) This will obviously fail if the record was changed by another user.
Datasnap will then raise this as an error in the normal way (using a
ReconcileError dialog if supplied.) UpWhereChangeOnly relaxes this a
little, and only checks that the fields that was changed in the CDS is still
unmodified in the original record (but this means that other fields, not
modified by the CDS *may* have been changed by other users.)
Kind regards,
Walter Prins
Natalie Boodram
news:3d1dab6b_1@dnews...
> UpWhereChangeOnly relaxes this a
> little, and only checks that the fields that was changed in the CDS is
still
> unmodified in the original record (but this means that other fields, not
> modified by the CDS *may* have been changed by other users.)
>
Hi Walter,
According to the help for upWhereChanged, "Only key field values and the
original value of fields that have changed are used to find the record."
Does this mean that the "where" part of the update sql will use the primary
key only, or the primary key and all changed fields? We had also thought
that upWhereKeyOnly changed the "where" part of the sql only, still checking
the other fields against the server values to detect if another user made a
change. We figured that upWhereKeyOnly is more efficient in terms of the
database locating the record. The familiar "Record changed by another user"
is not a database error (at least not in our case with Oracle). If the
database is given an update command--
update mytable set field1 = 'newfield1', field2 = 'newfield2'
where pk_field = 1 and field1 = 'oldfield1' and field2 = 'oldfield2'
it will not raise an error but simply not update any records. Does datasnap
then do some checking to see why 0 records were updated, and then figure out
that the record was changed by another user? I guess it just seems as if
changing the "where" part of the update clause is separate from checking if
a record was changed by another user.
Natalie
Walter Prins
> original value of fields that have changed are used to find the record."
> Does this mean that the "where" part of the update sql will use the
primary
> key only, or the primary key and all changed fields? We had also thought
It means, that the "where" part will use the key fields as well as the
original values of the fields that
changed, when generating the SQL update statement responsible for updating
the database.
> that upWhereKeyOnly changed the "where" part of the sql only, still
checking
> the other fields against the server values to detect if another user made
a
> change. We figured that upWhereKeyOnly is more efficient in terms of the
Umm, upWhereKeyOnly is, as you say, (obviously) more efficient for locating
the record for update, but in order to be sure that the record hasn't
changed, you still have to compare every field. Which is why upWhereAll is
safer and upWhereKey not always appropriate. During the resolve/update
process, the provider (normally) calls on a SQLResolver, which in turn calls
PSExecuteStatement on the TDataset associated to the Provider, in order to
effect an update to the backend database. See e.g. Provider.pas line 3607.
This statement executed is a single Update statement, and the
PSExecuteStatement returns the number of rows affected by it. Then, based
on the number of rows affected, Datasnap deduces what happened. If
upWhereAll, and zero rows affected, for example, implies that the original
record was changed by another user. I guess Datasnap then does a seperarate
select to get the conflicting values. Etc etc for the other cases. The
same holds for the upWhereChanged and upWhereKey. In addition, by using the
ProviderFlags for each field in the orignal TDataset tied to the provider,
you can make exceptions to the above processing model, eg. if you exclude
the "pfInWhere" flag from a particular field's Provider flags, that field
will never be included in the Where clause, even when using UpdateMode
upWhereAll. Similarly, you can keep a field from ever being updated, by
removing the pfInUpdate flag from it. So, that anyway is my understanding
of it all... (I'm sure if this is wrong somewhere someone will be kind
enough to correct me.)
> database locating the record. The familiar "Record changed by another
user"
> is not a database error (at least not in our case with Oracle). If the
> database is given an update command--
> update mytable set field1 = 'newfield1', field2 = 'newfield2'
> where pk_field = 1 and field1 = 'oldfield1' and field2 = 'oldfield2'
> it will not raise an error but simply not update any records. Does
datasnap
> then do some checking to see why 0 records were updated, and then figure
out
> that the record was changed by another user? I guess it just seems as if
Yes, this is correct AFAIK.
> changing the "where" part of the update clause is separate from checking
if
> a record was changed by another user.
Well, I guess Datasnap tries to get 2 birds with one stone: deduce if you
need to investigate what happened only if you don't update any records or
update more than 1 -- otherwise if you updated one record then you assume
everything is peachy.
HTH
Walter Prins
Dave Rowntree
locating
> the record for update, but in order to be sure that the record hasn't
> changed, you still have to compare every field. Which is why upWhereAll
is
> safer and upWhereKey not always appropriate. During the resolve/update
> process, the provider (normally) calls on a SQLResolver, which in turn
calls
> PSExecuteStatement on the TDataset associated to the Provider, in order to
> effect an update to the backend database. See e.g. Provider.pas line
3607.
> This statement executed is a single Update statement, and the
> PSExecuteStatement returns the number of rows affected by it. Then, based
> on the number of rows affected, Datasnap deduces what happened. If
> upWhereAll, and zero rows affected, for example, implies that the original
> record was changed by another user. I guess Datasnap then does a
seperarate
> select to get the conflicting values.
Yes. It constructs another SQL select statement on all the fields that were
in the update SQL Where clause. It uses upWhereKeyOnly for this select
statement, using the DeltaDS field ProviderFlags to determine which the key
fields are. That is why, if no pfInKey ProviderFlags are set in the DeltaDS,
a second error message is generated 'Unable to find record. No key
specified'.
Having obtained the current values of all the fields that were in the update
SQL Where clause, MIDAS then checks to see which of these values is
different to the DeltaDS.OldValue's (i.e. identifies which fields caused the
update conflict). It sends the new current value(s) back to the CDS
OnReconcileError event in the Field.CurValue of the passed in DataSet, for
the conflicting fields. You can therefore determine which fields caused the
update conflict by examining the DataSet.Field.CurValue in the
CDS.OnReconcileError event.
> In addition, by using the
> ProviderFlags for each field in the orignal TDataset tied to the provider,
> you can make exceptions to the above processing model, eg. if you exclude
> the "pfInWhere" flag from a particular field's Provider flags, that field
> will never be included in the Where clause, even when using UpdateMode
> upWhereAll. Similarly, you can keep a field from ever being updated, by
> removing the pfInUpdate flag from it. So, that anyway is my understanding
> of it all... (I'm sure if this is wrong somewhere someone will be kind
> enough to correct me.)
I would just add the following:
You can override/alter the ProviderFlags inside a CDS.ApplyUpdates call at
the DSP. The ProviderFlags are available inside the DSP events:
- OnUpdateData - set them against the passed in DataSet fields.
- BeforeUpdateRecord - set them against the passed in DeltaDS fields.
If no pfInKey ProviderFlags were set on the DSP.DataSet fields at the time
the CDS was opened, there will be no pfInKey ProviderFlags set in the
DeltaDS so MIDAS would not know which the key fields were. MIDAS looks for
this problem when it receives the DeltaDS. If there are no key fields
identified in the DeltaDS, MIDAS then has a look at the fields on the
DSP.DataSet to see if there are any set there. If there are, it sets the
pfInKey flags against the corresponding fields in the DeltaDS. So you have
an opportunity in the DSP.BeforeApplyUpdates event to set/alter the pfInKey
ProviderFlags on the DSP.DataSet.
--
Dave Rowntree