Subject Re: delete row in dataset
From Boris Borzic <bb> <Boris Borzic <bb>>
Date Sat, 9 Jun 2018 00:56:40 +0200
Newsgroups sqlexpress

Andreas Gehrs-Pahl <> 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
desired row.

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.

Best regards,
Boris Borzic
industrial strength Xbase++ development tools

Recent messages in this thread
-# delete row in dataset Victorio <> 08-Jun-2018 03:26 pm
.-# Re: delete row in dataset Andreas Gehrs-Pahl 08-Jun-2018 05:24 pm
..-# Re: delete row in dataset (Current message) Boris Borzic <bb> 08-Jun-2018 06:56 pm
...-# Re: delete row in dataset Victorio <> 09-Jun-2018 08:58 am
...|-# Re: delete row in dataset Boris Borzic <bb> 09-Jun-2018 09:33 am
...|.\# Re: delete row in dataset Victorio <> 09-Jun-2018 10:19 am
...\# Re: delete row in dataset Victorio <> 10-Jun-2018 01:50 pm