rec_fetch()

Purpose

Reads a record from the database

Old name

#define fetch rec_fetch

Syntax (CCOWN)

int rec_fetch(int flags, short *datarec, int index, int role,ADDITIONAL)

Syntax (DP4DBAPI)

BOOLEAN dp4_rec_fetch(DCONN * dconn,short * datarec, int flags,int index,int index_role, int match_keys);

Parameters

DCONN * dconn

Handle to database connection

int flags

This parameter determines the search method used. This parameter is formed from a combination of at least one direction flag, and zero or more other flags, as explained in the following tables:

Direction Flags

Direction	Meaning
EQUAL	Searches for a record matching on all the keys. For a non unique secondary index, if there is more than one possible matching record EQUAL finds the first such record in the index sequence, unless you also specify PREV, in which case it will find the last search record
FIRST	Finds the first record in the index sequence. If a non zero value was specified for the match_keys parameter, and the DEPTH flag is effective, then the first record in the sequence with the specified number of matching keys is found.
LAST	Finds the last record in the index sequence. If a non zero value was specified for the match_keys parameter, and the DEPTH flag is effective,then the last record in the sequence with the specified number of matching keys is found
NEXT	Finds the record with the next higher key than the key initially in datarec. Note that for non unique secondary indexes the key returned may actually match the initial key. Internally the database manager distinguishes between identical keys using an entity known as the find list, which is explained later. There are some restrictions on when you can use a NEXT (or PREV) search on a secondary index.
PREV	Similar to NEXT, but goes backwards instead of forwards

In calls to rec_fetch() itself you will generally only pass one direction flag, though the combinations of EQUAL|NEXT and EQUAL|PREV are meaningful: they return a matching record if there is one, or the nearest record in the appropriate direction otherwise. When you call a block or buffered fetch function two other combinations are also meaningful: FIRST|NEXT and LAST|PREV. It should be noted that searching forwards through an index is slightly faster than search backwards, though both are efficient.

All the five basic direction flags apart from the EQUAL flag can be modified with the BUT and DEPTH flags, as explained in the next section.

FIRST,LAST and EQUAL fetches are called terminal fetches: presented with the same intiial record and other parameters the database manager will always return the same record. NEXT and PREV searches are different ("no terminal"), because the record returned by a call to a fetch function may be different according to what records have been fetched previously. The record to be found depends on the find list, which stores an extra hidden key component for every record on the database, known as its sequence number or row identifier. When doing a NEXT or PREV search the row identifier of the record previously fetched to that table/memory address combination is used as the extra key component.

When accessing records using the primary index, NEXT and PREV searches are effectively also terminal, because the database manager ignores the find list since there can be only one record with a given key. But for a secondary index the find list is not ignored unless NO_FINDLIST is specified. Where a secondary index is non-unique a fetch using a PREV or NEXT value on a record area that has been modified since it was last used for a fetch() will have indeterminate results: If you set the key fields for a record when using a secondary index, you should always perform one of the three "terminal" fetches before embarking on a sequence of NEXT or PREV fetches to read further data. Even if the secondary index is unique you may still encounter unexpected results if you do a NEXT or PREV search with a changed key: the database manager treats such a secondary index like any other secondary index, and uses the find list information - a fetch NEXT or PREV with a changed key might or might not find a matching record depending on the relative values of the row identifier of the matching record and the row identifier in the find list.

Direction Modifier flags

There are two main direction modifier flags: DEPTH and BUT. The DEPTH flag instructs the database manager to respect the match_keys (or depth) parameter: any records returned will have a key matching at least as many leading keys as were specified in the match_keys parameter, so that NEXT,PREV,FIRST, and LAST searches are all relevant to the subset of records determined by the contents of the datarec parameter and the match_keys parameter. In the CCOWN implementation of fetch functions you must specify the DEPTH flag explicitly if you want to pass a non zero match keys parameter. Conversely, in the DP4DBAPI implementation, since you have to pass match_keys anyway, the DEPTH flag is added automatically if you specify a non zero value for match_keys.

The BUT modifier instructs the database manager to match the number of keys specified in the index properties. For a secondary index this is always all the keys. For the primary index it is the number of keys in the prime parent if there is one, or all the keys otherwise. Since the number of keys the BUT value matches might change if the database structure is changed, most DP4 programmers prefer to specify an explicit match_keys parameter rather than relying on this flag.

For historical reasons, the DP4 C include files define all the various possible combinations of direction and modifier flags explicitly, so you can use FIRST_DEPTH, NEXT_BUT and so on, as well as FIRST|DEPTH NEXT|BUT. The two forms are identical in effect in every case.

In theory you can also specify NO_FINDLIST as a direction modifier. This causes the stored find list position to be ignored and for an "impossible" sequence number to be used, so that a NEXT or PREV record would always find a record with a different key from the one initially passed in. This might be useful if you were implenenting some kind of "select distinct" functionality, but is rarely if ever used in practice.

Other flags

The following values may also be added to the parameter flags:

NO_UPDATE	If the record has been updated but db_commit() has not been called, the original version of the record is fetched even if the PRIVATE_DB flag is in effect. Passing NO_UPDATE can improve performance considerably when the private database is in effect, if you are scanning through a table sequentially updating records as you go and do not want to commit until all the records have been updated.
NO_DATA	The record is looked up in the index and the keys for the record found are copied back into it. The record is not read from the data file and the other data fields are not filled in. This provides a fast way of reading the record if the application only requires the fields which are key fields for the index being used. For example the count mode option of DYNACHEK uses this flag, and it is also used by a number of deletion programs.
NO_READ	The record found is not read from the database although the return value and depth are set as appropriate. Note that this flag cannot be used with NEXT or PREV effectively, except in the unusual case where you just want to count, but not read, the number of records with a specific key.
LOCK	If a matching entry is found and is not locked by another user, it is marked as locked. For the CCOWN API the global variable locked may be tested to see whether the record was already locked by another user. In the DP4DBAPI you can test the flags field of the record header to see if the F_LOCKED bit is set (this also works in CCOWN).
UNLOCK	If the record is locked by this program, it is unlocked immediately instead of at the next call to the function db_checkpoint()
UPDATE_ONLY	The record is looked up only in the work file in which the application program keeps records that have been posted to the database but not yet committed. When this flag is used, it is permissible for the application program to use an index which has updating turned off.

short* datarec

Pointer to the L field of a database record to be fetched. LIBMAKE is used to declare suitable variables to be used as record areas for fetch() functions to work with.

int index

Index number of the index used. You can use the #index toggle of LIBMAKE to generate suitable constants for both the index and role parameters

int role

Role number of the index used

int match_keys

Number of keys to match when doing a FIRST,LAST,NEXT or PREV fetch(). For the CCOWN API this parameter is optional, and you must pass DEPTH as one of the flags for it to be respected. In DP4DBAPI you must pass this parameter, and should pass 0 if you do not want to limit the search to matching keys at all.

Description

rec_fetch() is the most general function for fetching database records one at a time, as it allows you to specify a search index unlike rec_fetch_main() which always uses the primary index. If you want to process several records table sequentially, for example in report programs, then you should use buffered fetch functionality to read the database.

Return values

Returns whether the read was successful or not

See also

rec_fetch_main(),bf_fetch()

Example

rec_fetch(EQUAL,&customer.l,0,0);

This is the same as:

rec_fetch_main(EQUAL,&customer.l);

and

rec_fetch(EQUAL,&customer.l,_REGIONS,0);

The Find List and Nested Searches

As explained above, when searching on a secondary index, the database manager uses an extra key component, to remember where in a sequence of identical keys the search has got to. You may sometimes need to do some kind of recursive search on a secondary index, to enumerate a complete bill of materials for instance.

You may wonder how the find list can keep track of the search position in this case. In order for the searches to proceed as expected you must do one of two things:

Allocate a new table structure for each level of the search. The find list is keyed on the memory address of the record area being used in the search. So if each level of the search uses a separate area there is no problem. This solution is convenient in C++ programs, provided the level of recursion is not likely to exceed the application stack or heap size (depending on exactly how the record area are allocated).
You can use one record area for all the levels. Before entering a new level of the search save the primary key of the current record. When you return from the recursice search, restore the primary key and fetch the record again using rec_fetch_main() with an EQUAL direction. This will restore the correct record, and reset the find list to the correct position in the index.