Database field type: what changed
Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.
| Old | New | Line | |
|---|---|---|---|
| 1 | 1 | ## Overview | |
| 2 | 2 | ||
| 3 | - | ||
| 3 | + | FCL-DB database fields can have different data types. In datasets, a subset of these types is available. What types are supported differ per dataset type. | |
| 4 | 4 | ||
| 5 | 5 | ## Types | |
| 6 | 6 | ||
| 7 | - | ||
| 8 | - | ||
| 9 | - | ||
| 10 | - | ||
| 11 | - | ||
| 12 | - | ||
| 13 | - | ||
| 14 | - | ||
| 15 | - | ||
| 16 | - | ||
| 17 | - | ||
| 18 | - | ||
| 19 | - | ||
| 20 | - | ||
| 21 | - | ||
| 22 | - | ||
| 23 | - | ||
| 24 | - | ||
| 25 | - | ||
| 26 | - | ||
| 27 | - | ||
| 28 | - | ||
| 29 | - | ||
| 30 | - | ||
| 31 | - | ||
| 32 | - | ||
| 33 | - | ||
| 34 | - | ||
| 35 | - | ||
| 36 | - | ||
| 37 | - | ||
| 38 | - | ||
| 39 | - | ||
| 40 | - | ||
| 41 | - | ||
| 42 | - | ||
| 43 | - | ||
| 44 | - | ||
| 45 | - | ||
| 46 | - | ||
| 47 | - | ||
| 48 | - | ||
| 49 | - | ||
| 50 | - | ||
| 7 | + | Currently, the following field types are defined. See [FCL field type documentation](<http://lazarus-ccr.sourceforge.net/docs/fcl/db/tfieldtype.html>). To do: add assignment information (e.g. can you use .AsString) and additional information below. | |
| 51 | 8 | ||
| 9 | + | Field type | Type | Size | Description | |
| 10 | + | ---|---|---|--- | |
| 11 | + | ftADT | | | Abstract data type (user defined types created on the server); not supported yet (?). | |
| 12 | + | ftArray | | | Represents an Interbase 6 / Firebird array datatype (array of simple data-types like varchar or integer). However, SQLDB does not support the array datatype currently. | |
| 13 | + | ftAutoInc | | | An auto-incrementing integer field. | |
| 14 | + | ftBCD | packed record | | A binary coded decimal floating point value. | |
| 15 | + | ftBlob | | | A binary large object (BLOB), meant to store arbitrary binary data. | |
| 16 | + | ftBoolean | Boolean | 1 byte | A boolean value (yes/no). | |
| 17 | + | ftBytes | | | Presumably a fixed number of bytes stored as-is. Needs to have its Size property set to work. | |
| 18 | + | ftCurrency | | | A format to precisely store currency values. | |
| 19 | + | ftCursor | | | | |
| 20 | + | ftDataSet | | | Presumably meant to store an entire dataset (possibly to implement master/detail table). | |
| 21 | + | ftDate | | | A date without time information. | |
| 22 | + | ftDateTime | | | A date and time information. | |
| 23 | + | ftDBaseOle | | | Presumably meant to store OLE objects in a DBase database. Needs to have its Size property set to work. | |
| 24 | + | ftFMTBcd | | | A form of Binary Coded Decimal (BCD) number field. Needs to have its Size property set to work. | |
| 25 | + | ftFixedChar | | | A fixed width character field, similar to a Pascal shortstring. Needs to have its Size property set to work. | |
| 26 | + | ftFixedWideChar | | | A fixed width multibyte character field. Needs to have its Size property set to work. | |
| 27 | + | ftFloat | Double | 8 bytes | A floating point numeric type. | |
| 28 | + | ftFmtMemo | | | Needs to have its Size property set to work. | |
| 29 | + | ftGraphic | | | Needs to have its Size property set to work. | |
| 30 | + | ftGuid | | | A field used to store a GUID (Globally Unique Identifier). With the current code, this field needs to have its Size property set to 38. | |
| 31 | + | ftIDispatch | | | | |
| 32 | + | ftInteger | Longint | 4 bytes | An integer field. | |
| 33 | + | ftInterface | | | | |
| 34 | + | ftLargeint | | 8 bytes | A field that contains an integer, stores more bytes than an integer and therefore has a larger range. | |
| 35 | + | ftMemo | | | Stores a variable amount of string/text data; needs no size set. | |
| 36 | + | ftOraBlob | | | Presumably stores Oracle BLOB. | |
| 37 | + | ftOraClob | | | Presumably stores Oracle CLOB: an Oracle data type that can hold up to 4 GB of data [[1]](<http://www.orafaq.com/wiki/CLOB>). | |
| 38 | + | ftParadoxOle | | | Presumably meant to store OLE objects in a Paradox database. | |
| 39 | + | ftReference | | | | |
| 40 | + | ftSmallint | | 2 bytes, signed | An integer type field with less bytes than ftInteger. | |
| 41 | + | ftString | | | Stores string data; needs to have its Size property set to the maximum number of characters possible in that field. | |
| 42 | + | ftTime | | | Stores time-only data. | |
| 43 | + | ftTimeStamp | | | Stores date/time data. Probably equivalent to ftDateTime. | |
| 44 | + | ftTypedBinary | | | Some kind of BLOB-like field? | |
| 45 | + | ftUnknown | | | | |
| 46 | + | ftVarBytes | | | Presumably a variant with byte/binary data? | |
| 47 | + | ftVariant | | | Presumably meant to store variant data. | |
| 48 | + | ftWideMemo | | | An ftMemo with widestring (UTF-16) characters. | |
| 49 | + | ftWideString | | | An ftString with widestring (UTF-16) characters. | |
| 50 | + | ftWord | | 2 bytes, unsigned | Presumably stores an integer value. | |
| 51 | + | ||
| 52 | 52 | ## Size, DataSize and Unicode | |
| 53 | 53 | ||
| 54 | - | ||
| 54 | + | Note that for string type fields, Size indicates the number of characters that can be stored. As indicated in [[FPC Unicode support|FPC Unicode support#Introduction]], FPC up to and including 2.6 only deals with **ANSI/ASCII** single byte characters; it does not support Unicode/UTF8/UTF16/Unicodestring characters. | |
| 55 | 55 | ||
| 56 | - | ||
| 56 | + | The read-only property DataSize indicates the field size in bytes. | |
| 57 | 57 | ||
| 58 | - | ||
| 58 | + | If you use multibyte characters (e.g. UTF8 or UTF16/Unicodestring encoded), DataSize and Size do not mean the same thing. If you use only ANSI/ASCII characters, DataSize and Size are effectively the same thing. | |
| 59 | 59 | ||
| 60 | 60 | ## Defining types in your dataset | |
| 61 | 61 | ||
| 62 | - | ||
| 62 | + | _Todo: write me. Explain various ways of doing things (TFieldDef, TFields) and which dataset supports which methods._ | |
| 63 | 63 | ||
| 64 | 64 | ## Assigning and retrieving values | |
| 65 | 65 | ||
| 66 | - | ||
| 67 | - | ||
| 68 | - | ||
| 69 | - | ||
| 70 | - | ||
| 71 | - | ||
| 72 | - | ||
| 73 | - | ||
| 74 | - | ||
| 66 | + | Once you have the fields in your dataset defined, you can assign and retrieve data like this - suppose you have a dataset called FTestDataset: | |
| 75 | 67 | ``` | |
| 68 | + | FTestDataset.Open; //Open for viewing/editing/inserting | |
| 69 | + | FTestDataset.Append; | |
| 70 | + | FTestDataset.FieldByName('YourFieldName').AsString := 'This is my field data'; //Suppose field YourFieldName is a memo field | |
| 71 | + | FTestDataset.Post; //"Commit"/save changes in the record to the dataset. | |
| 72 | + | Writeln('YourFieldName has data:' + FTestDataset.FieldByName('YourFieldName').AsString); | |
| 73 | + | { Retrieve the field value of the current record. Because we haven't moved the record, we should get what we just entered } | |
| 74 | + | ``` | |
| 76 | 75 | ||
| 77 | - | ||
| 76 | + | For text/memo fields, use the AsString method. For date/time fields, use the AsDateTime method. For binary fields, I suppose you can use the AsString method - _but there must be another way, too_ | |
| 78 | 77 | ||
| 79 | 78 | ## See also | |
| 80 | 79 | ||
| 81 | - | ||
| 80 | + | * [[Databases]] | |
| 82 | 81 | ||
| 83 | 82 | --- | |
| 84 | - | ||
| 83 | + | *From the Free Pascal wiki, page "Database field type", as the Internet Archive kept it on 2025-03-23, last changed on 2024-09-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).* |