SQLite COM database
The ALP Run-time library in Active Local Pages 1.2 and later includes a
component named SQLite COM. This is a fully functional SQL database engine based
on the SQLite free source code. The database
engine and the programming interface to it is implemented in a single component
and thus it is very easy to re-distribute with any application that uses it.
A SQLite COM databases is stored in single file - all the database objects
are packed in that file, there are no indices or other side files you must care
about when moving the database. The database file format is one for all the
platforms supported by the component and the other the SQLite implementations -
there is no need of conversion for instance.
Over the standard SQLite features SQLite COM adds also some features useful
in Windows environment. Most of them are related to the database interface, but
there are also some implemented as SQL functions - the OLE Date/time formatting
and arithmetic support.
The SQLite COM requires no installation, no persistent configuration, nor
depends on ADO, OLEDB, MS Jet/MS Access or any other 3-d party software. It is
implemented over standard OS features available in all the Windows OS versions.
This makes it perfect for any application that aims to avoid any complications
related to the OS and its components versions.
Along with the typical database applications the fact that a single database
file contains all the database objects opens unusual opportunities such as using
a database as document file or exchange media. Even applications based on
another database software my benefit of using SQLite COM as a mediator between
them and other programs or as a way to construct tear-of applications exposing
scraps of the main database without need of persistent connection to it.
SQLite COM also supports in-memory database functionality. You just pass an
empty string to the Open method and this will open an in-memory database.
Through SQL statements you can connect other databases and transfer data from/to
the in-memory database.
The SQL support is fairly advanced. If compared to other lightweight
databases SQLite can be positioned at the same level as MS Jet/MS Access for
example. However, the week and the strong sides of SQLite and Jet are different
- for example one of the strongest sides of SQLite is the support for triggers
and full-scale non-nested transactions, but it does not support parameters from
the external statements in nested SQL statements. For the most usages the
differences would not matter, but it is recommended to take a look at the
SQLites SQL language support reference in order to plan your work better.
The database interface supplied by SQLite COM is non-recordset based, but
this is also a plus. Developers who use frequently the ADO Recordset's GetRows
method will find it familiar, but much more convenient. The query results are
presented in collections that can be addressed by numerical and named indices
(i.e. field names) and enumerated using traditional For .. Next cycles. The
field type information can be exposed in various ways which depends on a single
property you can set to instruct the database return the information in the best
form for the particular case. The data returned in result of a query execution
can be converted to appropriate script manageable types automatically or
presented as strings depending on your preference.
Listing a table looks like this:
<% Set r = g_db.Execute("SELECT * FROM Clients ORDER BY Name")
For I = 1 To r.Count %>
<%= r(I)("Name") %>, <%= r(I)("Email") %><BR>
<% Next %>
Paging the results/Extraction of partial query results is simple too. This is
the above code changed to return only the first 10 records.
<% Set r = g_db.Execute("SELECT * FROM
Clients ORDER BY Name",1,10)
For I = 1 To r.Count %>
<%= r(I)("Name") %>, <%=
<% Next %>
The SQLite databases are type-less internally. Still, the database
distinguishes numeric from text values in order to perform operations. You are
free to specify whatever type names you want for the table columns which allows
the applications implement databases that resemble other database engines
closely. SQLite COM maintains a set of dynamically customizable type names that
are treated as numeric types. This allows you configure which type names will
imply numeric values by default and thus instruct the database convert them
automatically when they are returned by queries. The default settings are
selected in manner that will suit most applications.
The database performance is one of the best in its class. Provided you use
transactions when inserting/updating SQLite outruns many popular databases -
such as MySQL for example.
One connection - one thread. Single SQLite COM object implies one connection
to the database, but you can open more SQLite COM connections to the same
database - all the control is yours and is absolutely clear. The records locking
is intelligent enough to allow several concurrent connections perform
substantial tasks thus allowing you construct whatever database infrastructure
you may need in your applications.
See the SQLite COM reference in NDL.
See the database samples installed by ALP.
See the SQLite COM