 |  LoginProducts  SalesSupportDownloadsAbout |
| Home » Technical Support » DBISAM Technical Support » Product Manuals » DBISAM Version 4 Manual for RAD Studio XE (Delphi) » Using DBISAM » Data Types and NULL Support |
Data Types and NULL SupportIntroduction
DBISAM supports the most common data types available for the Delphi and C++ development products as well as the SQL language. Below you will find a listing of the data types with a brief description, their Delphi and C++ equivalent TFieldType type and TField object, and their SQL data type.
The TFieldType type is also used with the TDBISAMFieldDef, TDBISAMParam, and TDBISAMFunctionParam objects.
NULL Support
The rules for NULL support in DBISAM are as follows:
• If a field has not been assigned a value and was not defined as having a default value in the table structure, it is NULL.
• As soon as a field has been assigned a value it is not considered NULL anymore. String, FixedChar, GUID, Blob, Memo, and Graphic fields are an exception this rule. When you assign a NULL value (empty string) to a String, FixedChar, or GUID field the field will be set to NULL. When the contents of a Blob, Memo, or Graphic field are empty, i.e. the length of the data is 0, the field will be set to NULL.
• If the Clear method of a TField object is called the field will be set to NULL.
• NULL values are treated as separate, distinct values when used as an index key. For example, let's say that you have a primary index comprised of one Integer field. If you had a field value of 0 for this Integer field in one record and a NULL value for this Integer field in another record, DBISAM will not report a key violation error. This is a very important point and should be considered when designing your tables. As a general rule of thumb, you should always provide values for fields that are part of the primary index.
• Any SQL or filter expression involving a NULL value and a non-NULL value will result in a NULL result. For example:
The exception to this rule is when concatenating a string value with a NULL. In this case the NULL value is treated like an empty string. For example:
String, FixedChar, or GUID field types in DBISAM treat empty strings as equivalent to NULL, and vice-versa, in any filter or SQL expressions.
NULLs with SQL and Filter Operators
The following pseudo-expressions demonstrate the rules regarding NULLs (not empty strings) and the various SQL and filter operators:
The rules are slightly different for String, FixedChar, and GUID expressions due to the fact that DBISAM treats empty strings as equivalent to NULL, but also as a valid non-NULL empty string. The following pseudo-expressions demonstrate the rules regarding empty strings and the various SQL and filter operators:
The IN and LIKE operators use the same rules as the equivalency (=) operator. The IN operator behaves as if there are a series of equivalency tests joined together by OR operators.
DBISAM supports the most common data types available for the Delphi and C++ development products as well as the SQL language. Below you will find a listing of the data types with a brief description, their Delphi and C++ equivalent TFieldType type and TField object, and their SQL data type.
The TFieldType type is also used with the TDBISAMFieldDef, TDBISAMParam, and TDBISAMFunctionParam objects.| Data Type | Description |
| String | String fields are fixed in length and can store up to 512 characters in a single field. Trailing blank spaces are automatically trimmed from any strings entered into string fields. Internally, String fields are stored as a NULL-terminated string. String fields can be indexed using normal indexes as well as full text indexing. The equivalent Delphi and C++ TFieldType is ftString, the TField object used for String fields is the TStringField object, and the equivalent SQL data type is the VARCHAR type. The SQL VARCHAR data type is specified as: VARCHAR(<number of characters>) |
| FixedChar | FixedChar fields are basically the same as string fields with the exception that trailing blank spaces are not automatically removed from any strings entered into them. The equivalent Delphi and C++ TFieldType is also ftString, but the TStringField object that represents a FixedChar field will have its FixedChar property set to True. The equivalent SQL data type is either the CHAR or CHARACTER type. The SQL CHAR and CHARACTER data types are specified as: CHAR(<number of characters>) or CHARACTER(<number of characters>) |
| GUID | GUID fields are basically the same as string fields with the exception that they are fixed at 38 bytes in length and are always used to store the string representation of a GUID value. The equivalent Delphi and C++ TFieldType is ftGuid, the TField object used for GUID fields is the TGuidField object, and the equivalent SQL data type is GUID. |
| Bytes | Bytes fields are fixed in length and can store up to 512 bytes in a single field. Bytes fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftBytes, the TField object used for Bytes fields is the TBytesField object, and the equivalent SQL data type is BYTES, VARBYTES, BINARY, or VARBINARY. The SQL BYTES, VARBYTES, BINARY, OR VARBINARY data type is specified as: BYTES(<number of characters>) |
| Blob | Blob fields are variable in length and may contain up to 2 gigabytes of data. The data stored in Blob fields is not typed or interpreted in any fashion. Blob fields are stored in a blocked fashion internally in the physical BLOB file that is part of a logical DBISAM table. Blob fields cannot be indexed in any fashion. The equivalent Delphi and C++ TFieldType is ftBlob, the TField object used for Blob fields is the TBlobField object, and the equivalent SQL data type is either the BLOB or LONGVARBINARY type. |
| Memo | Memo fields are variable in length and may contain up to 2 gigabytes of data minus a NULL terminator. The data stored in Memo fields is always text. Memo fields are stored in a blocked fashion internally in the physical BLOB file that is part of a logical DBISAM table. Memo fields cannot be indexed using normal indexes, but can be indexed using full text indexing. The equivalent Delphi and C++ TFieldType is ftMemo, the TField object used for Memo fields is the TMemoField object, and the equivalent SQL data type is either the MEMO or LONGVARCHAR type. |
| Graphic | Graphic fields are variable in length and may contain up to 2 gigabytes of data. The data stored in Graphic fields is not typed or interpreted in any fashion, however it is identified in a special way to allow for Delphi and C++ to perform special type-assignments with bitmap and other graphic objects. Graphic fields are stored in a blocked fashion internally in the physical BLOB file that is part of a logical DBISAM table. Graphic fields cannot be indexed in any fashion. The equivalent Delphi and C++ TFieldType is ftGraphic, the TField object used for Graphic fields is the TGraphicField object, and the equivalent SQL data type is the GRAPHIC type. |
| Date | Date fields contain dates only. Internally, Date fields are stored as a 32-bit integer representing cumulative days. Date fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftDate, the TField object used for Date fields is the TDateField object, and the equivalent SQL data type is DATE. |
| Time | Time fields contain times only. Internally, Time fields are stored as a 32-bit integer representing cumulative milliseconds. Time fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftTime, the TField object used for Time fields is the TTimeField object, and the equivalent SQL data type is TIME. |
| TimeStamp | TimeStamp fields contain both a date and a time. Internally, TimeStamp fields are stored as a 64-bit floating-point number (a double) representing cumulative milliseconds. TimeStamp fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftDateTime, the TField object used for TimeStamp fields is the TDateTimeField object, and the equivalent SQL data type is TIMESTAMP. |
| Boolean | Boolean fields contain logical True/False values. Internally, Boolean fields are stored as a 16-bit integer. Boolean fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftBoolean, the TField object used for Boolean fields is the TBooleanField object, and the equivalent SQL data type is BOOLEAN, BOOL, or BIT (compatibility syntax, BOOLEAN or BOOL is preferred). |
| SmallInt | SmallInt fields contain 16-bit, signed, integers and are stored internally as such. SmallInt fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftSmallInt, the TField object used for SmallInt fields is the TSmallIntField object, and the equivalent SQL data type is SMALLINT. |
| Word | Word fields contain 16-bit, unsigned, integers and are stored internally as such. Word fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftWord, the TField object used for Word fields is the TWordField object, and the equivalent SQL data type is WORD. |
| Integer | Integer fields contain 32-bit, signed, integers and are stored internally as such. Integer fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftInteger, the TField object used for Integer fields is the TIntegerField object, and the equivalent SQL data type is INTEGER or INT. |
| AutoInc | AutoInc fields contain 32-bit, signed, integers and are stored internally as such. AutoInc fields are always editable and you may have more than one AutoInc field per record, with each field incrementing independently. AutoInc fields will increment if you are appending or inserting a record and a value is not specified for the field (field is NULL) when the Post operation occurs, and will leave any existing value alone if one is already specified. AutoInc fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftAutoInc, the TField object used for AutoInc fields is the TAutoIncField object, and the equivalent SQL data type is AUTOINC. |
| LargeInt | LargeInt fields contain 64-bit, signed, integers and are stored internally as such. LargeInt fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftLargeInt, the TField object used for LargeInt fields is the TLargeIntField object, and the equivalent SQL data type is LARGEINT. |
| Float | Float fields contain 64-bit floating-point numbers (doubles) and are stored internally as such. Float fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftFloat, the TField object used for Float fields is the TFloatField object, and the equivalent SQL data type is FLOAT. |
| Currency | Currency fields are the same as Float fields except they are identified in a special way to allow for Delphi and C++ to format their values as monetary values when displayed as strings. The equivalent Delphi and C++ TFieldType is ftCurrency, the TField object used for Currency fields is the TCurrencyField object, and the equivalent SQL data type is MONEY. Don't confuse the Currency field type with the Currency data type found in Delphi and C++. The Currency field type is essentially still a floating-point number and is not always good for storing exact monetary values, whereas the Currency data type is a fixed-point data type that minimizes rounding errors in monetary calculations. If you wish to have accurate financial figures that use up to 4 decimal places stored in DBISAM tables then you should use the BCD data type described next. |
| BCD | BCD fields contain a 34-byte TBcd type and are stored internally as such. DBISAM always uses a maximum precision of 20 significant digits with BCD numbers, and the maximum scale is 4 decimal places. BCD fields can be indexed using normal indexes only. The equivalent Delphi and C++ TFieldType is ftBCD, the TField object used for BCD fields is the TBCDField object, and the equivalent SQL data type is NUMERIC OR DECIMAL. The SQL NUMERIC or DECIMAL data type is specified as: NUMERIC(<precision>,<scale>) |
NULL Support
The rules for NULL support in DBISAM are as follows:
• If a field has not been assigned a value and was not defined as having a default value in the table structure, it is NULL.
• As soon as a field has been assigned a value it is not considered NULL anymore. String, FixedChar, GUID, Blob, Memo, and Graphic fields are an exception this rule. When you assign a NULL value (empty string) to a String, FixedChar, or GUID field the field will be set to NULL. When the contents of a Blob, Memo, or Graphic field are empty, i.e. the length of the data is 0, the field will be set to NULL.
• If the Clear method of a TField object is called the field will be set to NULL.
• NULL values are treated as separate, distinct values when used as an index key. For example, let's say that you have a primary index comprised of one Integer field. If you had a field value of 0 for this Integer field in one record and a NULL value for this Integer field in another record, DBISAM will not report a key violation error. This is a very important point and should be considered when designing your tables. As a general rule of thumb, you should always provide values for fields that are part of the primary index.
• Any SQL or filter expression involving a NULL value and a non-NULL value will result in a NULL result. For example:
100.52 * NULL = NULL 10 + 20 + NULL = NULL
The exception to this rule is when concatenating a string value with a NULL. In this case the NULL value is treated like an empty string. For example:
'Last Name is ' + NULL = 'Last Name is '
String, FixedChar, or GUID field types in DBISAM treat empty strings as equivalent to NULL, and vice-versa, in any filter or SQL expressions.NULLs with SQL and Filter Operators
The following pseudo-expressions demonstrate the rules regarding NULLs (not empty strings) and the various SQL and filter operators:
| Expression | Result |
| Column = NULL | Returns True if the column is NULL, False, if not |
| Column <> NULL | Returns True if the column is not NULL, False if it is |
| Column >= NULL | Returns True if the column is NULL, False if not |
| Column <= NULL | Returns True if the column is NULL, False if not |
| Column > NULL | Returns False |
| Column < NULL | Returns False |
| Column BETWEEN NULL AND NULL | Returns True if the column is NULL, False if not |
| Column BETWEEN NULL AND <non-null value> | Returns False |
| Column BETWEEN <non-null value> AND NULL | Returns False |
The rules are slightly different for String, FixedChar, and GUID expressions due to the fact that DBISAM treats empty strings as equivalent to NULL, but also as a valid non-NULL empty string. The following pseudo-expressions demonstrate the rules regarding empty strings and the various SQL and filter operators:
| Expression | Result |
| Column = '' | Returns True if the column is NULL or equal to an empty string, False, if not |
| Column <> '' | Returns True if the column is not NULL or not equal to an empty string, False if it is |
| Column >= '' | Returns True if the column is NULL, equal to an empty string, or greater than an empty string, False if not |
| Column <= '' | Returns True if the column is NULL or equal to an empty string, False if not |
| Column > '' | Returns True if the column is greater than an empty string |
| Column < '' | Returns False |
| Column BETWEEN '' AND '' | Returns True if the column is NULL or equal to an empty string, False if not |
| Column BETWEEN '' AND <non-empty string> | Returns True if the column is NULL, equal to an empty string, or greater than an empty string, False if not |
| Column BETWEEN <non-empty string> AND '' | Returns False |
The IN and LIKE operators use the same rules as the equivalency (=) operator. The IN operator behaves as if there are a series of equivalency tests joined together by OR operators.
More Support Options
This web page was last updated on Thursday, November 16, 2023 at 10:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ?  E-mail us at info@elevatesoft.com |
