Sign in Join
Wiki copytemporary

Database field type: what changed

Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.

Title: Database field type

OldNewLine
1+## Overview
2+
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+
5+## Types
6+
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.
8+
9+| Field type | 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 | A binary coded decimal floating point value. |
15+| ftBlob | A binary large object (BLOB), meant to store arbitrary binary data. |
16+| ftBoolean | 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 | 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 | An integer field. |
33+| ftInterface | |
34+| ftLargeint | 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 | 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 | Presumably stores an integer value. |
51+
52+## Size, DataSize and Unicode
53+
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+
56+The read-only property DataSize indicates the field size in bytes.
57+
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+
60+## Defining types in your dataset
61+
62+*Todo: write me. Explain various ways of doing things (TFieldDef, TFields) and which dataset supports which methods.*
63+
64+## Assigning and retrieving values
65+
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:
67+
68+``` pascal
69+ FTestDataset.Open; //Open for viewing/editing/inserting
70+ FTestDataset.Append;
71+ FTestDataset.FieldByName('YourFieldName').AsString := 'This is my field data'; //Suppose field YourFieldName is a memo field
72+ FTestDataset.Post; //"Commit"/save changes in the record to the dataset.
73+ Writeln('YourFieldName has data:' + FTestDataset.FieldByName('YourFieldName').AsString);
74+ { Retrieve the field value of the current record. Because we haven't moved the record, we should get what we just entered }
75+```
76+
77+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+
79+## See also
80+
81+- [[Databases]]
82+
83+---
84+*From the Free Pascal wiki, page "Database field type", as it stood on 2024-03-14 after a change by Bart, out of the wiki's dump of 2024-05-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).*

Back to history