SQLite3 COM vs. SQLite COM

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.

Overview

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 BLOB (binary). 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 auto-typing interface.

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 description

When writing data to the db (INSERT and UPDATE queries for example) you can still use the Sprintf or SAPrintf 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 queries.

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 

When writing 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. 

In 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.

References

Component files
Creation (ClassID and ProgID)
Methods and properties
SQL language reference
    - SQL
    - Pragma-s
    - Data types
    - expression syntax
    - standard functions
    - OLE date/time functions
    - Session parameters and functions
 Data types 
Other components that offer useful features for SQLite3 COM developers.
    - StringUtilities.Sprintf and SAPrintf

Component files:

SQLITE3COMUTF8.dll - COM DLL, ALPFrame autorun compatible. It depends on newobjectspack1.dll and must be distributed with it. Both DLL must be registered on the PC/Device or alternatively they must be both configured with ALPFrame for on-the-fly execution.

Creation:

Threading model: Both
Program ID: newObjects.sqlite3.dbutf8
ClassID: {A080B21E-00F3-43f2-984D-1B58AF325946}
Threading model: Free
ProgramID: newObjects.sqlite3.dbutf8.free
ClassID: {B357C242-5C35-4fac-A3A1-2C83118BE550}

Methods and properties

	Open	Opens a database.
	Close	Closes the currently opened database
	Execute	Executes SQL statement(s) and returns the fetched data and other details. The V,C and A variants offer a range of techniques to pass parameters to the executed statement(s). See also the Result collection - the data returned by these methods
	VExecute
	CExecute
	AExecute
	ColectionsBaseIndex	Specifies what base index will be used by the CExecute method when un-named parameters must be substituted in a query.
	Parameters	Enables you to specify and manage the session wide parameters. A session is considered all the operations performed with the same SQLite3 object.
	Interrupt	Interrupts any current actions in progress. Makes sense only if called from a different thread.
	LastError	Holds the text for the last error (if any has been occurred).
	SQLiteVersion	Returns a string representing the SQLite engine version.
	BusyTimeout	Sets busy timeout used if the engine is unable to obtain lock over a database or part of it.
	NoErrorMode	Allows script languages that lack of exception handling to instruct the object to not error when Execute method fails. They can detect if there is an error by checking the LastError property.
	TypeInfoLevel	Sets/Gets the level of type info reported by the Execute method.
	IsOpened	Returns true if the database is opened (i.e. Open method has been called successfuly prior to checking this property).
Obsolete members left for compatibility with SQLite COM The methods and properties listed below are supported by SQLite3 COM, but they have no impact over the way the database works. Wherever they have functionality not related to the database it is preserved the same as in SQLite COM component. Some of the members may be of some use even for new applications that need to keep a list of type names for their own usage, but this remains entirely your own business and will have no impact over the database functionality! The listed obsolete members will be preserved in all the future versions of the component, thus there is no need to get rid of their usage.
	ParseNumeric	Parses numeric value from a string in SQL compatible format regardless of the locale settings (see the description in SQLite COM's documentation).
	StripTypeName	Helper method. Strips the meaningful part of the type name passed as string. (see the description in SQLite COM documentation).
Auto type management members (numeric types list management) See the SQLite COM documentation for details.
	AutoType	No effect in SQLite3 COM. The value is saved but ignored. Autotyping is always on in SQLite3 COM.
	AddNumericType	Adds a type name to the list of the types treated as numeric.
	RemoveNumericType	Removes a numeric type name from the list
	CountNumericTypes	Returns the number of the numeric types in the list
	RemoveAllNumericTypes	Clears the numeric types list.
	NumericType(index)	Returns the name of a numeric type from the list.
	IsNumericType	Checks if the passed string represents one of the numeric types.