Connect to SQLite Database (FireDAC) - RAD Studio

Connect to SQLite database (FireDAC) - RAD Studio

1 of 6

http://docwiki.embarcadero.com/RADStudio/XE5/en/Connect_to_SQLite...

Show: Delphi C++ Display Preferences

Connect to SQLite database (FireDAC) From RAD Studio Go Up to Database Connectivity (FireDAC) This topic describes how to connect to SQLite database files.

Contents 1 Supported Versions 2 Windows Client Software 3 Linux Client Software 4 Mac OS X and iOS Client Software 5 Driver Linkage 6 Connection Definition Parameters 7 Usage Cases 8 See Also

Supported Versions The FireDAC native driver supports SQLite database version 3.0 and later. For a detailed discussion on SQLite usage in FireDAC for a Delphi application, read the "Using SQLite with FireDAC" article.

Windows Client Software FireDAC supports two SQLite library linking modes: Static linking -- the x86 sqlite3_x86.obj or x64 sqlite3_x64.obj client library is statically linked into the application. FireDAC provides sqlite3_Xxx.obj v 3.7.15. That is the default mode, no additional files or actions are required. Note that the SQLite database encryption is supported only for static linking. Dynamic linking -- the x86 or x64 SQLITE3.DLL client library must be available in order to open a SQLite database. The recommended SQLITE3.DLL versions are 3.7.7.1 or later. That is the default mode for non-Windows platforms. Note that for C++Builder x64 mode the dynamic linking is used. You can download: the latest x86 DLL version from here (http://www.sqlite.org/download.html) , Chapter "Precompiled Binaries For Windows", item "This is a DLL" and place it in a folder listed in your PATH environment variable (for example, System32 folder) or in your application EXE folder. FireDAC Installer installs SQLITE3.DLL v 3.7.15 in your windows\system32 folder. the x64 DLL version from here (http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)

12/23/2013 4:26 PM


2 of 6


as "sqlite-netFx40-binary-x64-xxxxx.zip". Extract to a folder, copy SQLite.Interop.DLL into SQLITE3.DLL, then place it as above. FireDAC Installer installs x64 SQLITE3.DLL v 3.7.15 in your windows\system32 folder. To choose the linking mode, in $(BDS)\source\data\firedac\FireDAC.inc: define FireDAC_SQLITE_STATIC for static linking. undefine FireDAC_SQLITE_STATIC for dynamic linking. If the SQLite client library has not been installed properly, an exception is raised when you try to connect: [FireDAC][Phys][SQLite]-314. Cannot load vendor library [SQLITE3.DLL]. The specified module could not be found. Check [SQLITE3.DLL], which is located in one of the PATH directories or in the applic ation EXE directory.

Linux Client Software FireDAC on Linux supports only dynamic linking and requires: the libsqlite3.so x86 or x64 engine. To install on Linux use the commands: sudo apt-get update sudo apt-get install sqlite3 libsqlite3-dev

Mac OS X and iOS Client Software FireDAC on Mac OS X and iOS supports only dynamic linking and requires: the libsqlite3.dylib x86 engine. It comes pre-installed on Mac OS X and iOS. Note that the default Mac OS X and iOS libsqlite3.dylib are compiled with limited column metadata capabilities (SQLITE_ENABLE_COLUMN_METADATA is undefined). As a result, FireDAC may fail to detect column auto-incremental mode and optionality.

Driver Linkage To link the driver: drop a TFDPhysSQLiteDriverLink component from the "FireDAC Links" palette page or include the FireDAC.Phys.SQLite unit in an uses clause.

Connection Definition Parameters To connect to a SQLite database, most applications require you to specify DriverID and Database. DriverID=SQLite

12/23/2013 4:26 PM


3 of 6

Parameter Database


Description

Example value c:\MyApp\db.sdb $(temp)\db.sdb

A path to a database. Use ':memory:' or an empty string to create and connect to an empty in-memory database. A path can include path variables. OpenMode

ReadOnly A mode to open a database: CreateUTF8 -- open a database to read or write. If the database does not exist, it will be created with the UTF8 default encoding (the default value for pre-Delphi 2009). CreateUTF16 -- open a database to read or write. If the database does not exist, it will be created with the UTF16 default encoding (the default value for Delphi 2009 and higher). ReadWrite -- open a database to read or write. If the database does not exist, an exception is raised. ReadOnly -- open a database to read only. If the database does not exist, an exception is raised.

Encrypt

Password

Specifies a default encryption mode for a database. The mode may be overridden with an optional password prefix. If it is not specified, then the mode specified by this parameter is used. Otherwise, aes-256 is used. For more details, read this (http://www.da-soft.com/blogs/anydac-sqlite-driverencryption.html) . Specifies a password for an encrypted database. The value may have the following form: [ aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 | aes-ecb-128 | aes-ecb-192 | aes-ecb-256 :] The optional prefix controls the cipher algorithm to be used. The default value is an empty string, which means the unencrypted mode.

aes-256:12345 qwe12345qwe

NewPassword Specifies a new password for a database and performs an encryption operation: To encrypt an unencrypted database, specify the non-empty NewPassword and empty Password; To decrypt an encrypted database, specify the empty NewPassword and non-empty Password; To change an encrypted database password, specify the non-empty NewPassword and non-empty Password. BusyTimeout

Sets an "ms" milliseconds to sleep when a table is locked and 5000 UpdateOptions.LockWait is set to True. Zero means do not wait. The default value is 10000.

12/23/2013 4:26 PM


4 of 6

CacheSize SharedCache LockingMode


Changes the maximum number of database disk pages that SQLite 10000 holds in memory at once. Each page uses about 1.5K of memory. The default value is 10000. Enables or disables the SQLite shared cache feature. For more False details, read this (http://www.sqlite.org/sharedcache.html) . The default value is True. Exclusive Sets the database connection locking-mode. The value is one of the following: Normal -- This mode gives multiuser access to database files. Exclusive -- This mode gives maximum performance. The default value is Exclusive, because it allows you to get maximum read/write speed for single user applications.

Synchronous

Off Sets the database connection synchronization mode of the in-memory cache with database files. The value is one of the following: Full -- Synchronizes at every critical moment. Normal -- As above, but less often. Off -- Gives maximum performance. This is the default value.

ForeignKeys

Off Enables foreign key usage for the database connection, when the application uses SQLite v 3.6.19 or later. The value is one of the following: On -- Foreign keys in a session are enabled. This is the default value. Off -- Foreign keys in a session are disabled.

StringFormat

Unicode Defines how to represent String values: Choose -- represent as ftString / ftWideString / ftMemo / ftWideMemo, depending on the declared data type name (default); Unicode -- always represent as ftWideString / ftWideMemo; ANSI -- always represent as ftString / ftMemo.

GUIDFormat

Binary Defines how to store GUID values: String -- stores GUID as a character string value (default). Binary -- stores GUID as a binary string value.

12/23/2013 4:26 PM


5 of 6


DateTimeFormat

Binary Defines how to store date and time values: String -- stores date and time as a character string value, using the YYYY-MM-DD and HH:MM:SS.XXX format (default). Binary -- stores date and time as a real number, which is a Julian date. DateTime -- stores date and time as a real number, which is a TDateTime value.

Extensions

MyExt.dll;FullTS.dll Enables, disables, or specifies the SQLite engine extensions to load: True -- enables extensions. False -- disables extensions (default). Otherwise, use a list of extensions to load in the form [=][;...].

SQLiteAdvanced Additional SQLite database connection options. For details, see the Pragma statements (http://www.sqlite.org/pragma.html) supported by SQLite. MetaDefCatalog Default database name. The Design time code excludes the catalog name from the object name if it is equal to MetaDefCatalog. Setting MetaDefCatalog does not change the current database in the SQLite session. The default value is 'MAIN'.

auto_vacuum = 1;page_size = 4096;temp_store = FILE MyDB

Usage Cases Connect to a local database in exclusive mode: DriverID=SQLite Database=$(FDHOME)\DB\Data\ADDemo.sdb

Connect to a shared database (it is not recommended to store SQLite databases on WinNT shared folders for multi user read-write access): DriverID=SQLite Database=\\srv\mydb.sqlite LockingMode=Normal Synchronous=Normal

Encrypt an unencrypted database: DriverID=SQLite Database=$(FDHOME)\DB\Data\ADDemo.sdb NewPassword=aes-256:123qwe

Open an encrypted database:

12/23/2013 4:26 PM


6 of 6


DriverID=SQLite Database=c:\temp\test.db Password=123qwe

Connect to an in-memory database: DriverID=SQLite Database=:memory:

Connect to a database file in the Documents folder on the iOS device or emulator in exclusive mode: DriverID=SQLite Database=$(DOC)/test.sdb

See Also Common Connection Parameters FAQs How to configure FireDAC Drivers How to manage FireDAC Connection Definitions Mobile Tutorial: Using FireDAC in Mobile Applications (iOS and Android) Retrieved from "http://docwiki.embarcadero.com/RADStudio/XE5/e /index.php?title=Connect_to_SQLite_database_(FireDAC)&oldid=210485" Category: FireDAC This page was last modified on 25 October 2013, at 17:09. Help Feedback

12/23/2013 4:26 PM

Connect to SQLite Database (FireDAC) - RAD Studio

Recommend Documents