Scalar Types
These are the data types that map directly to C or C++ scalar types. FairCom DB API allows the use of c-tree scalar data types such as:
| TEXT | Declared as 8-bit char. This is the equivalent of C type char. |
| UTEXT | Declared as 8-bit unsigned char. This is the equivalent of C type unsigned char. |
| COUNT | Declared as 16-bit integer. This is the equivalent of C type short. |
| UCOUNT | Declared as 16-bit unsigned integer. This is the equivalent of C type unsigned short. |
| NINT | Declared as the system natural integer. This is equivalent of C type integer. |
| UINT | Declared as the system natural unsigned integer. This is the equivalent of C type unsigned integer. |
| LONG | Declared as 32-bit signed integer. This is the equivalent of C type long for 32-bit CPUs. |
| ULONG | Declared as 32-bit unsigned integer. This is the equivalent of C type unsigned long for 32-bit CPUs. |
FairCom DB API also exposes the following data types, based on c-tree scalar types. These FairCom DB API types also provide a one-to-one relationship with FairCom DB API field types:
| CTBOOL | Declared as an integer, this type holds Boolean values using the predefined Boolean constants YES for true and NO for false. |
| CTSIGNED | Declared as a signed long (32-bit signed integer), this type holds any scalar value: 8-bit signed integer (TEXT), 16-bit signed integer (COUNT), integers (NINT), and 32-bit signed integers (LONG). |
| CTUNSIGNED | Declared as an unsigned long (32-bit unsigned integer), this type holds any scalar value: 8-bit unsigned char (UTEXT), 16-bit unsigned integer (UCOUNT), unsigned integers (UINT), and 32-bit unsigned integers (ULONG). |
| DOUBLE | Declared as C type double. |
| CTFLOAT | Declared as C type double. |
All the types in this category can be manipulated using the normal operators defined for the C and C++ language.
Date Types
CTDATE is declared as an unsigned 32-bit integer. The date is stored as the number of days since March 1st 1700. The CTDATE type was implemented to be compatible with FairCom's r-tree and ODBC products. Hence, if a CTDATE type has a value of 1, it means that the date is March 1st 1700, while a value of 2 means March 2nd 1700 and so on.
Since CTDATE is a 32-bit unsigned integer, numerical operations with dates can be done using standard C operators. For example, to add 10 days to a date use the normal C operation date + 10 (assuming that date is a CTDATE type). To get the difference in days of 2 dates, just subtract one date from another.
FairCom DB API provides methods and functions to allow the user to manipulate the CTDATE type in their applications.
| Function | Operation |
|---|---|
| ctdbDateCheck | Returns CTDBRET_OK if it is a valid date. Month must be a value between 1 and 12 and day must be a value between 1 and 31. This function checks for dates such as February 29th for leap years and for months that have 30 and 31 days. |
| ctdbDatePack | Packs a date as year, month and day and store the result in pDate. The date is checked and ctdbDatePack() returns CTDBRET_OK if the date was packed correctly. |
| ctdbDateUnpack | Update a CTDATE value into year, month and day. |
| ctdbDateToString | Convert a packed CTDATE value into string. DateType() gives the format of the date in string form. |
| ctdbStringToDate | Convert a date in string format to a packed CTDATE value. DateType gives the format of the date in string form. |
| ctdbGetDay | Retrieve the day component of a packed date type. |
| ctdbGetMonth | Retrieve the month component of a packed date type. |
| ctdbGetYear | Retrieve the year component of a packed date type. |
| ctdbIsLeapYear | Indicate if the year component of the packed date type is a leap year. |
| ctdbDayOfWeek | Retrieve the day of the week for a given packed date type. Sunday is 0, Monday is 1, Tuesday is 2, and so on. |
| ctdbGetDefDateType | Retrieve the default date format type for this session. The record manager uses the session default date format type when converting date fields into string values and vice-versa. |
| ctdbSetDefDateType | Set the default date format type for this session. When a session handle is allocated, the default date format type is set to CTDATE_MDCY: month followed by day and year |
| ctdbCurrentDate | Retrieve the current system date and store it in CTDATE packed format. |
Date Type Formats
The possible date formats for string conversion are:
| FairCom DB API Symbolic Constant |
FairCom DB API .NET Symbolic Constant |
Description |
|---|---|---|
| CTDATE_MDCY | MDCY_DATE | Date is mm/dd/ccyy |
| CTDATE_MDY | MDY_DATE | Date is mm/dd/yy |
| CTDATE_DMCY | DMCY_DATE | Date is dd/mm/ccyy |
| CTDATE_DMY | DMY_DATE | Date is dd/mm/yy |
| CTDATE_CYMD | CYMD_DATE | Date is ccyymmdd |
| CTDATE_YMD | YMD_DATE | Date is yymmdd |
Time Types
CTTIME is declared as a 32-bit unsigned integer, stored as the number of seconds since midnight. The CTTIME type was implemented to be compatible with FairCom's r-tree and ODBC products.
FairCom DB API provides functions and methods to allow the user to manipulate the CTTIME type in their applications.
| Function | Operation |
|---|---|
| ctdbTimeCheck | Check an unpacked time in hour, minute and second and return CTDBRET_OK if the time is valid. Hour must be a value between 0 and 23, minute and second must be a value between 0 and 59. |
| ctdbTimePack | Pack a time in the form hour, minute and second into a CTTIME form. |
| ctdbTimeUnpack | Unpack a CTTIME time into hour, minute and second. |
| ctdbTimeToString | Convert a packed time value into string format. |
| ctdbTimeMsecToString | Convert a packed CTTIMEMS into a string. |
| ctdbStringToTime | Convert a time in string form into a packed time. TimeType() gives the string form of the time value. |
| ctdbGetHour | Retrieve the hour component of a packed time. The hour return is a 24-hour value (e.g. 11 pm is returned as 23). |
| ctdbGetMinute | Retrieve the minute component of a packed time. |
| ctdbGetSecond | Retrieve the second component of a packed time. |
| ctdbGetDefTimeType | Retrieve the default time format kept by the session. The record manager uses the default time format to convert time fields into string fields and vice-versa. |
| ctdbSetDefTimeType | Set the default time format for the session. The record manager uses the default time format to convert packed time fields into string values, and vice-versa. When a session handle is allocated, the default time format is set to CTTIME_HMP (HH:MM am/pm) |
| ctdbCurrentTime | Retrieve the current system time. |
Time Type Formats
Time Types can be one of the following string time formats:
| FairCom DB API Symbolic Constant |
FairCom DB API .NET Symbolic Constant |
Description |
|---|---|---|
| CTTIME_HMSP | HMSP_TIME | Time is hh:mm:ss am|pm |
| CTTIME_HMP | HMP_TIME | Time is hh:mm am|pm |
| CTTIME_HMS | HMS_TIME | Time is hh:mm:ss (24 hour) |
| CTTIME_HM | HM_TIME | Time is hh:mm (24 hour) |
| CTTIME_MIL | MIL_TIME | Time is hhmm (military) |
| CTTIME_HHMST | Time is hh:mm:ss.ttt (24 hour) |
Millisecond Resolution
In V11 and later, a new base c-tree type, CTTIMEMS, was added to hold a time with millisecond format. CTTIME and CTTIMEMS are not comparable without transformation. CTTIME is a LONG value holding the number of seconds since midnight, CTTIMEMS is a LONG value holding the number of milliseconds since midnight.
- New FairCom DB API functions were added to set and get fields as CTTIMEMS, specifically ctdbGetFieldAsTimeMsec and ctdbSetFieldAsTimeMsec.
- Calling ctdbSetFieldAsTimeMsec against a CT_TIME field would cause a loss of milliseconds. If this attempted, the error CTDBRET_MSEC_NOTSUPPORTED (4147) is returned.
- New functions were added to FairCom DB API time with millisecond manipulation functions: ctdbTimePackMsec and ctdbTimeUnpackMsec.
Searching an index based on CT_TIME is problematic when milliseconds are significant since the index information does not contain milliseconds and, as we do with filter, we cannot automatically add milliseconds before doing the search. SQL will error out when performing index searches on CT_TIME columns.
Note: Millisecond support requires new extended data types in FairCom DB V11 forward.
Several functions were added to FairCom DB API related to TIMESTAMP (CTDATETIME):
CTDATETIME <-> CTTIMEMS conversions:
ctdbDateTimeSetTimeMsec()
ctdbDateTimeGetTimeMsec()
Millisecond DateTime handling:
ctdbDateTimePackMsec()
ctdbDateTimeUnpackMsec()
Timestamps with milliseconds are supported on existing timestamp data with no conversion. A minor behavior change should be noted:
Prior to this release, a query similar to the example shown below would have ignored the milliseconds portion, returning records matching '11/11/2015 12:00:15.000':
SELECT * FROM mytable WHERE mytimestamp = '11/11/2015 12:00:15.998' With the changes described above, the milliseconds are now significant.
Date/Time (Timestamp) Types
CTDATETIME is declared as a double type and stores both the date and time. This type is also called a time stamp. The date component of a CTDATETIME value is stored as the value on the left of the decimal point, while the time component is stored as the value on the right side of the decimal point.
For additional precision, there is also the CTDATETIME100NS data type, which is the same as CTDATETIME, except that it has a precision of 7 digits to the right of the timestamp's decimal point (.1 microseconds). For example: 2026-01-01T00:00:00.1234567Z.
FairCom DB API provides functions and methods to manipulate the CTDATETIME (or CTDATETIME100NS) type.
| Function | Operation |
|---|---|
| ctdbDateTimePack | Pack a date and time into a CTDATETIME value. |
| ctdbDateTimeUnpack | Unpack a CTDATETIME value into year, month, day, hour, minute and second. |
| ctdbDateTimeGetDate | Retrieve the CTDATE component of a CTDATETIME value; |
| ctdbDateTimeGetTime | Retrieve the CTTIME component of a CTDATETIME value. |
| ctdbDateTimeSetDate | Replace the CTDATE component of a CTDATETIME value. |
| ctdbDateTimeSetTime | Replace a CTTIME component of a CTDATETIME value. |
| ctdbDateTimeToString | Convert a CTDATETIME to string. |
| ctdbStringToDateTime | Convert a string to i. |
| ctdbCurrentDateTime | Retrieve the system current date and time. |
| ctdbServerDateTime | Retrieves the current server-side date and time (V11 and later). |
Numeric Types
Numeric types are used to manipulate numeric values that are too large for the scalar types or numeric values representing currency values. FairCom DB API implements the following numeric types:
CTBIGINT
CTBIGINT is a 64-bit signed integer type. Today most C compilers are capable of dealing with 64-bit integers. In the Windows operating system, Borland, Microsoft and Watcom use a __int64 type to represent native 64-bit integers, while in other operating systems such as Unix and Linux, 64-bit integers are represented as long long types.
The following set of functions converts of CTBIGINT into other FairCom DB API types.
| Function | Operation |
|---|---|
| ctdbBigIntToLong | Convert a CTBIGINT value to a long (32 bit signed integer). If the CTBIGINT value is too large for the conversion, ctdbBigIntToLong() return either CTDBRET_OVERFLOW or CTDBRET_UNDERFLOW errors. |
| ctdbLongToBigInt | Convert a long value to CTBIGINT. |
| ctdbBigIntToFloat | Convert a CTBIGINT value to CTFLOAT. |
| ctdbFloatToBigInt | Convert a CTFLOAT value to CTBIGINT. |
| ctdbBigIntToString | Convert a CTBIGINT value to string. |
| ctdbStringToBigInt | Convert a string to CTBIGINT value. |
CTMONEY
CTMONEY represents a currency value in a 32-bit signed integer. The last two decimal digits of the value are used as the decimal part of the value. For example, a currency value of 123.45 is represented with CTMONEY as 12345. A currency value of 1 is represented in CTMONEY as 100.
Since all the operations performed on CTMONEY values are integer operations, this type offers exact currency value capabilities that do not need large values or large precision at excellent performance that is very close to 32-bit integer performance.
FairCom DB API provides the following set of functions to manipulate CTMONEY values, including functions for performing the basic arithmetic operations on CTMONEY values. These set of basic arithmetic operations are especially important in multiplication and division of CTMONEY values. Take for example the multiplication of two values such as 123.45 and 67.89. These values are represented in a CTMONEY type as 12345 and 6789 respectively. An integer multiplication on the values above, would give the result 83810205, which is 838102.05 in CTMONEY representation. This result is clearly wrong since the expected result would be 8381.02. The same principle applies to division operation. By using the CTMONEY API provided by FairCom DB API, the user will be able to operate correctly with CTMONEY values.
| Function | Operation |
|---|---|
| ctdbMoneyToLong | Convert a CTMONEY value to LONG. Only the integer portion of the CTMONEY value is converted to LONG, as the decimal portion of the CTMONEY value is ignored for the conversion. |
| ctdbLongToMoney | Convert LONG to CTMONEY . |
| ctdbMoneyToFloat | Convert CTMONEY value to CTFLOAT. |
| ctdbFloatToMoney | Convert CTFLOAT value to CTMONEY . |
| ctdbMoneyToString | Convert CTMONEY value to string. |
| ctdbStringToMoney | Convert string to CTMONEY value. |
| ctdbMoneyAdd | Add two CTMONEY values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left + right. |
| ctdbMoneySub | Subtract two CTMONEY values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left - right. |
| ctdbMoneyMul | Multiply two CTMONEY values and store the result in pResult. The mathematical equivalent of this function would be pResult = left * right. |
| ctdbMoneyDiv | Divide two CTMONEY values and store the result in pResult. The mathematical equivalent of this function would be pResult = left / right. If the value of right is zero, ctdbMoneyDiv() returns CTDBRET_DIVBYZERO. |
| ctdbMoneyCmp | Compare two CTMONEY values left and right and return zero value if left is equal to right. Return negative value if left is less than right and return a positive value if left is greater than right. |
| ctdbMoneyAbs | Return the absolute value of a CTMONEY value. |
CTCURRENCY
CTCURRENCY represents a currency value in a 64-bit signed integer. The last four decimal digits of the value are used as the decimal part of the value. Example: a currency value of 123.45 is represented with CTCURRENCY type as 1234500. A currency value of 1 is represented in CTCURRENCY as 10000.
Since all the operations performed on CTCURRENCY values are integer operations, this type offers exact currency value capabilities with large value capabilities and good precision at excellent performance that is very close to 64-bit integer performance.
pResult provides the following set of functions to manipulate CTCURRENCY values, including functions for performing the basic arithmetic operations on CTCURRENCY values. This set of basic arithmetic operations are especially important in multiplication and division of CTCURRENCY values. Take for example the multiplication of two values such as 123.45 and 67.89. These values are represented in a CTCURRENCY type as 1234500 and 678900 respectively. An integer multiplication on the values above would yield 838102050000, which is 83810205.0000 in CTCURRENCY representation. This result is clearly wrong since the expected result would be 8381.02. The same principle applies to division operation. By using the CTCURRENCY API provided by pResult , the user will be able to operate correctly with CTCURRENCY values.
| FairCom DB API Function | Operation |
|---|---|
| ctdbMoneyToCurrency | Convert a CTMONEY value to CTCURRENCY value. |
| ctdbCurrencyToMoney | Convert a CTCURRENCY value to CTMONEY value. |
| ctdbCurrencyToLong | Convert a CTCURRENCY value to a LONG value. |
| ctdbLongToCurrency | Convert a LONG value to CTCURRENCY value. |
| ctdbCurrencyToBigInt | Convert a CTCURRENCY value to CTBIGINT value. |
| ctdbBigIntToCurrency | Convert a CTBIGINT value to CTCURRENCY value. |
| ctdbCurrencyToFloat | Convert a CTCURRENCY value to a CTFLOAT value. |
| ctdbFloatToCurrency | Convert a CTFLOAT value to CTCURRENCY value. |
| ctdbCurrencyToString | Convert a CTCURRENCY value to a string. |
| ctdbStringToCurrency | Convert a string to a CTCURRENCY value. |
| ctdbCurrencyAdd | Add two CTCURRENCY values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left + right. |
| ctdbCurrencySub | Subtract two CTCURRENCY values left and right, and store the result in pResult. The mathematical equivalent of this function would be pResult = left - right. |
| ctdbCurrencyMul | Multiply two CTCURRENCY values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left * right. |
| ctdbCurrencyDiv | Divide two CTCURRENCY values left, and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left / right. If the value of right is zero, ctdbCurrencyDiv() returns CTDBRET_DIVBYZERO. |
| ctdbCurrencyCmp | Compare two CTCURRENCY values. If the value of left and right are equal, ctdbCurrencyCmp() return zero. If left is greater than right return a positive value. If the value of left is less than right return a negative value. |
| ctdbCurrencyAbs | Return the absolute value of CTCURRENCY value. |
| ctdbCurrencyRound | Round the CTCURRENCY value to the number of decimal places specified by scale. |
CTNUMBER
CTNUMBER corresponds to a number with a given precision (maximum number of digits) and scale (the number of digits to the right of the decimal point). Numeric values can have maximum values of 32 digits precision and scale of 0 (this is 99999999999999999999999999999999), which can represent very large exact values.
pResult provides the following set of functions to manipulate this very powerful number representation.
| Function | Operation |
|---|---|
| ctdbMoneyToNumber | Convert CTMONEY to CTNUMBER. |
| ctdbNumberToMoney | Convert CTNUMBER to CTMONEY. |
| ctdbNumberToLong | Convert CTNUMBER to LONG. |
| ctdbLongToNumber | Convert LONG to CTNUMBER. |
| ctdbNumberToBigInt | Convert CTNUMBER to CTBIGINT. |
| ctdbBigIntToNumber | Convert CTBIGINT to CTNUMBER. |
| ctdbNumberToCurrency | Convert CTNUMBER to CTCURRENCY. |
| ctdbCurrencyToNumber | Convert CTCURRENCY to CTNUMBER. |
| ctdbNumberToFloat | Convert CTNUMBER to CTFLOAT. |
| ctdbFloatToNumber | Convert CTFLOAT to CTNUMBER. |
| ctdbNumberToString | Convert CTNUMBER to string. |
| ctdbStringToNumber | Convert String to CTNUMBER. |
| ctdbNumberAdd | Add two CTNUMBER values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left + right. |
| ctdbNumberSub | Subtract two CTNUMBER values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left - right. |
| ctdbNumberMul | Multiply two CTNUMBER values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left * right. |
| ctdbNumberDiv | Divide two CTNUMBER values left and right and store the result in pResult. The mathematical equivalent of this function would be pResult = left / right. If the value of right is zero, ctdbNumberDiv() return CTDBRET_DIVBYZERO. |
| ctdbNumberZero | Set the value pointed by pNumber to zero. |
| ctdbIsNumberZero | Return YES if the value pointed by pNumber is zero. |
| ctdbNumberCmp | Compare two CTNUMBERs and return zero if both numbers are equal. Return a negative value if pLeft is less than pRight. Return a positive value if pLeft is greater than pRight. |
| ctdbNumberAbs | Return the absolute value of the number pointed to by pResult. |
| ctdbNumberNegate | Invert the sign of the value pointed to by pSource and place the result in pResult. The mathematical equivalent of this function would be pResult = - pSource. |
| ctdbNumberCopy | Copy the value of the number pointed to by pSource into pDest. In other words, assign the value of pSource into pDest. |
| ctdbNumberRound | Round the value pointed to by num to the number of decimal digits (digits to the right of the decimal point) indicated by scale. |
| ctdbNumberGetDecimal | Given the CTNUMBER data, ctdbNumberGetDecimal() retrieves the number of digits to the left of the decimal point and store it in digit_before and retrieves the number of digits to the right of decimal point and stores it in digit_after. |