Watch, Follow, &
Connect with Us

Stephen Blas

Categories:



Archives:



RAD Studio XE3 and SQLite

RAD Studio XE3 introduces a new dbExpress driver which enables connectivity to SQLite 3.x in Win32, Win64, and OSX applications.  The driver is written in Delphi and the source code is provided.  In this blog post, I’ll list a few things you need to do to get set up to use this driver and also explain the purpose of a couple of the connection properties.

First off on Windows,  you will need to download the SQLite client from http://www.sqlite.org/download.html and verify that sqlite3.dll can be found by your application.  Once you have done this you can easily build FireMonkey and VCL applications that connect to SQLite using dbExpress.  Drop a TSQLConnection onto your form, set the Driver property to Sqlite and away you go.  If you choose to create a console application instead, make sure that you use the Data.DbxSqlite unit so that the driver is registered in the driver registry.  If you are creating an application for OSX, ensure that libsqlite3.dylib is present on the machine you are deploying to.

The SQLite driver supports a connection property called ColumnMetaDataSupported which can be set to True or False.  This property is in place because sqlite3.dll (or libsqlite3.dylib) can be compiled with or without the SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol being defined.  This symbol determines whether certain metadata APIs are available.  On Windows, the symbol is defined in the dll they provide for you so we have set the default value of ColumnMetaDataSupported to True.  On OSX, this symbol is not defined so we default to False.  However, as SQLite provides source, you could choose to compile the SQLite client library yourself either way.

FailIfMissing is another property that the SQLite driver supports.  If this is set to False and the database you specify is not found, a new database will be created for you.  When this property is set to True, an exception will be raised when the database is not found instead.

That’s all I’ll cover in this blog post.  Connecting to SQLite should be pretty straightforward.

Posted by sablas on October 2nd, 2012 under SQLite, XE3, dbExpress |



12 Responses to “RAD Studio XE3 and SQLite”

  1. Vsevolod Leonov Says:

    >> verify that sqlite3.dll can be found by your application.
    application (which can be not compiled yet) or Delphi IDE?

  2. sablas Says:

    Both. The IDE if you would like live data at design time. Your application if you decide to try and run it. Adding the directory containing the library to your path would be the quickest way to accomplish this on your development machine.

  3. Aleksandr Bozhko Says:

    How I can use connection properties in Delphi?

  4. sablas Says:

    You can add name\value pairs to the Params collection on your TSQLConnection.

  5. Mehmed Ali Caliskan Says:

    Hello Stephen

    ColumnMetaDataSupported causes the driver code to miss the fullname (alias) of the field because it sets the original name to the ColumName. Normally programmers expect the FullName there. To be bale to get the fullname I set this parameter to False, bu this time miss the tableName of the column.

    So for me a solution may be like this:

    Select MyTable.Id as MyTableID from MayTable

    When ColumnMetaDataSupported = TRUE
    FieldName = Id (OriginalName)
    TableName = MyTable
    DisplayName (or FullName) = MyTableId

    When ColumnMetaDataSupported = FALSE
    FieldName = MyTableId
    TableName = ”
    DipslayName = MyTableId

    If the ColumnMetaDataSupported is set t

  6. Omar Says:

    Hi,

    1. Why is this driver written in Delphi and what are the advantages coming with this aspect different from the other drivers?

    2. Do you intend to provide a driver what will allow as - as users, to have a pull of connections in one single applications?

  7. Peter BELL Says:

    Hi Stephen,

    I’ve followed your instructions and also those provided by David I in his October 9th You Tube video. I created a FireMonkey HD application usingn C++ Builer Professional (with hotfix 3 installed).

    All works well until I attempt to commit the update, then I receive an exception "… raised exception class TDBXError with message ‘database is locked’".

    I’ve also tried the same with a VCL project. The error message still indicates ‘database is locked’.

    I’ve looked through all the properties I know how to find, and nothing leaps out at me as to a wrong setting.

    At the moment I’m quite puzled as to why this is occuring and wondered if you can shed any light on this.

    I’m quite new to using C++ builder for database access, so please excuse me if this is a basic error.

    Cheers,
    Peter Bell.

  8. sablas Says:

    Thank you Mehmed, I will look into it.

    Omar, writing the driver in Delphi allows you to easily link the driver into the application for all platforms. If you would like a connection pool, the DBXPool delegate is available.

    Peter, if the database has been opened for reading by one process, a 2nd process cannot write to it. This could happen if you set the Active property to true in the IDE on a TSQLQuery or TSQLDataSet and then attempt to run your application and modify data.

  9. Jan Coolen Says:

    "…and verify that sqlite3.dll can be found by your application…"

    Sounds reasonable, but no matter where I put it (c:\Windows\System32, application source code dir, application exe dir) Delphi keeps complaining: "Sqlite3.dll not found". So where exactly is it supposed to be or how do I tell Delphi where to find it.

    Thanks for some help (XE3 Pro under Windows 8 Pro)

  10. sablas Says:

    Anywhere in your path should work. If this is on a 64-bit OS and the application is 32-bit, you may need to put it in c:\Windows\SysWOW64 rather than c:\Windows\System32.

  11. Pau Dominguez Says:

    I have tried the ColumnMetaDataSupported=FALSE and i dont get the correct alias field name.

    Select a.*,b.remote_id as REmp_id
    from users a join empresas b on a.empresa_id = b.id
    where a.id

    The users table has a remote_id and get b.remote_id as remote_id_1 instead REmp_id.

  12. Pau Dominguez Says:

    Sorry I have a mistake in the params list. I have copy-pasted and some blank spaces cause this error.

Leave a Comment



Server Response from: BLOGS2