The following topics cover working with records:

Some Btrieve operations can be issued at any time, such as Create (14), Reset (28), and Version (26). However, most Btrieve operations require that you open a file using Open (0). Then, you must establish a position, or currency, in the file before you can operate on any records.

You can establish logical currency based on a key or physical currency by using Step operations.

After you establish currency, you can issue appropriate file I/O operations, such as Insert (2), Update (3), and Delete (4).

Based on your currency, you can move through the file as follows:

You cannot establish physical currency with one operation and then follow with an operation that requires logical currency. For example, you cannot issue a Step First operation and then a Get Next operation.

In data-only files, the MicroKernel does not maintain or create any index pages. You can access the records using only the Step operations and Get Direct/Record (23), all of which use the physical location to find records.

In key-only files, the MicroKernel does not maintain or create any data pages. You can access records using only the Get operations, which use logical currency to find records.

When you have finished working with a file, use Close (1) to close it. When your application is ready to terminate, issue a Stop (25).

Btrieve provides both physical and logical access to your data. With physical access, Btrieve retrieves records based on the physical record address within the file. With logical access, Btrieve retrieves records based on a key value contained in the record. In addition, Btrieve also allows you to access "chunks" of data within a record.

Record accessing by physical location is faster for the following reasons:

Physical currency is the effect on positioning when accessing records by physical location. When you insert a record, the MicroKernel writes that record into the first free space available in the file, regardless of any key values contained in the record. This location is referred to as the physical location, or address, of the record. The record remains in this location until you delete it from the file. The Btrieve Step operations use the physical location to access records.

The record accessed last is the current physical record. The next physical record is the record with the immediately higher address relative to the current physical record. The previous physical record is the record with the immediately lower address. There is no physical record previous to the first physical record; likewise, there is no physical record next to the last physical record.

Together, the current, next, and previous physical locations form the physical currency within a file.

Your application can use the Step operations to access records based on their physical location within a file. For example, the Step First operation (33) retrieves the record that is stored in the first, or lowest, physical location in the file.

The Step Next (24) operation retrieves the record stored in the next higher physical location. Step Previous (35) retrieves the record stored in the next lower physical location in the file. Step Last (34) retrieves the record that is stored in the last, or highest, physical location in the file.

Step Next Extended (38) and Step Previous Extended (39) retrieve one or more records from the physical location following or preceding the current record.

Accessing records by key value allows you to retrieve records based on their values for a specified key.

Logical currency is the effect on positioning when accessing records by key value. When you insert a record into a file, the MicroKernel updates each B-tree index for which the appropriate key in the record has a nonnull value. Each key of a file determines a logical ordering of the records. The ordering is determined by the key's defined sort order or ACS.

The record accessed last is the current logical record. (This record is not necessarily the last record retrieved. The last record could have been retrieved by Get Direct/Chunk (23), which does not change the logical currency.) The next logical record is the record that is immediately next in the defined logical sequence. The previous logical record is the record that is immediately previous in the defined logical sequence. There is no logical record previous to the first logical record; likewise, there is no logical record next to the last logical record.

Together, the current, next, and previous logical records form the logical currency within a file.

The current logical record is also the current physical record, except when you perform an operation that uses the no-currency-change (NCC) option or when you operate on a record with a null key value. For example, you can perform an NCC Insert (2) operation and have the same logical position in the file as you had prior to the insert. The physical currency is updated.

NCC operations are useful when you must preserve your logical currency in order to perform another operation. For example, you may want to insert or update a record and then use a Get Next (6) operation based on your original logical currency.

Your application can use the Get operations to retrieve records based on their values for a specified key. The appropriate Get operation can retrieve a specific record from a file or retrieve records in a certain order.

For example, the Get First (12) operation retrieves the first record by the key specified in the Key Number parameter. Likewise, Get Last (13) retrieves the last record according to the logical order based on the specified key. Some Get operations, such as Get Equal (5) or Get Less Than (10), return a record based on a key value your application specifies in the Key Buffer parameter.

In addition to establishing logical currency, all Get operations except Get Position (22) establish the physical currency. As a result, you can continue with Step Next (24) or Step Previous (35). However, using the Step operations destroys the logical currency.

Reading a variable-length record is the same as reading a fixed-length record in that you use the Data Buffer Length parameter to tell the MicroKernel how much room you have for the record to be returned. Set this parameter to the size of your entire Data Buffer, which can accommodate the maximum amount of data.

After a successful read operation, the Data Buffer Length parameter is changed to reflect the size of the returned record, which is the size of the fixed-length portion plus the amount of actual data in the variable portion (not the maximum size of a record). Your application should use this value to determine how much data is in the Data Buffer.

For example, suppose you have the following records in a data file:

Following are examples of Get Equal operations.

If the returned record is longer than the value specified by the Data Buffer Length, the MicroKernel returns as much data as it can (according to the size the Data Buffer Length was set to) and status code 22.

The Btrieve Data Buffer Length parameter, because it is a 16-bit unsigned integer, limits the record length to 65535. Chunk operations expand the record length well beyond this limit by allowing you to read or write portions of a record. A chunk is defined as an offset and length; the offset can be as large as 64 GB, but the length is limited to 65535 bytes. The limits that your operating system and the Data Buffer Length parameter impose also apply to the chunk operations; however, because the chunk operations can access any portion of a record, the limits have no effect on record length, only on the maximum size of a chunk accessible in a single operation.

For example, using the chunk operations, an application can read a 150,000 byte record by making three chunk retrieval calls. Each chunk in this example is 50,000 bytes long. The first chunk starts at offset zero, the next at offset 50,000, and the final at offset 100,000.

A chunk's offset and length do not have to correspond to any of the internal structures of a record that are known to the MicroKernel, such as key segments, the fixed-length portion of a record, or a variable tail. Also, a chunk does not have to correspond to any portion of the record that your application defines (for example, a field), although you may find it useful to update such defined portions as chunks.

In some cases, the use of chunk operations in client/server environments allows the client Requester to use a smaller Data Buffer Length setting, which lowers the Requester memory requirement. For example, if your application used whole record operations and accessed records up to 50 KB long, your Requester would have to set its Data Buffer Length to at least 50 KB, thereby using 50 KB of RAM. But if your application used chunk operations and limited the size of each chunk to 10 KB, for example, then the Requester could set its Data Buffer Length to 10 KB, thereby saving 40 KB of RAM.

Intrarecord currency is relevant in chunk operations, because it tracks an offset within the current record. The current position is the offset that is one byte beyond the last byte of the chunk that was read or written. This is true even if in your last operation you attempted to read an entire record and the MicroKernel could return only part of that record, which can happen when the Data Buffer Length is inadequate.

The exception is for an Update Chunk (53) operation that uses the Truncate subfunction. In this case, the MicroKernel defines the current position in the truncated record as the offset that is one byte beyond the end of the record itself.

By tracking intrarecord currency, the MicroKernel can do the following:

You access chunks using the Get Direct/Chunk (23) operation and the Update Chunk (53) operation. To use these operations, you must define a chunk descriptor structure that defines the offset and length of the chunk. For the Get Direct/Chunk operation, the chunk descriptor structure must also specify an address to which the MicroKernel returns the chunk.

Before you can use Get Direct/Chunk (23), you must retrieve the physical address of the current record by issuing Get Position (22). You can use a single Chunk operation to retrieve or update multiple chunks in a record by using a next-in-record subfunction bias.

While the MicroKernel is an extremely reliable data management engine, it cannot prevent system failures. System failures are more common in client-server applications, because network failures can occur. You can ensure reliability by taking advantage of these MicroKernel features:

If you are inserting a record with a key value that may already exist and for which you do not allow duplicates, you can proceed in one of two ways:

If your Insert operation stands alone and does not depend on logical currency in the file, executing a Get Equal prior to each Insert is an additional overhead. However, for a group of inserts, the Get Equal operation facilitates any subsequent Insert operations by fetching into memory the index pages that point to the key location.

When designing a variable-length data file, you must decide the maximum size of the variable-length portion of the record your application will support. You should set up a record structure that accommodates the fixed-length portion plus the maximum size of the variable portion. Use this structure as the Data Buffer when reading, inserting, and updating your variable-length records.

When inserting or updating a variable-length record, you use the Data Buffer Length parameter to tell the MicroKernel how much data to write. Set this parameter to the size of the fixed-length portion plus the amount of real data in the variable portion. Do not set the Data Buffer Length to the fixed length plus the maximum size your application allows for the variable field; if you do, the MicroKernel will always write the maximum size.

For example, suppose you want to insert the following record.

Following are examples of Insert operations. Note that the Data Buffer Length is computed as the fixed-length portion plus the amount of data in the Comments field (8 bytes), not the maximum size of the Comments field (1000 bytes).

It is possible to read only the fixed-length portion of a record by setting the data buffer size to that fixed length. The MicroKernel returns only the fixed-length portion and status code 22. However, if you then use Update (3) and pass in only the fixed-length portion, the variable-length portion is lost. Instead, use Update Chunk (53), which updates any part of a record, based on a byte offset and length. Set the byte offset to 0 and the length to the length of the fixed-length portion. This operation updates the fixed-length portion and retains the variable-length portion.

If you attempt to update a key value that is defined as not modifiable, the MicroKernel returns status code 10. If you want to update the key value anyway, you must first Delete (4) the record and then Insert (2) it.

You can perform a variation on the standard insert or update, called a no-currency-change (NCC) operation, by passing in a –1 (0xFF) for the Key Number parameter. NCC operations are useful when an application must save its original logical position in a file in order to perform another operation, such Get Next (6).

To achieve the same effect without an NCC insert operation, you would have to execute these steps:

The NCC Insert operation has the same effect as a standard Insert in terms of logical currency, but can have a different effect in terms of physical currency. For example, executing a Get Next (6) after either procedure produces the same result, but executing a Step Next (24) might return different records.

To maintain original positioning without an NCC update operation, you would have to execute these steps:

Zen provides a high performance mechanism for filtering and returning multiple records or portions of multiple records. The mechanism is called "extended operations" and is supported by four specific operation codes:

For detailed information on how to code these operations, see Btrieve API Guide. This section explains how to optimize your use of these operations for best performance.

The following words can have other meanings in other contexts. For the purposes of this section, definitions are provided below.

Descriptor
Also called the Extended Expression. The whole contents of the data buffer which describes how the Btrieve extended operation should be accomplished.

Filter
A portion of the Extended Expression which describes a selection criteria to apply to the records being selected.

Condition
A portion of a filter that uses a single logical operator.

Connector
A Boolean operator that connects a condition to what follows it. Either AND, OR, or NONE

Extractor
A portion of the Extended Expression which defines what data to return.

Key
A whole index definition which may have multiple segments. Get operations require the MicroKernel to move through the data file along a single key path.

Key Segment
Compound indices, also called multisegmented keys, can have multiple segment definitions. Each segment defines an offset, length, data type, and so on.

The filter evaluation mechanism for extended operations is designed to be very fast. It evaluates expressions in a straightforward manner without doing any extra processing. Because of this approach, you should be aware of some of the idiosyncracies of the interface.

While an extended operation can return a status 62 (Invalid Descriptor) in many ways, the following list includes some of the most common:

Optimizing an extended operation means that the MicroKernel can stop looking at records along the current key path because there is no possibility that records remaining on the key path can satisfy the filter. Starting with PSQL 2000i SP3, the MicroKernel can optimize on multiple conditions as long as they occur sequentially and match the segments of the current key.

As it evaluates each condition, the MicroKernel determines if the given segment can be optimized. In order to do that, all the following must be true:

The actual direction is dependent on both the direction indicated by the operation, but also on whether the current key is descending or ascending, as shown in the following table.

Any one of the following can cause optimization to be turned off for all future segments.

The following examples of multirecord operations are based on this table of sample data:

Consider the table above, which has a compound key on fields 1, 2 and 3 in that order. Assume that the application performs a Get First (12) operation on this file using this three-segment key, followed up with Get Next Extended (36). Notice that these examples contain parentheses where they are assumed to be. This is the only way parentheses can occur when the filter is evaluated from left to right.

Remember that in order to optimize against a key segment, the offset, length, type, case, and ACS identified in the filter condition all must be the same as the key definition.

(Field1 = AAA AND (Field2 = AAA AND (Field3 = AAA)))
The MicroKernel Engine retrieves record 1 and stops searching with status 64 (filter limit reached). The last record examined that satisfies the optimization criteria is record 1, which becomes the current record. Engines before Pervasive.SQL 2000 SP3 can optimize only one condition and thus leave the current record at record 3.

(Field1 = AAA OR (Field2 = AAA OR (Field3 = AAA)))
The MicroKernel Engine retrieves records 1, 2, 3, 4, 5, 6, 7, 10, 13, and 14 and returns with status 9 (end of file reached). No condition can be optimized since the first condition contains an OR connector. The current record becomes record 14.

(Field1 = BBB AND (Field2 = BBB OR (Field3 = BBB)))
The MicroKernel Engine would retrieve records 5, 7, 8, 9, and 11 and return with status 64. The first condition was optimized, but the second condition was not since it contained an OR connector. The last record examined that satisfies the optimization criteria is record 12 which becomes the current record.

(Field4 = OOO AND (Field2 = BBB AND (Field3 = BBB)))
The MicroKernel Engine retrieves records 2 and 8 and return with status 9. No condition can be optimized to the first key segment, so the following segments cannot be optimized. The current record becomes record 14.

(Field1 = BBB AND (first byte of Field2 = B AND (Field3 = BBB)))
The MicroKernel Engine record 8 and returns with status 64. The first two conditions can be optimized, but since the second condition is a substring, the third condition cannot be optimized. The last record examined that satisfies the optimization criteria is record 9. Engines before Pervasive.SQL 2000 SP3 can optimize only one condition and thus return with record 12 as the current record.

(Field1 = BBB AND (Field2 = Field3))
This is done by using the +64 bias on the comparison code, which indicates that the second operand is another field of the record, rather than a constant. The MicroKernel Engine retrieves records 4, 8, and 12 and returns with status 64. The first condition can be optimized, but since the second condition does not compare to a constant, it cannot be optimized. The last record examined that satisfies the optimization criteria is record 12.

(Field1<= BBB AND (Field2 <= BBB AND (Field3 <= BBB)))
The MicroKernel Engine retrieves records 1, 2, 4, 5, 7, and 8 and returns with status 64. The first condition can be optimized, but since it does not have a logical operator of EQ, the following conditions cannot be optimized. The last record examined that satisfies the optimization criteria is record 12.

(Field1= BBB AND (Field2 < BBB AND (Field3 < BBB)))
The MicroKernel Engine retrieves record 4 and returns with status 64. The first condition can be optimized, but since the second condition does not have a logical operator of EQ, it cannot be optimized. The last record examined that satisfies the optimization criteria would be record 12.

(Field1= BBB AND (Field2 = BBB AND (Field3 < BBB)))
The MicroKernel Engine retrieves record 7 and returns with status 64. The first two conditions can be optimized because they use EQ, but the third condition cannot. The last record examined that satisfies the optimization criteria is record 9. Engines prior to Pervasive.SQL 2000 SP3 can optimize on only one condition and so the current record would be 12.

(Field2>= AAA AND (Field2 <= BBB AND (Field1 >= AAA) AND (Field1 <= BBB))))
The MicroKernel Engine retrieves records 1, 2, 4, 5, 6, 7, 8, and 9 and returns with status 64. The first three conditions cannot be optimized to the first key segment, but since they are all ANDed together, the fourth condition can be used to optimize the search. The second condition would be optimizable if it occurred immediately after the fourth condition. But since it is out of position relative to the key segments, it cannot be optimized. Since only one key segment is optimized, the last record examined that satisfies the optimization criteria would be record 12. Note that there is a defect in Pervasive.SQL 2000 SP3 that prevents optimization unless the optimizable condition occurs first. So the SP3 engine would retrieve the same records, but would return status 9.

This section provides some information on how to speed up your operations.

Since extended operations evaluate logical expressions left to right, this feature is by no means a complete expression evaluator that can be used to extract whatever data you want in the most efficient manner. Extended operations are designed to be used in conjunction with a Get or Step operation to initially set the cursor in the file to the correct position. So the first suggestion is:

In other words, it may be appropriate to add some OR’ed conditions for fields not in the index after an optimizable condition for an indexed field. For example, let say you are searching a nationwide phone book for everyone in Texas with first name "William", "Bill", "Billy" or "Billybob". Using a key on the State field, you would use GetEqual to set the current record on the first person in Texas. Then call GetNextExtended with a filter like (State = "Texas" AND (FirstName = "William" OR (FirstName = "Bill" OR (FirstName = "Billy" OR (FirstName = "Billybob"))))). If your Extractor indicated a reject count of 10,000 and a return count of 100 records, the GetNextExtended would return after looking at about 10,000 records. But with 14 million people in Texas, you will need to keep issuing the same GetNextExtended operation over and over until you finally get to Utah and a status 64 (filter limit reached). This process would be much faster than having each record transferred to your application one at a time.

But what if a compound index existed on State and FirstName? The Get Next Extended above would still work, but it would be much faster to do a Get Equal and Get Next Extended on each of the four State and FirstName combinations, optimizing on both fields.

So you can see that having filters with OR connectors is only useful when no index is available. Priority should be placed on AND connectors for fields that match the key.

Another issue that you should understand is how to set the reject count. If your application is the only one being serviced by a MicroKernel engine, then using the maximum reject count is most efficient since it keeps that network traffic or interprocess communication to a minimum. However, if there are a lot of applications running in a highly concurrent environment, there can be serious consequences if you have a reject count that is too large.

The MicroKernel can handle many Btrieve requests concurrently even while doing Btrieve operations atomically on a file. So it allows any number of reader threads, but only one write thread, to access the same file. Most Btrieve operations are read operations and they do not take much time to accomplish. So if a write operation comes in, it waits until all readers are finished before it locks the file for the instant it takes to insert, update, or delete a record. This coordination works great until a read operation comes in that takes a long time to finish. That is what an extended operation with a high reject count will do if it does not find any records. It keeps reading and reading and reading. The other read operation can get done without a problem, but the write operations start to back up. After a write operation has tried to get write access to the file 100 times, it reaches what is called a frustration count. At this time, it puts a block on all new reader threads. So now all Btrieve operations on this file are hung until the extended operation is done.

Even with a reject count of 100 to 1000, it is better to have the MicroKernel read and reject them than it is to have the records returned to your application to reject them.

Btrieve provides two operations for adding and dropping keys in your files: Create Index (31) and Drop Index (32). The Create Index operation is useful for adding keys to a file after the file has been created. The Drop Index operation is useful for removing keys whose index pages have become damaged. After dropping a key, you can re-add it, which causes the MicroKernel to rebuild the index.

When you drop a key, the MicroKernel renumbers all higher-numbered keys, unless you specify otherwise. The MicroKernel renumbers the keys by decrementing all higher-numbered keys by 1. For example, suppose you have a file with key numbers 1, 4, and 7. If you drop key 4, the MicroKernel renumbers the keys as 1 and 6.

If you do not want the MicroKernel to automatically renumber keys, add a bias of 0x80 to the value you supply for the Key Number parameter. This allows you to leave gaps in the key numbering; consequently, you can drop a damaged key and then rebuild it without affecting the numbering of other keys in the file. You rebuild the index using Create Index (31), which allows you to specify a key number.