Checking uniqueness constraint on server

[Enquiring Mind]

Hi,

Suppose a SQL server table has a unique fields constraint. Since a client
dataset in general only has a partial view of the complete table on the
server, checking the uniqueness constraint locally will not guarantee that
it is satisfied on the server. The definitive uniqueness check can only be
done on the server. Therefore just before applying inserts or updates to
the SQL server the client program must query the server to determine whether
the server unique fields constraint would be satisfied by the field values
to be applied to the server. Presumably such an operation should be done by
the TDatasetProvider during the execution of ApplyUpdates. Does the
DatasetProvider do this automatically if the server constraints have been
imported to the provider/client dataset?

Thanks.

EM

[Craig Stuntz [TeamB]]

Therefore just before applying inserts or updates to the SQL server
the client program must query the server to determine whether the
server unique fields constraint would be satisfied by the field
values to be applied to the server.

Why? Just handle the error when/if it happens, by handling the
CDS.OnReconcileError event. You can never perfectly reproduce the SQL
Server constraint checks, so I wouldn't bother trying.

[Enquiring Mind]

Post by Craig Stuntz [TeamB]
Why? Just handle the error when/if it happens, by handling the
CDS.OnReconcileError event. You can never perfectly reproduce the SQL
Server constraint checks, so I wouldn't bother trying.

Thanks for that. I guess that's the optimistic approach. It seems to me that
the disadvantage of the approach is that the error message returned by the
server may be expressed in terms that are not easily understood by the
target application user. It's difficult for the client app to identify the
nature of the error from the error code and error message of the exception
object, so that it can report the error to the user in a more user-friendly
manner. Presumably each SQL server DB vendor has error codes specific to its
own software.

[Craig Stuntz [TeamB]]

Post by Enquiring Mind
Thanks for that. I guess that's the optimistic approach. It seems to
me that the disadvantage of the approach is that the error message
returned by the server may be expressed in terms that are not easily
understood by the target application user.

True, though you can (and should) fix that with centralized error
handling. We have an error handler function which translates exceptions
into "human-readable" error messages. Generally, this is fired from
Application.OnException, but there are special cases, such as
OnReconcileError, where this is called directly.