DELETE

Contents


Delete is used to delete one or several recordings from a table.

Syntaxes

   SYNTAXE 1   Delete [ class ] [ key ] [ read_mode [ key_value ] ]
   SYNTAXE 2   Delete [ class ] [ Where_cl ]

Parameters

Element

Description

Restrictions

  Key :

Description of a file key in the form: variable_name
variable_name '(' exp_ind ')'

None.

exp_ind

Expression entière du nombre de parties de clés utilisées. Par défaut, toute la clé est utilisée.

0<= exp_ind <=number of key components (at most 8).

read_mode

One of the following key-words or one of the following operators:

keyword

operator

Read mode used

Curr


>=
>
=
<
<=

Current recording
Key >= key_value
Key > key_value
Key = key_value
Key < key_value
Key <= key_value

key_value

List of expressions separated by
';' corresponding to the values of the
sub-keys.

value comptatible with the type of the
sub-key

where_cl

See Where instruction

None

Examples

# Read of a recording and deletion
Read [FICH]CLE = 3.14: If fstat = 0 : Delete :     Endif
# Deletion of all the recordings whose invoice number is "00001001"
# the invoice number is the 1st element of the CLEFAC key
Delete [LIG] Where [LIG]NUMFAC = "00001001"
# equivalent syntax
Delete [LIG]CLEFAC(1) = "00001001"
# another syntax yielding the same result but less efficient
For [LIG]CLEFAC(1) Where [LIG]NUMFAC = "00001001" : Delete curr: Next
 
# Deletion of all the recordings among which an alphanumerical field
#  is higher than or equal to "" (all the recordings!)
Lock [BOUM]
If [S]fstat
Delete [BOUM] Where ALPHA >= ""
Unlock
  Endif

Description and comments

Delete is used to delete information from a table according to various modes listed below. Three cases can occur:

If no key value is specified for the modes that require one ( '<', '>', '<=', '>=', '=' ), the current value of the variables of class [F] corresponding to the file is used.

The abbreviation of the file or the key name can be left out of the syntax of Delete. In the first case, the file used is the first in the default table list. In the second case, the key used is the current key (for syntax 1), in other words:

For syntax 2, the database identifies itself the key to be used depending on the Where clause.

The keys that can be used in Delete are:

Delete does not modify the current recording (the content of the variables of the corresponding class [F]).
Delete positions the Adonix system variable adxdlrec that contains the number of erased recordings.
Delete updates the fstat variable in order to mention how the erase operations went.

fstat

Code

Del.

Explanation

0

 

Yes

The erase operations were carried out successfully. adxdlrec recordings were erased. 
or no recording has been selected.

1

ERLOCK

No

inter-locking on a recording.

2

ERECIND

Yes

In read mode, <= or >= means that the read duly took place but the key found is < or > to the searched value.

Caution!
for syntax 1: The deletion stops whenever a recording has been locked.
for syntax 2: The deletion remains locked from the moment that a recording is already locked.

Note

This instruction must be used within a transaction.

The deleted recordings are still visible to the other users while waiting for the transaction to end.

While waiting for the transaction to end, the deleted recordings are still visible to the other users, with Oracle, whereas they will not be with Sql-server. In effect, Adonix uses the "dirty read"(or read uncommitted) technique in Sql-server, in other words, the entries not committed yet are viewed by the users. 
As a matter of fact, SQL-server provides the possibility to perform "committed" reads, but ib that case, the read stops on the modified recordings (unlike Oracle that is going to read in the rollbacks-segments) thus risking slow-dows, especially on the left lists. 

Attempt at deleting locked recordings:

In both cases, the Delete remains pending and proceeds wiith the erase operation when the recording become free again. On the other hand, in inter-locking mode, the  Delete stops the deletion and sends a fstat=1.

The recordings to be deleted are first locked. The locks remain in position until the end of the transaction. For all databases, the limitation of this resource is one of the parameters of the database. Should this resource be depleted, it will no longer be possible to delete another recording and an Adonix error n 43 (FISLOCK) will be generated. Either the number of locks will need to be increased or the program will have to be adapted, or this error will have to be managed.

It is not possible to use a link abbreviation to delete a recording from a table.

Associated errors

Error

Description

MODIN (22)

Read mode incorrect.

ERINDI ( 8)

The number of given values or the index mentioned, exceeds the number of elements of the key.

PACLE (21)

The key does not exist for this file.

ERCLAS (7)

The file class does not exist (file not open).

ERACCE (27)

File open in "read only" mode in the table dictionary.

FISLOCK(43)

No more locks available.

Associated keywords

FILE - WHERE - FOR - FSTAT - ADXDLREC


CONTENTS

Copyright © Sage 1999 - 2007