read
Syntax
READ facsname
READ facsname NEXT
READ facsname UPDATE
READ facsname USING index = value
READ facsname POINTER = value
READ facsname WHERE search_statement
Meaning
The READ operation looks for a record in an earlier opened FACS buffer (replace facsname by the actual name of your FACS buffer) and reads it into memory. After a read operation the error status variable &E contains the status code 0 (when successful) or any other number (if unsuccessful).
If the READ operation is successful (status code = 0) all variables that have been declared for the buffer receive the values from the retrieved record. The location in the database table or in the used index is remembered for a subsequent read operation.
If a READ operation is not successful (status code <> 0) then the variables for that buffer will not be filled.
The FACS buffer must be opened with OPEN before the READ operation can be carried out.
Options
READ without options reads the first record in the database table referred to by the FACS definition into the FACS buffer. READ instructions with options can be combined if necessary.
| • | read…next
The READ facsname NEXT operation uses the current position to find the next record. The first READ NEXT after opening a FACS buffer is equivalent to a normal READ operation. Warning: you may be tempted to use a READ...NEXT in a loop which also, maybe, writes changes to a record (or deletes a record) which has been read, and at the end of the loop reads the next record. However, this construction does not work in Collections. The READ...NEXT can be looped all right, but you cannot write to the same FACS buffer and then continue with the READ...NEXT. The writing or deleting always needs to be done via a different FACS buffer pointing to the same database table. So the solution to this problem is to use a double FACS declaration for the same database table: reading and reading the next record must then be done in FACS declaration 1, while writing or deletion must be done in FACS declaration 2. To get the same record in the FACS 2 buffer as already read with FACS 1, use READ...USING with the current priref from FACS buffer 1. See example 2 below for a clarification. Note that for a specific READ...USING (see further down) you can write any changes in the record via the same FACS buffer without problem. |
| • | read…update
If a record is found, READ facsname UPDATE locks it automatically so that you are the only one who can carry out a DELETE or WRITE operation. Other users can then read, but not lock the record. If no record is found, no lock will be set. Once set, a lock must be removed and this will happen automatically when you execute a DELETE or WRITE, or it will happen by your instruction with an UNLOCK. No one else can edit the record as long as the lock remains. |
| • | read…using
In READ facsname USING variable = search_key the variable should refer to an indexed field in the relevant database table. If the indexed field is declared with an alias (using IS) in the FACS declaration, you must use the alias, otherwise just use the proper field tag included in the FACS declaration. If your search_key is literally a text, put quotes around it. You can use a truncated search (on literal text) by ending the search key string with an asterisk, e.g. 'paint*'. Note that an index for the searched field is not required per se, but it does make READ USING much faster. A READ NEXT USING with the same condition can be used to read the next record listed in the index. From Collections 1.12, just a READ NEXT (without USING) can be used as well: if records from a FACS search in an index, e.g. read COLLECT using COL_objectname = 'chair', are subsequently read by a READ NEXT USING in a loop, e.g. read COLLECT next using COL_objectname = 'chair', then from 1.12 you could abbreviate that next read to just a READ NEXT, e.g. read COLLECT next. The adapl processor will retrieve pointers to the complete result set from the initial READ <FACS name A> USING ... and will remember it for any following READ <FACS name A> NEXT, so the next record will automatically be read by using that list: the index search won't be executed again. Still, the verbose way of formulating the next READ is not slower than the new abbreviated way, because the old way used the initially retrieved pointers list as well. You can still use the old syntax too. So the key value is searched in the specified index. If search_key occurs, the corresponding record is loaded into memory, and the index location is registered. If the NEXT option is used, the next record meeting the requirement will be retrieved. A loop containing READ ... NEXT UPDATE USING index = key and a WRITE or DELETE instruction is dangerous because the keys for the current record in the index table can either be moved or deleted, thus making the pointer to the index table invalid. While in this kind of loop, you should not use the DELETE command on the FACS buffer, and you should not modify the index field on which you are searching. |
READ options may be combined freely.
| • | read…pointer
You can also search for records using the selection in a saved search (aka pointer file) that you've already opened with OPEN. To do this, first open the FACS buffer with the POINTER option (OPEN facsname POINTER = number) and then use the command READ facsname POINTER to read the first record, or READ facsname NEXT POINTER to retrieve the next record referred to in the saved search. |
| • | read...where
Prior to Collections 1.12, in ADAPL, FACS READ statements have always been limited to either subsequently reading each record in the FACS database table, each record in an index in the FACS database table, each record matching a certain index value in the FACS database table or each record in a saved search (aka pointer file) in the FACS database table. This meant that ADAPL had no way to only fetch records that matched multiple conditions. For output formats using adapls this had the consequence that in such cases the user had to perform the proper search in Collections already, possibly apply a specific sorting and mark the desired records, before printing to the output format. In Collections 1.12 and up, this restriction has been removed with the introduction of the WHERE option for a FACS READ. Syntax: READ <FACS name> WHERE '<advanced search statement>' The advanced search statement can be any query you would normally use in Collections, including any sort option if desired. It uses field tags or English field names from the .inf that is referenced by the FACS declaration (the fields do not have to be included in the FACS declaration per se): it does not use FACS field aliases. Keys to search on must be enclosed by double quotes (or double single quotes), while the entire search statement (if coded literally) must be enclosed by single quotes. For maximum flexibility you don't need to enter a fixed query string behind WHERE: you can also put toghether the advanced search statement in a text variable and then use the text variable (without single quotes around it) as the WHERE argument: the search statement stored in such a variable doesn't need to have single quotes around it either. This allows you to include e.g. user input (obtained via a parameters screen for a task or output format) or values from the currently processed record in the advanced search. A sorted query works out the same as in Collections, so note that if the sort field has multiple occurrences, sorting takes place on the value with the highest value (for descending sorting) or lowest value (for ascending sorting). Besides the obvious use to only fetch records that match the result set of an advanced search, you could well use this functionality for statistical purposes, to calculate sums of insurance values or total volumes etc. When you perform the READ WHERE for the first time during an adapl run, only the first record of the resulting set will be available in the FACS buffer, so you can use the relevant FACS aliases from your FACS declaration to obtain values from the fetched record. To fetch the next record from the result set, simply use: READ <FACS name> NEXT (Pointers to the complete result set are kept in memory after the first READ WHERE, so the following READ NEXT automatically uses that list to fetch the next record without having to execute the advanced search again.) Example of an adapl-only output format for any object data source (no record needs to be selected): |
fdstart COLLECT '../data+collect'
%0 is COL_priref
IN is COL_object_number
OB is COL_objectname
VV is COL_creator
fdend
open COLLECT noreset
if (&E <> 0) {
errorm 'Error opening database COLLECT, code = ' + &E
end
}
text searchstringvar[0]
searchstringvar = 'object_name = * and VV = "Mondriaan, Piet" sort OB'
read COLLECT where searchstringvar
while (&E = 0) {
print 5, 70, COL_objectname
output
print 5, 70, COL_creator
output line
read COLLECT next
}
end
Example
* Open all records and save them, so that storage adapls
* and such are executed.
fdstart COLLECT '../data+collect'
%0 is recpriref
fdend
fdstart COLLECT2 '../data+collect'
%0 is recpriref2
fdend
open COLLECT
if (&E) {
errorm 'Error opening COLLECT: ' + &E
}
open COLLECT2
if (&E) {
errorm 'Error opening COLLECT2: ' + &E
}
read COLLECT
while (&E = 0) {
read COLLECT2 using recpriref2 = recpriref
lock COLLECT2
write COLLECT2
if (&E <> 0) {
errorm 'Error writing COLLECT2 record number: ' + recpriref + ', error code: ' + &E
unlock COLLECT2
}
read COLLECT next
}
end
See also
Click here for general information about FACS.
Feedback
