LiteDAC

Connecting To SQLite Database

This tutorial describes how to connect to SQLite Database.

Requirements

LiteDAC supports 2 modes of working with SQLite database:

  1. Direct Mode - no additional client libraries required;
  2. Using sqlite3.dll(.o,.dylib) client library - requires a corresponding client library on a particular PC or device. The library must be located either in the application folder or in the folder specified in the environmental variables.

General information

SQLite works with 3 database types: real DB file, temp DB file, and DB in memory.

To use different DB types, a corresponding value must be set in the TLiteConnection.Database proeprty. To work with a DB file,you have to specify the full, relative or UNC path to the DB file. To work with a database in memory, the ':memory:' value must be set. To work with a temporary database, the property value should be empty.

To switch modes of work with the database, the TLiteConnectionOptions.Direct property is used. The property is set to False by default (client library mode). To use the Direct mode, the proeprty must be set to True.

When connecting to a database file, if it doesn't exist, the file can be created automatically. For this, the TLiteConnectionOptions.ForceCreateDatabase property must be set to True. Its default value is False. On an attempt to connect to a non-existing DB file with disabled ForceCreateDatabase, an error message will be displayed saying that such a file doesn't exist.

Note: this option doesn't apply to working with a temp database and an in-memory database.

Note: when working with client library, its bitness must match the application bitness, i.e. a 32-bit application can work with a 32-bit library version only, and 64-bit - only with 64-bit.

Creating connection

Design time creation

The following assumes that you have IDE running, and you are currently focused on a form designer.

  1. Open the Component palette and find the TLiteConnection component in the LiteDAC category.
  2. Double-click the component.

Note: a new object appears on the form. If this is the first time you create TLiteConnection in this application, it is named LiteConnection1.

After you have done these steps, you should set up the newly created LiteConnection1 component. You can do this in two ways:

Using TLiteConnection Editor

  1. Double-click on the LiteConnection1 object.
  2. In the Database edit box specify the database name (for example, test.db3). If Database is not specified, the temporary database is used.
  3. If a client library with suppport for encryption or Direct Mode is used, then you can specify the key to the database file in the Encryption Key edit box.
  4. If Direct Mode is used, then you can select an encryption algorithm in the Encription Algorithm ComboBox. When using a client library, the standard algorithm built into the library is used.
  5. You can specify a particular SQLite3 library in the Client Library edit box.
  6. To enable Direct Mode, the Direct CheckBox must be checked.

Using Object Inspector

  1. Click on the LiteConnection1 object and press F11 to focus on object's properties.
  2. In the Database property specify the database name (for example,test.db3). If Database is not specified, the temporary database is used.
  3. If a client library with suppport for encryption or Direct Mode is used, then you can specify the key to the database file in the EncryptionKey poperty.
  4. If Direct Mode is used, then you can select an encryption algorithm in the Options.EncriptionAlgorithm proeprty. When using a client library, the standard algorithm built into the library is used.
  5. You can specify a particular SQLite3 library in the ClientLibrary property.
  6. To enable Direct Mode, the Options.Direct property must be set to True.

Run time creation

Same operations performed in runtime look as follows:

[Delphi]

var
  LiteConnection: TLiteConnection;

begin
  LiteConnection := TLiteConnection.Create(nil);
  try
    LiteConnection.Database := 'test.db3';
    LiteConnection.Options.ForceCreateDatabase := True; 
    LiteConnection.Options.Direct := True; 
    LiteConnection.EncryptionKey := '123';
    LiteConnection.Options.EncryptionAlgorithm := leAES256;
    LiteConnection.LoginPrompt := False; //to prevent showing of the connection dialog
    LiteConnection.Connect;
  finally
    LiteConnection.Free;
  end;
end.

Note: To run this code, you have to add the LiteAccess and LiteCall units to the USES clause of your unit.

[C++Builder]

{
  TLiteConnection* LiteConnection = new TLiteConnection(NULL);
  try
  {
    LiteConnection->Database = "test.db3";
    LiteConnection->Options->ForceCreateDatabase = True; 
    LiteConnection->Options->Direct = True;
    LiteConnection->EncryptionKey = '123';
    LiteConnection->Options->EncryptionAlgorithm = leAES256;
    LiteConnection->LoginPrompt = False; //to prevent showing of the connection dialog
    LiteConnection->Connect();
  }
  __finally
  {
    LiteConnection->Free();
  }
}

Note: To run this code, you have to include the LiteAccess.hpp and LiteCall.hpp header files to your unit.

And using the ConnectString property:

[Delphi]

var
  LiteConnection: TLiteConnection;
begin
  LiteConnection := TLiteConnection.Create(nil);
  try
    LiteConnection.ConnectString := 'Database=test.db3;ForceCreateDatabase=True;Direct=True;EncryptionAlgorithm=AES 256;EncryptionKey=123;LoginPrompt=False';
    LiteConnection.Connect;
  finally
    LiteConnection.Free;
  end;
end.

Note: To run this code, you have to add the LiteAccess and LiteCall units to the USES clause of your unit.

[C++Builder]

{
  TLiteConnection* LiteConnection = new TLiteConnection(NULL);
  try
  {
    LiteConnection->ConnectString = "Database=test.db3;ForceCreateDatabase=True;Direct=True;EncryptionAlgorithm=AES 256;EncryptionKey=123;LoginPrompt=False";
    LiteConnection->Connect();
  }
  __finally
  {
    LiteConnection->Free();
  }
}

Note: To run this code, you have to include the LiteAccess.hpp and LiteCall.hpp header files to your unit.

Opening connection

As you can see above, opening a connection at run-time is as simple as calling of the Connect method:

[Delphi]

LiteConnection.Connect;

[C++Builder]

LiteConnection->Connect();

Another way to open a connection at run-time is to set the Connected property to True:

[Delphi]

LiteConnection.Connected := True;

[C++Builder]

LiteConnection->Connected = True;

This way can be used at design-time as well. Of course, LiteConnection1 must have valid connection options assigned earlier. When you call Connect, LiteDAC tries to find the database file and connect to it. If any problem occurs, it raises an exception with brief explanation on what is wrong. If no problem is encountered, LiteDAC tries to establish the connection. Finally, when connection is established, the Connect method returns and the Connected property is changed to True.

Closing connection

To close a connection, call its Disconnect method, or set its Connected property to False:

[Delphi]

LiteConnection.Close;

[C++Builder]

LiteConnection.Close();

, or
[Delphi]

LiteConnection.Connected := False;

[C++Builder]

LiteConnection.Connected = False;

Modifying connection

You can modify connection by changing properties of TLiteConnection object. Keep in mind that while some of the properties can be altered freely, most of them close connection when the new value is assigned. For example, if you change Database property, it gets closed immediately, and you have to reopen it manually.

© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback