UPDATE

Contents


Update is used to rewrite records in a table with the values contained in the parameters.

 

Syntaxes

   Update [ class ] [ Where_exp ]   With  field = exp_chp [ , field = exp_chp ]

 

Parameters

Element

Description

Restrictions

class

Abbreviation of an open file

None

Where_exp

Selection_expression See Where

None

field

field name of the table

None

exp_chp

expression giving the value to be taken by the field

compatible with that of the field - see Where -

 

Examples

   # Update the FLGACTIF field in the CLIENT file for all
   # records whose DEBIT and CREDIT zones are 0.
   
CLIENT File [CLI]
    Update [CLI] Where DEBIT = 0 and CREDIT = 0 and FLGACTIF <> 1
    & With FLGACTIF = 1
    # check that there is no more
    For [CLI]
      N += 1
    Next
    If N : Errbox num$(N)-"records not modified" : Endif

   # Incrementation of number of edits
    Update [DIV] Where FLAGEDIT & CLE >= [M]DEB1 With NBEDIT += 1

 

Description and comments

Update is used to assign particular record fields in a table with values contained in parametres, automatically performing read, lock, rewrite and unlock operations. This instruction simplifies writing of the routine and speeds up operations if several records are involved.

This instruction exploits the possibility of performing the same type of request on Oracle databases. Adonix only translates expressions given in a language the database can understand, limiting the choice of possible functions. The list is given in the documentation for the Where clause.

Update updates the variable fstat to show the progress of the operation.

fstar

Code

Update

Explanation

0

 

Yes

The update took place correctly.

1

ERLOCK

Yes and No

The record was already locked by another user. In this case the update stops.

3

ERDUPCL

No

Attempt to create a key that cannot be copied.

4

ERDEBF

No

Start/End of file (or empty selection)

An expression calculated from the value of a field in the table may be used as a value. This value will be that read in the table by the read operation performed by Update and not the value of class [F] as it was before the instruction.
Example :[F:FIC1]NUMBER = 0
Update [FIC1] With NUMBER = NUMBER +1
The various values of NUMBER will be incremented and not set to 1.

WARNING: there is no way of predicting the behaviour of an Update instruction which would assign a value to a field from a field itself assigned by Update.
Example:
Update [FIC] With FIELD1 = VALUE, FIELD2 = FIELDA.

The update stops as soon as a record cannot be locked or updated. Therefore the update may only be partial.

Update does not modify class [F] variables corresponding to the file.

 

Comment

A link abbreviation may not be used to update a file.

The update by Update must be take place within a transaction.

During the transaction, modifications will not be visible to other users.

Each "successful" record lock involves allocation of a lock, but the lock is only released at the end of the transaction. Any database uses its own locks, and the number of these is limited by one of the database parameters. If this resource is exhausted, Adonix generates an error no. 43 (FISLOCK). Either the number of locks must then be increased, or the programme modified, or this error managed.

The lock depends on the database. It is independent of the value of [S]lockwait. In Oracle, Sql-server, Db2 the lock attempt will continue for an unlimited period, and cannot be interrupted. Locking the table as a whole with Lock [abvf] is a rather brutal, but effective way of avoiding any problems.

 

Associated errors

Error

Description

ERMODE (10)

Type of parameters not compatible with the file zones to be modified.

ERCLAS ( 7)

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

 

Associated key words

 WHERE - READLOCK - FILE - REWRITE - TRBEGIN - FSTAT - FILTER


CONTENTS


Copyright © Sage 1999 - 2007