SQLite3 COM database offers much more than SQLite COM - not
only as database engine but also as interface. Take a look at
the detailed comparison where you
can learn quickly not only about the differences, but also about
the features of the both if you are new to these components.
The SQLite3 engine works over a local file from the file
system. The entire database is kept in a single file and the
engine is responsible to maintain its internal structure and
consistency. The file is automatically opened for reading and/or
writing - if both are possible the both modes are enabled if the
file is on a read-only media the engine will report errors
whenever an attempt is made to change the database. The database
engine is contained in the COM DLL and requires no
configuration - supplying the database file name to the Open
method is everything it needs to know!
The textual data is kept internally UTF8 encoded and thus
there is no need of UNICODE to ANSI and reverse conversions -
all the texts are fully preserved and processed correctly. The
other parts of the application may require some character code
page tuning, but not the database interface - all the UNICODE
characters passed to and from the engine are fully preserved. This
means also that the data in the database file is binary the same
on all the platforms. For instance you can copy a database
created on a PC to a Pocket PC device and work with it without
need of any conversion.
A specific feature of SQLite is that it is type-less. This
can be perceived as a plus or as a minus depending on the
programming habits you have and the applications you are going
to build. However, most of the modern programming environments
can even benefit from this feature. In most cases the
applications need to take care for themselves to validate the
data entered into the database and even if the database engine
supports wise validation methods the interface requirements
often make them inconvenient. On the other hand applications
that may benefit of relaxed data typing cannot do that
effectively if the
database requires each column to be of the specified type.
SQLite is designed to deal smartly when the fields participate
in expressions and will always return appropriate results by
doing whatever conversions are required.
Data types and auto typing mode
SQLite3 COM stores the values into the database in one of the
following types: TEXT, INTEGER, REAL (double) and
This obsoletes the auto-typing interface from SQLite COM.
However it is retained in order to preserve better compatibility
with the other component. Thus the applications can be migrated
with less effort, the new applications should not use the
SQLite3 COM returns the following type names for the fields
in result of Execute: TEXT, INTEGER, REAL,
BLOB or NULL. Regardless of the specified column
type (in CREATE TABLE) only these 5 types are indicated when
data is extracted using a SELECT query and the type returned
indicates the actual type of the value in the particular field.
See some more info in the Execute result
When writing data to the db (INSERT and UPDATE queries for
example) you can still use the Sprintf
functions from StringUtilities
component to construct queries with parameters. However, SQLite3
COM offers a more convenient way - see VExecute, CExecute and
AExecute. They pass parameters to the executed queries without
need of help from an external objects. The passed values are
converted to the closest database type and substituted in the
SQLite engine supports transactions (not nested)
without limit to the number of statements in them. This allows
you to perform safe database updates and commit the changes only
if everything is ok.
SQLite supports triggers on tables and views (with
some limitations - see Unsupported
SQL features). This allows you build databases that care for
themselves internally or/and create views that act as pseudo
tables and simplify certain frequent operations.
The extended specific SQL commands (Pragma-s)
allow you to control the engine behavior at run-time and also
allow you fetch detailed information about the database objects
and use it to construct utility code.
The SQLite3 engine is faster or at least equal to the
most of the other small-scale and embedded databases (such as MS
Jet/MS Access, MySQL and so on). SQLite3 COM can be used instead
of MS Access with great success. It lacks no significant
features and even offers some advantages (see the Session
parameters for instance). If indexed correctly it is faster.
Note that MS Access offers automatic creation of indices over
many columns, in SQLite3 you need to specify most of the indices
explicitly. To reach maximum performance you need to create as
many indices as possible, still the downside is the database
size. Being not-so-automatic SQLite3 leaves these decisions to
you. The creation of an index is a couple of mouse clicks in the
DB manager, so you just need to take a look over the tables you
created and decide which columns deserve indices.
More information about the engine itself you can find on the
official SQLite site.
Where are the recordsets?
Do you really need them?
The fetched data is returned by SQLite3 COM as collection of rows
which are collection of named values (the field values) in turn
(see Execute, VExecute,
CExecute and AExecute). This is even
easier to work with for it allows direct indexing of each value
in each row by index or name. It combines features similar
to what both GetRows and ADODB.Recordset will give you in ADO.
When you need to page the output (in an ASP page for example)
you can use the optional parameters of the Execute methods to
restrict the result to the part you really need. Furthermore the
SQLite SELECT statement supports LIMIT and OFFSET modifiers
which enable you to limit the results also from within the
database. The one or the other technique can be better suited
for each particular case
data the recordsets are most often limited to one table only
which is just some more source code - to write down all these
rst("X1").Value = something etc. Many developers
prefer executing insert/update SQL statements directly as they
happen to require less code. What really recordset gives you is
the type conversions, but with the
VExecute, CExecute and AExecute this is done for you
automatically. This brings the way you work with the database
closer to the SQL nature and helps you keep the "SQL
spirit" and minimize the code.
conclusion, SQLite3 COM makes the work with the database closer
to the SQL language in contrast to ADO centered programming
which attempts to hide at least part of the SQL used for the actual
operations performed over the DB. If this is good or bad depends
on the point of view, but ultimately SQLite3 COM will require
less code than equivalent ADO based solution. The fact that you
see all the SQL involved keeps the developer closer to the
relational nature of the database and helps building better
code. If you are relatively new to the databases consider these
points: 1) a recordset needs implicitly generated SQL to perform
certain operations - if it will be optimal for a given database
engine is a big IF providing the fact that ADO is engine
independent. 2) The recordsets resemble some of the features of
the arrays, but they are not arrays and in the most cases they
use a lot of internal resources to cache the data in order to
provide this functionality. How much data they cache is at best
difficult to control and often impossible.
With the more SQL suited interface
SQLite3 COM you know exactly what are you doing, exactly
how many values are kept in memory and exactly how long they
will be there.