SQLite support in Appmethod
Go Up to Developing Database Applications
Overview
Appmethod provides support for SQLite databases. Within applications, users can establish connections to SQLite databases by using the FireDAC framework.
Column affinity mapping
For dbExpress, the SQLite column affinities are mapped as follows:
| Type name | Description |
|---|---|
| SQLITE_TEXT | BlobType with a subtype of WideMemoSubType.<br\> If the type declaration contains parenthesis (to specify precision, for example VARCHAR(14)), SQLITE_TEXT is mapped to WideStringType. |
| SQLITE_INTEGER | Int64Type |
| SQLITE_FLOAT | DoubleType |
| SQLITE_BLOB | BlobType with a subtype of BinarySubType |
| SQLITE_NULL | BlobType with a subtype of WideMemoSubType |
FireDAC uses the SQLite columns affinity:
- For SELECT expression items.
- For SELECT column items. In this case, the SQLite engine is compiled with SQLITE_ENABLE_COLUMN_METADATA defined (by default, this is True for Windows, and False for other platforms).
For SELECT column items, see the following table
| Type name | Description |
|---|---|
| ROWID | _ROWID_ | OID | dtInt64, Attrs = [ caSearchable, caAllowNull, caROWID ] |
| BIT | BOOL | BOOLEAN | LOGICAL | YESNO | dtBoolean |
| TINYINT | SHORTINT | INT8 [UNSIGNED] | dtSByte / dtByte |
| BYTE | UINT8 | dtByte |
| SMALLINT | INT16 [UNSIGNED] | dtInt16 / dtUInt16 |
| WORD | UINT16 | YEAR | dtUInt16 |
| MEDIUMINT | INTEGER | INT | INT32 [UNSIGNED] | dtInt32 / dtUInt32 |
| LONGWORD | UINT32 | dtUInt32 |
| BIGINT | INT64 | COUNTER | AUTOINCREMENT | IDENTITY [UNSIGNED] | dtInt64 / dtUInt64 |
| LONGLONGWORD | UINT64 | dtUInt64 |
| REAL | FLOAT | DOUBLE | dtDouble |
| SINGLE [PRECISION] [(P, S)] | dtSingle / dtBCD / dtFmtBCD |
| DECIMAL | DEC | NUMERIC | NUMBER [UNSIGNED] [(P, S)] | dtSByte / dtInt16 / dtInt32 / dtInt64 |
| MONEY | SMALLMONEY | CURRENCY | FINANCIAL [(P, S)] | dtCurrency |
| DATE | SMALLDATE | dtDate |
| DATETIME | SMALLDATETIME | dtDateTime |
| TIMESTAMP | dtDateTimeStamp |
| TIME | dtTime |
| CHAR | CHARACTER [(L)] | dtAnsiString, Len = L, Attrs = [ caFixedLen ] |
| VARCHAR | VARCHAR2 | TYNITEXT | CHARACTER VARYING | CHAR VARYING [(L)] | dtAnsiString, Len = L |
| NCHAR | NATIONAL CHAR | NATIONAL CHARACTER [(L)] | dtWideString, Len = L, Attrs = [ caFixedLen ] |
| NVARCHAR | NVARCHAR2 | NATIONAL CHAR VARYING | STRING [(L)] | dtWideString, Len = L |
| RAW | TYNIBLOB | VARBINARY | BINARY | BINARY VARYING [(L)] | dtByteString, Len = L |
| BLOB | MEDIUMBLOB | IMAGE | LONGBLOB | LONG BINARY | LONG RAW | LONGVARBINARY | GENERAL | OLEOBJECT | TINYBLOB | dtBlob |
| MEDIUMTEXT | LONGTEXT | CLOB | MEMO | NOTE | LONG | LONG TEXT | LONGCHAR | LONGVARCHAR | TINYTEXT | dtMemo |
| TEXT | NTEXT | WTEXT | NCLOB | NMEMO | LONG NTEXT | LONG WTEXT | NATIONAL TEXT | LONGWCHAR | LONGWVARCHAR | HTML | dtWideMemo |
| XMLDATA | XMLTYPE | XML | dtXML |
| GUID | UNIQUEIDENTIFIER | dtGUID |
| Other data types | dtWideString |
Compatibility
Sqlite3.dll support for Windows:
- FireDAC: 3.8.3.1 or previous versions.
- dbExpress: 3.7.8 or previous versions.
Platform portability:
| Win32 | Win64 | OS X | iOS | Android | |
|---|---|---|---|---|---|
|
SQLite Connection |
+ |
+ |
+ |
+ |
+ |
See Also
Examples
Samples
- FireDAC SQLite sample