SQLite support in Appmethod

From Appmethod Topics
Jump to: navigation, search

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

dtByte / dtUInt16 / dtUInt32 / dtUInt64

dtBCD / dtFmtBCD

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