The FairCom DB API .NET record management hides API from the user all the complexities of maintaining a generic record buffer for tables. While fixed length records may be easier to implement and maintain, variable length records do present a greater challenge for the developer.

When a table contains variable length records, each record read may require buffers of different sizes. The FairCom DB API .NET record manager performs the function of expanding and shrinking record buffers to deal with the necessities of variable length records. The c-tree ISAM API also requires different API calls for fixed length and variable length records, but the FairCom DB API .NET presents to the user one common API that deals with both fixed and variable length records.

Only the application architecture and system resources limit the number of FairCom DB API .NET record buffers associated with a table.

Creating a Record Object

A valid record object is required before most record operations can take place. You need to pass a valid CTTable object to the CTRecord() constructor.

// create a CTRecord object
CTRecord ARecord = new CTRecord(ATable);

After the record object has been created, but before any record operations can take place, the table associated with the record object must be opened.

Record Buffer Layout

Based on the fields added by the user, FairCom DB API .NET will organize the record buffer as follows:

The user fields are stored in the table record in the same order in which they were added. To initiate the operation on the records, a record handle must be declared and instantiated. Once the table associated with the record handle is opened, the record can be used to store the information that comes from or goes to the fields in the table.

Sharing the Same Context

A record object may be reset to its initial state (that is, to the same state it had just after being created). The record reset function will perform the following actions on a record handle:

• The context associated with the record is released
• Record sets are turned off
• Memory allocated for the record buffer is released
• Record properties are re-initialized.

// reset a record
try
{
   ARecord.Reset();
}
catch (CTException err)
{
   Console.Write("Record reset failed with error {0}\n", err.GetErrorCode());
}

All records associated with a table can be reset in one operation by calling CTTable.ResetAll() method.

// reset all records
try
{
   ATable.ResetAll();
}
catch (CTException err)
{
   Console.Write("Reset all records failed with error {0}\n", err.GetErrorCode());
}

When a table is closed, all records associated with that table are automatically reset by the CTTable.Close() method.

Resetting a Record

A record object may be reset to its initial state (that is, to the same state it had just after being created). The record reset function will perform the following actions on a record handle:

• The context associated with the record is released
• Record sets are turned off
• Memory allocated for the record buffer is released
• Record properties are re-initialized.

// reset a record
try
{
   ARecord.Reset();
}
catch (CTException err)
{
   Console.Write("Record reset failed with error {0}\n", err.GetErrorCode());
}

All records associated with a table can be reset in one operation by calling CTTable.ResetAll() method.

// reset all records
try
{
   ATable.ResetAll();
}
catch (CTException err)
{
   Console.Write("Reset all records failed with error {0}\n", err.GetErrorCode());
}

When a table is closed, all records associated with that table are automatically reset by the CTTable.Close() method.

The Default Index

Some of the record operations such as record navigation, finding records and record sets require an index to be specified. The default index property is used by the record functions that require an index on which to operate.

Any of the user defined indexes or the RECBYT or ROWID indexes (in the case these are created) may be used as the index controlling the search. To set the default index, use one of the overloaded CTRecord.SetDefaultIndex() methods. You can use the index number of the index name to identify the default index.

// create a new record object
CTRecord ARecord = new CTRecord(ATable);

//set the default index to "MyIndex"
try
{
   ARecord.SetDefaultIndex("MyIndex");
}
catch (CTException err)
{
   Console.Write("Set default index failed with error {0}\n", err.GetErrorCode());
}

Use CTRecord.GetDefaultIndex() to retrieve the current index number or CTRecord.GetDefaultIndexName() to retrieve the name of the current default index.

// make sure the default index is set to "MyIndex"
try
{
if (ARecord.GetDefaultIndexName() != "MyIndex")
{
      ARecord.SetDefaultIndex("MyIndex");
   }
}
catch (CTException err)
{
   Console.Write("Set default index failed with error {0}\n", err.GetErrorCode());
}

If no index is set as the default index before the first search, the default index is the first user-defined index. Once an index is chosen as the default index, all subsequent searches will be performed using this index, until a new selection is made for the default index. For instance, the user may set the default index as the ROWID index, search for the first input record, then change the default index to the user defined index (i_name, for instance, an index over the name field in the table), and search for the next record on this particular sequence, taking as the previous record the first input record.

A user may perform a chronological search of the records in a table using CTRecord.First() and CTRecord.Next(), and setting the ROWID index as the default index.

Selecting the RECBYT Index

The RECBYT index can be selected as the default index in order to perform record navigation with CTRecord.First(), CTRecord.Last(), CTRecord.Next() and CTRecord.Prev(), ordered by the record offset. The RECBYT index can be selected as the default index by passing the index value of IDXNO.RECBYT_NO to CTRecord.SetDefaultIndex() method.

// set the RECBYT index as the default index
try
{
   ARecord.SetDefaultIndex( (NINT) IDXNO.RECBYT_NO);
}
catch (CTException err)
{
   Console.Write("Set default index failed with error {0}\n", err.GetErrorCode());
}

Selecting the ROWID Index

The ROWID index can be selected as the default index in order to perform record navigation with CTRecord.First(), CTRecord.Last(), CTRecord.Next() and CTRecord.Prev(), ordered by the ROWID value of each record. The ROWID index can be selected as the default index by passing the index value of IDXNO.ROWID_NO to the CTRecord.SetDefaultIndex() method.

// set the ROWID index as the default index
try
{
   ARecord.SetDefaultIndex( (NINT) IDXNO.ROWID_NO);
}
catch (CTException err)
{
   Console.Write("Set default index failed with error {0}\n", err.GetErrorCode());
}

Navigating Records

FairCom DB API .NET provides features that allow navigation among records of a particular table. You can position the current record at the first record, last record, next record, previous record, or seek to a specific record if you know the record offset.

Setting a different default index for the record may change the order of the record navigation.

The record navigation function not only updates the current position of a record, but the record data is read from disk and placed in the record buffer inside the record handle. As the record is read from disk, the new record flag is set to false to indicate that the data in the record buffer originated from an existing record in the table.

First Record

Call CTRecord.First() to position the current record at the first record of a table. If the table is empty (i.e., the table contains no records, CTRecord.First() returns false).

// get the first record of the table
try
{
if (ARecord.First())
   Console.Write("First record found\n");
else
   Console.Write("Table is empty\n");
}
catch (CTException err)
{
   Console.Write("First record failed with error {0}\n", err.GetErrorCode());
}

Last Record

Call CTRecord.Last() to position the current record at the last record of a table. If the table is empty (i.e., the table contains no records, CTRecord.Last() returns false).

// get the last record of the table
try
{
if (ARecord.Last())
   Console.Write("Last record found\n");
else
   Console.Write("Table is empty\n");
}
catch (CTException err)
{
   Console.Write("Last record failed with error {0}\n", err.GetErrorCode());
}

Next Record

Call CTRecord.Next() to position the current record at the next record of a table. If the current record is already the last record of a table, CTRecord.Next() returns false.

// get the next record of the table
try
{
if (ARecord.Next())
   Console.Write("Next record found\n");
else
   Console.Write("No more records\n");
}
catch (CTException err)
{
   Console.Write("Next record failed with error {0}\n", err.GetErrorCode());
}

A current record must exist before CTRecord.Next() is called or an ICUR_ERR (100) is thrown indicating that there can be no next record if the current record does not exist. You can establish a current record by calling CTRecord.First(), CTRecord.Last(), CTRecord.Find() or CTRecord.SeekRecord().

Previous Record

Call CTRecord.Prev() to position the current record at the previous record of a table. If the current record is already the first record of a table, CTRecord.Prev() returns false.

// get the previous record of the table
try
{
if (ARecord.Prev())
   Console.Write("Previous record found\n");
else
   Console.Write("Already at first record\n");
}
catch (CTException err)
{
   Console.Write("Prev record failed with error {0}\n", err.GetErrorCode());
}

A current record must exist before CTRecord.Prev() is called, or an ICUR_ERR (100) exception is thrown indicating that there can be no previous record if the current record does not exist. You can establish a current record by calling CTRecord.First(), CTRecord.Last(), CTRecord.Find() or CTRecord.SeekRecord().

Seek to Record

If you know in advance the offset of a given record, CTRecord.SeekRecord() can be used to make it the current record.

You can use CTRecord.GetRecordPos() and CTRecord.SeekRecord() to implement a bookmark system for your records. Retrieve the record offset with CTRecord.GetRecordPos(), which is the bookmark value, and later on you can quickly go back to the record by calling CTRecord.SeekRecord().

// get record bookmark
long GetBookMark(CTRecord &ARecord)
{
return ARecord.GetRecordPos();
}

// goto record bookmark
void GotoBookmark(CTRecord ARecord, long bookmark)
{
   ARecord.SeekRecord(bookmark);
}

Finding Records

You can search for records in a table using one of the CTRecord.Find(), CTRecord.FindTarget() and CTRecord.FindRowid() methods. FairCom DB API .NET performs the find operations against the table indexes. When an index entry is found, the record data is loaded into the record buffer.

Before you can call CTRecord.Find(), you need to prepare the data you want to find:

1. Clear a record buffer
2. Set the default index with the appropriate value
3. Populate the fields that make up the index segment
4. Call CTRecord.Find() with the appropriate find mode

// find record with product code of DISKETTE
CTRecord ARecord = new CTRecord(ATable);

// clear the record buffer
ARecord.Clear();

// set the default index to "MyIndex"
ARecord.SetDefaultIndex("MyIndex");

// populate the 'product' field
ARecord.SetFieldAsString("product", "DISKETTE");

// find the record
try
{
   if (ARecord.Find(FIND_MODE.EQ))
      Console.Write("Record found\n");
   else
      Console.Write("Record not found\n");
}
catch (CTException err)
{
   Console.Write("Record find failed with error {0}\n", err.GetErrorCode());
}

CTRecord.Find(), CTRecord.FindTarget() and CTRecord.FindRowid() return true if the record was found or false if the record was not found. These methods throw an exception if an error occurs.

Find Modes

Use the following find modes with the record find methods:

Note: The Find Mode CTFIND_EQ requires that the target contains values for all segments that compose the index and the index cannot allow duplicates.

Note: FairCom DB API .NET defines this mode with the FIND_MODE enum.

Finding Records by Target Key

A record can be found by passing a target key already built by calling CTRecord.FindTarget() method. CTRecord.FindTarget() takes as its parameters a pointer to a target key and the find mode.

// find record with a target key
CTRecord ARecord = new CTRecord(ATable);
byte[] key = new byte[256];
int keylen = 256;

// clear the record buffer
ARecord.Clear();

// set the default index to index 0
ARecord.SetDefaultIndex(0);

// populate the 'product' field
ARecord.SetFieldAsString("product", "DISKETTE");

// build the target key
ARecord.BuildTargetKey(FIND_MODE.EQ, key, ref keylen);
try
{
   if (ARecord.FindTarget(key, FIND_MODE.EQ))
      Console.Write("Record found\n");
   else
      Console.Write("Record not found\n");
}
catch (CTException err)
{
   Console.Write("Find target failed with error {0}\n", err.GetErrorCode());
}

Finding Records by ROWID

A record can be quickly located if the rowid for that record is known by calling CTRecord.FindRowid(). CTRecord.FindRowid() take as its parameters the ROWID value and the find mode.

// find record with rowid of 1000
CTRecord ARecord = new CTRecord(ATable);
try
{
   if (ARecord.FindRowid(1000, FIND_MODE.EQ))
      Console.Write("Record found\n");
   else
      Console.Write("No record found\n");
}
catch (CTException err)
{
   Console.Write("Find Rowid failed with error {0}\n", err.GetErrorCode());
}

Record Sets

An alternative and more efficient way to retrieve records is the use of Record Sets. A record set contains part of a table, representing a group of records that have keys that match a certain target. For example, suppose an index contains the following keys:

CASE DISKCASE DISKDRIVE DISKETTE KEY KEYBOARD KEYCAP

When scanning through the index looking for records, record navigation functions work on the entire index file. Navigating to the first record obtains the first key in the index, CASE, and moving to the next record obtains each of the following keys in turn, on through KEYCAP.

When creating a record set using a record set function, with a target of "DISK" and a target length of 4, FairCom DB API returns only the records in which the first 4 bytes of the key start with "DISK".

The record navigation functions will operate only on the records that match the set until the record set operation is terminated with a call to a record set function. To work with multiple record sets at once, use more than one record handle.

Record sets allow a faster search for records, particularly in client/server mode since fewer records may be retrieved and less traffic on the network will be generated. When used in conjunction with filters (see below), one can create simple queries.

To use record sets, follow the steps below:

1. Clear the record buffer
2. Set the index you want to use
3. Populate the index segments you would like to participate in the set
4. Establish the Set

Creating a Record Set

To create a record set, perform the following steps

1. Clear a record handle
2. Select the default index
3. Populate the field, or fields, that form the partial index
4. Call CTRecord.RecordSetOn() to create a record set

 // create a new record object
CTRecord ARecord = new CTRecord(ATable);
 
// clear the record buffer
ARecord.Clear();
 
// populate the fields
ARecord.SetFieldAsString(0, "DISK");
 
// create the record set
try
{ 
   ARecord.RecordSetOn(4);
}
catch (CTException err)
{
   Console.Write("Record set on failed with error {0}\n", err.GetErrorCode());
}

 Once the record set is created, CTRecord.First(), CTRecord.Next(), CTRecord.Last() and CTRecord.Prev() will return only the records that match the record set criteria.

Terminating a Record Set

Once the record set is no longer necessary, you can turn it off by calling CTRecord.RecordSetOff().

// terminate the record set
try
{
   ARecord.RecordSetOff();
}
catch (CTException err)
{
   Console.Write("Record set off failed with error {0}\n", err.GetErrorCode());
}

Once the record set is terminated, CTRecord.First(), CTRecord.Next(), CTRecord.Last() and CTRecord.Prev() will return all records of a table.

Record Filters

FairCom DB API .NET allows the user to define filters on the records using CTRecord.SetFilter(). When a filter is set, all records retrieved with this CTRecord will be filtered against the given expression, and just those records that match will be returned.

// set the filter
try
{
   ARecord.SetFilter("ZIPCODE == 12345");
}
catch (CTException err)
{
   Console.Write("Filter record failed with error {0}\n", err.GetErrorCode());
}

Only the user who sets the filter will have its records filtered. The filter is turned off when the table is closed, or when CTRecord.SetFilter() is called with an empty string as the filter expression.

Important Note: Only one filter may be active per CTRecord per user at once, so if a new filter is set to a CTRecord with an existing filter, the old filter will be overridden.

// terminate the filter
try
{
   ARecord.SetFilter("");
}
catch (CTException err)
{
   Console.Write("Filter record failed with error {0}\n", err.GetErrorCode());
}

Use CTRecord.GetFilter() to retrieve the current filter expression. If no filters are active, CTRecord.GetFilter() returns an empty string. Use CTRecord.IsFiltered() to test if filters are active for a CTRecord.

When used in the client/server model, this feature has the potential to increase the performance since just the records matching the criteria will be returned, reducing the network traffic.

Record filters, when used in conjunction with sets, provide the FairCom DB API .NET API with simple query capabilities.

FairCom DB Expression Parser and Grammar

A powerful expression parser/analyzer provides for complex conditional expressions that can be defined and evaluated at runtime.

Filter expression syntax closely follows the C language syntax for expressions, including order of precedence. An expression interpreted by the expression parser should compile without errors with a standard C compiler. As in C, you cannot compare strings directly like LastName > 'S'. However, the expression parser has a number of built-in functions that allow the comparison of strings. Example:

strcmp( LastName, "S" ) > 0

The expression handling assures proper alignment considerations are handled, and ensures buffer size of any record being evaluated is big enough.

Routines that evaluate conditional expressions maintain fixed data record lengths and total data record lengths. This permits correct alignment adjustments and detects if insufficient data is available. The latter condition results in a CVAL_ERR (598) error. The easiest way to produce a CVAL_ERR (598) is to read only the fixed-length portion of a data record, and have an expression that relies on fields in the variable-length portion of the record.

For additional control, a Conditional Expression Callback Function is available. This allows advanced control through an external user created function.

See also

• Data Filters
• Conditional Index Support
• Partitioned Files
• cndxparse
• cndxeval
• cndxrun
• cndxfree
• ctparsedoda
• getcndxmem
• putcndxmem

Constants

The expression parser uses the constants below internally as a 32-bit signed or unsigned integer:

• Character constants: any valid char enclosed in single quotes, e.g., 'a'
• Signed Integer constants: values from - 2,147,438,647 to 2,147,438,647
• Unsigned Integer constants: values from 0 to 4,294,967,295
• Hexadecimal constants: values from 0x00000000 to 0xffffffff. Any combination of lower case or upper case letters are accepted, e.g., 0Xbc4f or 0xF5C56d
• Date constants:

{d'yyyy-[m]n-[d]d'}

yyyy is 4 digit year

[m]m is 1 or 2 digit month

[d]d is 1 or 2 digit day

Examples:

{d'2024-12-06'}

{d'2024-8-6'}

'[m]m-[d]d-yyyy' or '[m]m/[d]d/yyyy'

yyyy is 4 digit year

[m]m is 1 or 2 digit month

[d]d is 1 or 2 digit day

Examples:

'12-06-2024'

'8-6-2024'

'8/6/2024'

'yyyy-[m]m-[d]d' or 'yyyy/[m]m/[d]d'

yyyy is 4 digit year

[m]m is 1 or 2 digit month

[d]d is 1 or 2 digit day

Examples:

'2024-12-06'

'2024-8-6'

'2024/8/6'

'[d]d-mmm-yyyy' or '[d]d/mmm/yyyy'

[d]d is 1 or 2 digit day

mmm is 3 letter month name

yyyy is 4 digit year

month names (case insensitive) are:

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

Examples:

'1/jan/2024'

'12-feb-2024'

Time constants

{t'[h]h:[m]m:[s]s'}

[h]h is 1 or 2 digit hour

[m]m is 1 or 2 digit minute

[s]s is 1 or 2 digit second

Examples:

{t'23:11:33'}

{t'1:2:3'}

'[h]h:[m]m:[s]s[:[i][i]i]'

[h]h is 1 or 2 digit hour

[m]m is 1 or 2 digit minute

[s]s is 1 or 2 digit second

[i][i]i is optional 1 to 3 digit millisecond

Examples:

'1:2:3'

'12:33:44.123'

Timestamp constants

{ts'yyyy-[m]m-[d]d [h]h:M[M]:s[s]'}

yyyy is 4 digit year

[m]m is 1 or 2 digit month

[d]d is 1 or 2 digit day

[h]h is 1 or 2 digit hour

[M]M is 1 or 2 digit minute

[s]s is 1 or 2 digit second

Examples:

{ts'2024-08-12 22:33:44'}

{ts'2024-08-12 2:3:4'}

'[m]m-[d]d-yyyy [h]h:[M]M:[s]s[:[i][i]i]' or '[m]m/[d]d/yyyy [h]h:[M]M:[s]s[:[i][i]i]'

[m]m is 1 or 2 digit month

[d]d is 1 or 2 digit day

yyyy is 4 digit year

[h]h is 1 or 2 digit hour

[M]M is 1 or 2 digit minute

[s]s is 1 or 2 digit second

[i][i]i is optional 1 to 3 digit millisecond

Examples:

'08-12-2024 22:33:44'

'08/12/2024 2:3:4.123'

'yyyy-[m]m-[d]d [h]h:[M]M:[s]s[:[i][i]i]' or 'yyyy/[m]m/[d]d [h]h:[M]M:[s]s[:[i][i]i]'

yyyy is 4 digit year

[m]m is 1 or 2 digit month

[d]d is 1 or 2 digit day

[h]h is 1 or 2 digit hour

[M]M is 1 or 2 digit minute

[s]s is 1 or 2 digit second

[i][i]i is optional 1 to 3 digit millisecond

Examples:

'2024-08-12 22:33:44'

'2024/08/12 2:3:4.123'

'[d]d-mmm-yyyy [h]h:[M]M:[s]s[:[i][i]i]' or '[d]d/mmm/yyyy [h]h:[M]M:[s]s[:[i][i]i]'

[d]d is 1 or 2 digit day

mmm is 3 letter month name

yyyy is 4 digit year

[h]h is 1 or 2 digit hour

[M]M is 1 or 2 digit minute

[s]s is 1 or 2 digit second

[i][i]i is optional 1 to 3 digit millisecond

month names (case insensitive) are:

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

Examples:

'08-dec-2024 22:33:44'

'08/dec/2024 2:3:4.123'

Any integer larger than the maximum size allowed for integers, or any number with decimal points, or any numbers in scientific notation are interpreted as a floating point constant by the expression parser .

String constants are similar to C string constants and represent any text enclosed by double quotes, for example, "This is a string". The maximum size of a string constant defaults to 255 characters. The filter expression parser allows the following escape characters in string constants or character constants:

Variables

A filter expression variable is actually the name of the fields defined for the table. There is a limit of 128 characters for the name of variables and the names are case sensitive.

When a user specifies a variable name, the filter parser searches the table definition for a field of that name. The parser uses the type of the field and converts it to the types used internally by the expression evaluator. The conversion of field types is as follows:

Please note that "int" is a LONG, "unsigned" is a ULONG and "char*" is a pTEXT.

Field names that match a valid expression reserved word:

Consider a field named "year", which collides with the function YEAR. The expression "[year] == 2000" is needed to handle "year" as a field name rather than the function.

Parentheses

Use parentheses exactly like they are used in C expressions. There are no limits on the number of parentheses you may use in an expression, as long as each open parenthesis has a closing parenthesis. Parentheses are also used to enclose the arguments of built-in functions.

Predefined Functions

The FairCom DB conditional expression parser has numerous built-in functions for advanced conditional filtering possibilities. They are described in the Predefined Functions section of the FairCom DB Developer's Guide.

Type Casting

The filter expression parser allows you to use explicit type casts in expressions. This is very useful if you are comparing fields of different types and want to control the result of an expression.

For example, suppose "Salary" is a CT_MONEY field and "Average" is a CT_DFLOAT field; type casts can be used as illustrated in the following expression: (Salary - (int)Average) > 500

The following type casts may be used in conditional expressions:

• (int) or (long): Convert the result of expression to integer (32 bit).
• (unsigned [int | long]): Convert the result of expression to unsigned integer (32 bit).
• (double): Convert the result of expression to double.

You cannot type cast a string expression.

Automatic Type Promotion

When mixing different types in an expression without explicit type casting, the conditional expression parser automatically promotes the types using the following rule:

• signed and unsigned integers: promoted to unsigned integer
• signed integer and double: promoted to double
• unsigned integer and double: promoted to double

In the great majority of cases, mixing strings with numeric values returns a parsing error.

Operators

The following operators are allowed in conditional expressions:

• Mathematical operators
  • + Adds two operands
  • - Subtracts two operands or negates an operand (e.g., -5)
  • * Multiplication
  • / Division
  • % Modulus
• Relational operators
  • == Equal to
  • != Not equal to
  • < Less than
  • <= Less or equal to
  • > Greater than
  • >= Greater than or equal to
• Logical operators
  • && And
  • || Or
  • ! Not
• Binary operators
  • & And
  • | Or
  • ~ Not
  • ^ Xor

Record Batches

There are situations where a record fetch, a record insert, or a record delete operation has to be applied to a group of related records. For example, if an invoice record is being displayed, it may be necessary to retrieve all invoice items for that invoice as well. The following code fragment shows a typical FairCom DB API C function to extract all items of an invoice, given the invoice number.

For more information about batch processing of records, see the section in the FairCom DB API C++ Developer's Guide titled Record Batches.

Reading and Writing Field Data to a Record Buffer

The FairCom DB API .NET CTRecord class has the following methods to read field data from the record buffer:

To maintain compatibility with the "non .NET" FairCom DB API API, the following other methods are available:

// display all field data on screen
void DisplayData(CTTable ATable, CTRecord ARecord)
{
   int count = ATable.GetFieldCount();
   int i;
string value;
 
   for (i = 0; i < count; i++)
   {
      value = ARecord.GetFieldAsString(i);
      Console.Write("Field {0}: {1}\n", i, value);
   }
}

The following functions should be used to write fields into the data record buffer:

To maintain compatibility with the "non .NET" FairCom DB API API, the following additional methods are available, although they all are considered "obsolete" and should be replaced by SetFieldValue():

// add new record
void AddRecord(CTRecord ARecord, string name, string address, string phone)
{
   // clear the record buffer
   ARecord.Clear();
   // populate the record buffer with field data
   ARecord.SetFieldAsString(0, name);
   ARecord.SetFieldValue(1, address);
   ARecord.SetFieldAsString(2, phone);
   // add the new record
   ARecord.Write();
}

The SetFieldAs...() and SetFieldValue() methods will also clear the null bit flag for the updated field.

When you invoke one of the old GetFieldAs...() methods, you pass the field number or field name and the method returns the data you want to read from the data record buffer.

When you invoke the GetFieldValue() method, you pass the field number or field name and the variable you want to contain the data you want to read from the data record buffer (marked as out).

When you invoke one of the SetFieldAs...() or SetFieldValue() methods, you pass the field number or field name and the data you want to write to the data record buffer.

If the type of the field you are trying to read from or write to is different from the type of the data specified by the GetField or SetField methods, the record manager will automatically convert the field data type to match the data type of the parameter passed by the GetField or SetField method.

For example, if you are writing a report generator application that displays the fields of a record on screen, you can read all fields in the record buffer with GetFieldAsString() and the record manager will convert the different field types to string. The boolean field type is converted as "True" or "False", numeric values are converted to strings, and dates and times use the session wide default date and time formats.

Automatic Data Type Conversion

Some automatic data type conversions may not be possible, and an error (CTDBRET_CANTCONVERT or CTDBRET_INVTYPE) will occur. This will happen, for instance, if the method CTRecord.SetFieldAsDate() is used to store data in a field whose type is CT_BOOL.

The table below presents the valid automatic conversions for the fields and the GetFieldAs...() or SetFieldAs...() methods.

*1 - These are the FairCom DB API field types. The old c-tree Plus field types are also valid and may be used. For new projects, the best approach is to use the new field type convention presented in this table, since this is the naming convention for the FairCom DB SQL field types.

*2 - The value being set must be equal to either “TRUE” or “FALSE” (case insensitive), otherwise the function will fail with CTDBRET_CANTCONVERT (4042).

For every row in the table above, there is a highlighted column. This column represents the natural function that should be used to update that particular field. As discussed, though, any other function (column) marked with an “X” can be used, and the appropriate conversions will be performed.

Null Field Support

The FairCom DB API .NET record manager API implements the underlying support for null fields, but does not enforce it. If a table was created with null field support, the record manager will set or clear the null bit flag for fields, but there is no check to see if null fields are allowed or not.

When CTRecord.Clear() clears a record buffer, all fields are marked as null fields. As you update the field contents by calling SetFieldAs...() methods, the null field for the respective field is cleared indicating that the field contains data.

A specific field can be cleared, and its null field flag set, by calling the CTRecord.ClearField() method. Call CTRecord.IsNullField() to check if a field null flag is set.

// if a field 0 is not null, clear the field
if (!ARecord.IsNullField(0))
{
   ARecord.ClearField(0);
}

The FairCom DB API null flag controls the NOT NULL property of a column. Setting this to column has no effect on individual record null values: it is NOT enforced at the FairCom DB API layer. This attribute only applies to the FairCom DB SQL layer for constraint on values. It is useful to set this flag in c-tree data files before SQL import such that the property is maintained.

Field Defined Size

Call CTRecord.GetFieldSize() to retrieve the field's defined size. The field's defined size is the size of the field declared at the time the table was created.

Field Actual Length

Call CTRecord.GetFieldLength() to retrieve the field's actual size. For variable length fields such as FIELD_TYPE.VARCHAR and FIELD_TYPE.VARBINARY, the actual size in a record may vary from the defined size.

// check if field 0 actual size is different from defined size
if (ARecord.GetFieldSize(0) != ARecord.GetFieldLength(0))
{
   Console.Write("Field 0 defined and actual sizes are different\n");
}

Field Offset in Record Buffer

A field offset address in the record buffer can also be retrieved by calling CTRecord.GetFieldOffset(). The offsets of fields in the fixed portion of the record never change, while the offsets of fields in the variable portion of a record may change to accommodate the different sizes of variable length fields.

Check if Field is Fixed or Variable Length

Call the CTRecord.IsVariableField() method to check if a field is variable length or not. CTRecord.IsVariableField() returns true if the field is variable length.

// check if field 0 is variable length
if (ARecord.IsVariableField(0))
{
   Console.Write("Field 0 is variable length\n");
}
else
{
   Console.Write("Field 0 is fixed length\n");
}

Using Field Names

Most CTRecord class methods that operate on fields have overloaded methods that will accept a field number or a field name as a parameter to indicate which field to operate on.

Clearing the Record Buffer

Call CTRecord.Clear() to clear the record buffer. The record buffer is cleared, the full bit flag for all fields are cleared, the new record flag is set to false, the edited record flag is cleared, and the record offset is set to zero.

// clear the record buffer
CTRecord ARecord = new CTRecord(ATable);
try
{
   ARecord.Clear();
}
catch (CTException err)
{
   Console.Write("Record clear failed with error {0}\n", err.GetErrorCode());
}

Clearing a Field

A field can be cleared, and its null field flag set, by calling CTRecord.ClearField() method. Call CTRecord.IsNullField() to check if a field null flag is set.

// if a field 0 is not null, clear the field
if (!ARecord.IsNullField(0))
{
   try
{
      ARecord.ClearField(0);
   }
   catch (CTException err)
   {
      Console.Write("Clear field failed with error {0}\n", err.GetErrorCode());
   }
}

Adding New Records

To add a new record to a table, you need to perform the following actions:

1. Clear the record buffer
2. Populate the fields by calling the CTRecord.SetFieldValue() method (or the deprecated CTRecord.SetFieldAs...() methods).
3. Add the record by calling CTRecord.Write()

// clear the record buffer
ARecord.Clear();

// populate the record buffer
ARecord.SetFieldAsSigned(0, 1000);
ARecord.SetFieldAsString(1, "Joe Smith");

// write the record
try
{
   ARecord.Write();
}
catch (CTException err)
{
   Console.Write("Add record failed with error {0}\n", err.GetErrorCode());
}

A record is added to a table if the new record flag and edited flag are both set. CTRecord.Clear() sets the new record flag, while one of the CTRecord.SetFieldAs...() functions will set the edited record flag.

If you clear a record and then, without populating the record buffer with data, make a call to CTRecord.Write(), no error code will be returned but no data record will be written to disk. This is because new record flag is set but the edited record flag is cleared.

If you want to write a cleared or blank record to disk, you must clear the record buffer, set the edited record flag by calling ctdbSetEditedRecord(), and then write the record by calling CTRecord.Write().

// clear the record buffer
ARecord.Clear();

// set the edited record flag
ARecord.SetEdited(true);

// write the record
try
{
   ARecord.Write();
}
catch (CTException err)
{
   Console.Write("Write record failed with error {0}\n", err.GetErrorCode());
}

Updating Existing Records

To update an existing record, you need to perform the following actions:

1. Read the record from disk by calling one of the CTRecord.First(), CTRecord.Last(), CTRecord.Next(), CTRecord.Prev(), CTRecord.SeekRecord(), CTRecord.Find() or CTRecord.FindTarget() methods.
2. Update one or more fields using SetFieldValue() or one of the deprecated CTRecord.SetFieldAs...() methods.
3. Write the record by calling CTRecord.Write().

// update the first record
ARecord.First();

// change the first field
ARecord.SetFieldAsString(0, "Joe Smith");

// write the record
try
{
   ARecord.Write();
}
catch (CTException err)
{
   Console.Write("Update record failed with error {0}\n", err.GetErrorCode());
}

A record is updated when the new record flag is cleared and the edited record flag is set. The new record flag is cleared when a record is read from disk and it indicates that this is an existing record.

If you read a record from disk, and without updating the record data, call CTRecord.Write(), no data record is written to disk, since the edited record flag is cleared.

A record update can be forced by setting the record edited flag with a call to CTRecord.SetEdited() method.

// force update of first record
ARecord.First();

// set the edited flag
ARecord.SetEdited(true);

// write the record
try
{
   ARecord.Write();
}
catch (CTException err)
{
   Console.Write("Update record failed with error {0}\n", err.GetErrorCode());
}

A record can be duplicated by reading it from disk, setting the edited record flag by calling CTRecord.SetEdited() setting the new record flag by calling CTRecord.SetNew(), and calling CTRecord.Write() to write it to disk. Please note that a record can only be duplicated if the table has no indexes with unique keys.

// duplicate the first record
ARecord.First();

// set the edited flag
ARecord.SetEdited(true);

// set the new record flag
ARecord.SetNew(true);

// write the record
try
{
   ARecord.Write();
}
catch (CTException err)
{
   Console.Write("Write record failed with error {0}\n", err.GetErrorCode());
}

Deleting Records

To delete a record, you need to perform the following actions:

1. Read the record from disk by calling one of the CTRecord.First(), CTRecord.Last(), CTRecord.Next(), CTRecord.Prev(), CTRecord.SeekRecord(), CTRecord.Find() or CTRecord.FindTarget() methods.
2. Delete the record by calling CTRecord.Delete():

// delete the first record
ARecord.First();

// delete the record
try
{
   ARecord.Delete();
}
catch (CTException err)
{
   Console.Write("Delete record failed with error {0}\n", err.GetErrorCode());
}

Record Properties

New Record Flag

The new record flag indicates if a record has been cleared and not written to disk yet. The record manager uses this flag to decide if a record should be added or updated.

The new record flag is set when the record handle is allocated or when CTRecord.Clear() clears the record. The new record flag is cleared when a record is read from disk by calling one of the CTRecord.First(), CTRecord.Last(), CTRecord.Next(), CTRecord.Prev(), CTRecord.SeekRecord(), CTRecord.Find() or CTRecord.FindTarget() methods.

Use CTRecord.SetNew() to set the new record flag or CTRecord:IsNew() to check if a record is new or not.

Edited Record Flag

A record is only written to disk if the edited record flag is set. This flag is set when the record buffer is modified with a call to one of the deprecated CTRecord.SetFieldAs...() methods or by calling CTRecord.SetEdited() method. Use CTRecord.IsEdited() to check if a record has been edited or not.

Record Offset

This property holds the current record offset of a record. If a record is cleared, the record offset property is zero. All records in a table, even the first record, will have a record offset value greater than zero.

You can retrieve the record offset value by calling the CTRecord.GetRecordPos() method. You can set the record offset property and load the record data at the offset by calling CTRecord.SeekRecord().

Record Count

Use CTRecord.GetRecordCount() to retrieve the total number of records in a table. This is a read only property.

// check if table is empty
if (ARecord.GetRecordCount() > 0)
{
   Console.Write("Table is not empty\n");
}

Record ROWID

Use CTRecord.GetRowid() method to retrieve the ROWID value for the current record. This is a read only property.

// retrieve the first record rowid
long rowid = ARecord.GetRowid();

Record Locking

A record lock can be acquired by manually performing the following actions:

1. Read a record from disk by calling one of the CTRecord.First(), CTRecord.Last(), CTRecord.Next(), CTRecord.Prev(), CTRecord.SeekRecord(), CTRecord.Find(), or CTRecord.FindTarget() methods.
2. Lock the record by calling CTRecord.LockRecord().

Session-wide locks are better suited to data integrity than manual locks because it locks records as they are read from disk. A manual record lock can only be applied after the record has been read, manual locks leave a small window of opportunity for the record be modified or deleted by another thread before the record lock is acquired.

Release a record lock by calling CTRecord.UnlockRecord() or by calling CTRecord.LockRecord() with LOCK_MODE.FREE_LOCK mode.

Check if a Record is Locked

Call CTRecord.GetRecordLock() to retrieve the current lock mode acquired for the current record. If the record is not locked, CTRecord.GetRecordLock() returns LOCK_MODE.FREE_LOCK. If the current record is locked, CTRecord.GetRecordLock() returns the record lock mode.

// check if record is locked
if (ARecord.GetRecordLock() != LOCK_MODE.FREE_LOCK)
{
   Console.Write("The record is locked\n");
}

The following record modes are returned by CTRecord.GetRecordLock() and are defined in the LOCK_MODE enum:

Demoting Record Locks

If a record has acquired a write lock, it is possible to change or demote the write lock to a read lock if the record has not been updated. Use the CTRecord.LockRecord() method to demote a record with a write lock to a read lock.

// demote a write lock to a read lock
if (ARecord.GetRecordLock() == LOCK_MODE.WRITE_LOCK)
{
   try
{
   ARecord.LockRecord(LOCK_MODE.READ_LOCK);
   }
   catch (CTException err)
   {
      Console.Write("Demote lock failed with error {0}\n", err.GetErrorCode());
   }
}