||Re: delete row in dataset
Boris Borzic <bb>
||Sat, 9 Jun 2018 00:56:40 +0200
Andreas Gehrs-Pahl <Andreas@AbsoluteSoftwareLLC.com> wrote in
>>I use SQLDataset as shown :
> According to the documentation on SQLDataSet():
> Manages static client side cursors. When instantiated, this class
> will open the fastest and least expensive (read-only, forward) server
> side cursor, retrieve the entire result set into memory and then
> close the server side cursor in order to reduce server resources.
> Once the result set is retrieved, this class provides very fast;
> forward and backward scrolling through the dataset and allows
> relations to be built between other SQLDataSet cursors that may be
> connected to different data sources.
> This means that you can't delete a record on the server (only in the
> temporary DataSet currently in memory), using this class!
Thank you Andreas, but this is not entirely true. Changes in the
SQLDataSet will be applied to the source database when the :Commit()
method is called. However, your dataset MUST contain either the primary
key or list of fields needed to generate a unique key:
:SetPrimaryKey( xColumn ) -> lSuccess
Specify a column or array of columns to be used as a unique index in
searched updates and deletes.
xColumn can be specified as a character string (column name), numeric
(column position) or array containing column names or column positions.
It is very important that the dataset contain all necessary fields for
generating a unique key so that updates and deletes may be applied to the
The sample provided by Vitorio contains a long list of table columns,
however there is no guarantee that any combination of them will generate
a unique key and he is missing a call to :SetPrimaryKey().
One more observation: In most cases, when using an SQLDataSet, it is
better to sort on the client-side (by calling :Sort()) vs. sorting on the
server-side (by using ORDER BY). The reason is, when your SELECT includes
an ORDER BY, the DBMS will likely create a 'static' cursor by generating
a temp file. This is slower, consumes more resources and is generally not
as efficient as a forward-only cursor.
industrial strength Xbase++ development tools