| Feature |
SQLite COM |
SQLite3 COM |
Comments |
| Zero configuration |
Yes |
Yes |
Only the file name of the database file must be supplied. Any other parameters are optional and can be specified at run-time. |
| COM interface |
integrated |
integrated |
The database engine and the COM interface are in single DLL. Note that AXPack1 core is still needed because the output from queries is packed in AXPack1 objects. This may sound as added footprint, but in the reality the benefit of using common objects for database communication and other tasks enables the applications to perform very complex tasks with little code. |
| In-memory database |
Yes |
Yes |
Opening with empty file name opens a full featured in-memory SQL database.. |
| SQL support |
SQL 92 |
SQL 92 |
Not all the features are supported. See the SQL reference for the both components for details. |
| Sub-queries |
static |
full support |
The sub-queries are one of the most powerful mechanisms in SQL. Static means that the sub-query is executed once and used for each iteration of the outer query. The full support allows the sub-query to refer fields from the outer query. |
| Query parameters |
No support, You need a StringUtilities object to overcome this. |
Named and by position. |
In SQLite COM you can parameterize queries only by using the string formatting functionality supplied by the StringUtilities object.
In SQLite3 COM you have methods like VExecute, CExecute and AExecute. They allow you put parameters in the executed queries (for example using ? or $<name>) and pass values for them as trailing arguments (VExecute), as collection (CExecute) or as array (AExecute). You can pass any kind of value including binary (blob). |
| Session parameters |
None |
Scalar and object parameters supported. |
The SQLite3 COM's Parameters collection enables the application to set named parameters (values or objects) that can be used in any query executed with this database object. This includes not only directly executed queries, but also views and triggers. The session parameters are accessible through the Parameter database function. Any method or property on object parameters can be called using the CallObject database function. |
| Parameterized views |
No |
Yes |
See session parameters. |
| OLE Date/Time support |
Yes |
Yes - Extended |
For Windows applications it is more convenient to use OLE date/time values which are actually regular double precision floating point numbers. This date/time format is better than most other date/time formats (including the date/time format inherited from the SQL standard). The OLE date/time is not only directly convertible from/to double values, but also precise enough for time stamping. This makes it the recommended date/time format for SQLite COM and SQLite3 COM. |
| Internal database types |
numeric and text |
integer, text, real, blob |
In SQLite3 COM the internal database value types are enough to fit the needs of any application. |
| Column types |
smart |
smart |
SQLite (2 and 3) allow values of any of the supported types to be put in any column. Thus the declared column type is actually just a suggestion. It is up to the application to decide if it will stick to it or will allow mixed types to be put in the column. Smart type conversion is applied in the SQL expressions and the application can determine the actual type of the value of each particular field in the returned result. |
| Internal COM to database interface |
textual |
direct |
In SQLite COM the data is automatically or explicitly converted to text before sending/receiving it to/from the database engine (internally).
In SQLite3 COM the data is converted from/to the closest internal database value type which makes the interface 3 to 5 times faster than in SQLite COM. |
| Triggers |
Yes |
Yes |
|
| Transactions |
Yes |
Yes |
The transactions cannot be nested. |
| Database storage |
Single file (or in-memory) SQLite2 UTF-8 format |
Single file (or in-memory) SQLite3 UTF-8 format. |
The file format is machine independent. For example the Windows CE versions of the SQLite COM/SQLite3 COM work with the same files as the desktop versions. The format is the standard SQLite format and is compatible also with non SQLite COM implementations based on SQLite (for example UNIX applications using SQLite in their own way) |
| Database manager |
ALP based |
ALP based |
Database manager is available for the both components. It is written using ALP. It can be obtained separately or as part of other products that include it (if you have installed some of our products you may already have it - check before downloading separately). |
| Availability |
Windows 95/98/ME
Windows NT4/2k/XP/2003/Vista and above
Windows CE 3.0/CE.NET and above (including Pocket PC and Smartphone) |
Windows 95/98/ME
Windows NT4/2k/XP/2003/Vista and above
Windows CE 3.0/CE.NET and above (including Pocket PC and Smartphone) |
|
| Database locking |
Yes
(Windows CE - No) |
Yes (page level)
(Windows CE - No) |
On desktops multiple applications can work with the same database file at the same time. On Windows CE devices only one application may have full access to the database while the others can only read from it at the same time. |
| Extending the database with custom functions |
No |
Yes |
See CallObject. |
