SQLite DB Engine SQLite embedded data base engine

This component implements an embedded (into the component's DLL), zero-configuration SQL data base engine  The component is designed as add-on to the newObjects ActiveX Pack1 and uses certain classes from it to implement part of its functionality.

The database engine compiled into it is SQLite 2.8.15. SQLite is a public domain C library available as source code. You can learn more about it on this address: www.sqlite.org. This documentation contains copies of the content of some of the relevant pages from that site. If you you miss something please take a look there first. Note that there are two SQLite components - this one and SQLite3 based component. They are quite different - while this component is relatively light the SQLite3 component implements more sophisticated functionality. Although the database engine built into them is built upon a similar core they use different database file formats and cannot be used as a replacement for each other (of course, conversion is always possible). You must choose one of them for your application and stick to it. The right selection should be made over the needs of the application and the potential future enhancements that may be better served by one or the other database engine. SQLite is bundled with ALP version 1.2 and later and both SQLite and SQLite3 components are bundled with the next versions. When re-distributing applications you should include with them only the component you actually use (to save size).


The SQLite 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

SQLite COM object allows manual and Auto Typing mode. By default it is manual but many developers will want to use auto typing as it results in automatic conversions of the numeric types to numeric variant types when results are returned from a query. SQLite COM types for more information. In autotyping mode you can configure the type names that refer to numeric values as you prefer. 

When writing data to the db (INSERT and UPDATE queries for example) you can use the Sprintf or SAPrintf funtions from StringUtilities component to construct queries with minimal efforts.

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.

If used rationally SQLite engine is faster or equal to the most of the other small-scale and embedded databases (such as MS Jet, MySQL and so on).

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 SQLite COm as collection of rows which are collection of named values (the field values) in turn (see Execute). This is even easier to work with as it allows direct indexing of each value in each row by index or name. Thus 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 method to restrict the result to the part you really need.

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 Sprintf or SAPrintf methods you can do this in even less code.

Thus, in conclusion, SQLite COM makes the work with the database closer to the SQL language in contrast to ADO centered programming which tries 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 in most cases SQLite COM will require less code than equivalent ADO based solution. Added the fact that it is embedded, zero-configuration and able to work from scratch (without even COm registration) the weight of its good sides becomes higher. 


Component files
Creation (ClassID and ProgID)
Methods and properties
SQL language reference
SQLite COM types
SQLite COM OLE DATE SQL functions
Auto typing mode
StringUtilities.Sprintf and SAPrintf
Best techniques

Component files:

SQLITECOMUTF8.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 or alternatively they must be both configured with ALPFrame for on-the-fly execution.


Threading model: Both
Program ID: newObjects.sqlite.dbutf8
ClassID: {27917F02-97BC-4e88-AB7F-173E5E885FAC}
Threading model: Free
ProgramID: newObjects.sqlite.dbutf8.free
ClassID: {4BB07A77-6360-4e84-BC91-3EBCC29A2535}

Methods and properties

method.gif (107 bytes) Open Opens a database.
method.gif (107 bytes) Close Closes the currently opened database
method.gif (107 bytes) Execute Executes SQL statement(s) and returns the fetched data and other details.
method.gif (107 bytes) 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.
method.gif (107 bytes) ParseNumeric Parses numeric value from string in SQL compatible format regardless of the locale settings.
TypeInfoLevel Sets/Gets the level of type info reported by the Execute method. 
method.gif (107 bytes) StripTypeName Helper method. Strips the meaningful part of the type name.
Auto type management members
(numeric types list management)
AutoType Switches on/off auto typing support (default is off)
method.gif (107 bytes) AddNumericType Adds a type name to the list of the types treated as numeric.
method.gif (107 bytes) RemoveNumericType Removes a numeric type name from the list
CountNumericTypes Returns the number of the numeric types in the list
method.gif (107 bytes) RemoveAllNumericTypes Clears the numeric types list.
NumericType(index) Returns the name of a numeric type from the list.
method.gif (107 bytes) IsNumericType Checks if the passed string represents one of the numeric types.

SQL Reference

SQLite SQL reference
Pragma statements (SQLite specific extensions)
Unsupported SQL features





Overview | Reference


The NSMain object can be positioned as feature dispatcher and provider of functionality not applicable to another particular object from the library. It also initializes the WinSock and uninitalizes it when released. Thus the best practice is to keep one such object in the memory while using the library - first to create and last to release. Although the direct creation of the most of the other objects is possible it is not the best way for their usage - prefer NewXXXX methods (see below). 



newObjects Copyright 2001-2006 newObjects [ ]