The SQLite engine compiled into the SQLite COM component is type-less. This means that it will permit you put any value into any column except a few special cases discussed further. This is true also about the size of the value - for instance on Jet, MS SQL server, MySQL and other DB-s you will receive error if you attempt to put a string long 100 characters in a filed declared as 50 characters wide. In SQLite it will pass and will be recorded correctly.

No matter what type is assigned to the column in the table declaration the column will accept any other type. If you want to enforce some restrictions you will need to do this yourself - from outside (in the application's code) or from inside (triggers for example). Only few restrictions can be effectively applied in a CREATE TABLE statement or an INDEX (see also SQL reference) and they are not type oriented - NOT NULL (to disallow null values in the column, UNIQUE (to disallow duplicate values) and INTEGER PRIMARY KEY will have some more effect but only on one column.

So, the only true exception is the INTEGER PRIMARY KEY column declaration which makes the column auto increment and forced to contain only INTEGER values (32-bit). While the values in such columns are managed by the DB engine this has nothing to do with the typing of the actual data.

How this works without types?
(See also the expressions in the SQL Reference for detailed description of the type behavior in expressions.)

The trick is that the values are converted to whatever is needed only when they occur in statements, but there are only two general types supported: TEXT and NUMERIC. In other words any expression may return only one of these two types (see the expressions for information which expressions generate which type).

The types of the fetched data.

Thus you will be concerned about the types when you fetch data from the DB. The SELECT statements return set of fields. Part of them may correspond to directly to certain columns of the tables involved, others will be result of expressions.

The result fields that are result of expressions will be NUMERIC or TEXT depending on the result of the expression, while the fields that correspond to columns directly will be reported as declared (with the type name specified in the CREATE TABLE when the table has been created).

Sometimes this type-less-ness is something desired and helpful, but other applications will need some more strict typing for some columns at least. SQLite allows you to use the DB engine as you prefer with very little efforts from the outside (in the application code). So, you can combine both at the level that suits your needs.

How to deal with the types yourself.

There are two directions for which this question must be answered separately - writing data (INSERT, UPDATE) and reading data (SELECT). In all the sample code lines below we will assume that the db variable has been created like

Set db = Server.CreateObject("newObjects.sqlite.dbutf8")

and then a database has been opened using db.Open. We assume that the tables we use for sample purposes already exist.

Writing data (INSERT, UPDATE)

The most convenient way to put the data in correct form (from typing point of view) is to use a formatting function that will do this. The Sprintf method of the StringUtilities object is just what you need (it is useful for non-db scenarios as well). With it you specify a format string that contains the constant parts of the query and escape command sequences where data must be put from externally supplied arguments. For example lets take an INSERT statement we want to compose:

Set su = Server.CreateObject("newObjects.utilctls.StringUtilities")
' Assume the variables arg1,arg2,arg3 are initialized from appropriate
' source. For example arg1 = Request("SomeField") and so on.
str = Sprintf("INSERT INTO T (F1,F2,F3) VALUES (%q,%d,%M);",arg1,arg2,arg3)
Set r = db.Execute(str)
...

The Sprintf uses variable argument list. This means you can pass as many arguments after the first argument of the function (which is the format string) as the format string requires. Each %<something> sequence in it refers to an argument from the list and the sequence specifies how it will be formatted in the output string. See the syntax of the %<something> sequences in the format specifications for the Sprintf method.

In the above example %q - means that the arg1 will be treated as string and all the ' characters in it will be escaped (by doubling them) in SQL safe manner and finally it will be enclosed in 'quotes' when inserted instead of %q in the output string. The next one %d refers to the next argument (arg2) and it will be treated as integer value, the %M refers to the next one arg3 and says that it will be treated as double precision value which will be formatted with Maximum precision.

The Sprintf method uses by default . for decimal symbol thus it will generate correct SQL numeric representations for the numeric arguments. Also the %q and %Q escapes help you escape the strings passed to the data base without additional efforts.

As the above can be combined in one line of VBScript code (or translated to one line of JScript code) you need only single line to perform all the typing you need no matter that the database engine will not enforce types itself. The Sprintf method will try to convert the arguments to the types required by the escape command (%<something>) that corresponds to it. If the conversion fails error will be generated as like another database engine will generate if you try to put incompatible value type in it.

Reading data (SELECT)
Numeric types list definition

In general the data read is always textual unless the auto typing mode is set (see AutoType property). The Auto Typing mode simplifies the conversion tasks you need when reading data, but again it is highly customizable and you can tune the data base to work in quite different ways depending on the settings you specify (see AddNumericType, CountNumericTypes, NumericType and the other related members).

Aside of auto typing mode you have the ParseNumeric method. It parses a string that contains a numeric value in SQL compatible format (. - for decimal, E or e for exponent if any). However it needs additional effort to be applied. 

To make it easier you can turn on auto typing mode after creation of the SQLite COM object. By default the following types:

NUMERIC, INTEGER, SMALLINT, DATE, TIME, DOUBLE, FLOAT, SINGLE, SHORT, LONG, DATETIME, INT, BYTE

are treated as numeric. If the auto typing mode is turned on when packing the result the returned values (the fields in the row collections) will be converted to VT_I4 or VT_R8 VARIANT-s if they are of one of the above types. I.e. the corresponding elements in the row collections will contain VARIANT values of these types and the application will be able to use them directly in expressions where numbers are needed.

When you work with English locale settings this may seem a bit stupid work done for nothing, but remember that many locales use incompatible numeric formats and the returned strings (if auto typing mode is off) will not be recognized as numeric values by the most languages (VBScript for example). So you need to use one of the both methods - manual usage of ParseNumeric or auto typing mode if you expect that your application will deal with locales that may cause problems. Even if you do not have such plans it is recommended - for example many users in non-English speaking countries use English as their work language. If it happens that such an user attempts to use your application it will fail if his locale settings are different and you do not use auto typing or ParseNumeric manually.

The types treated as numeric can be changed to fit your needs. Using the following methods and properties you can change that list: AddNumericType, RemoveNumericType, CountNumericTypes, RemoveAllNumericTypes, NumericType(index). This is called numeric types list in this documentation .This may be useful to keep your database declarations as close as possible to syntax compatible with other databases (if for instance another version of the application works with another database engine).

What to do with date/time values?

Most database engines support some kind of date/time representation formats for date/time literal values. This is used by many developers in the queries they execute. It is particularly useful if the database is to be maintained manually from an SQL console from example. However if the interface with the database is through an application it is of little importance and all you need to consider is correct functioning of the arithmetic functions with date/time values or at least correct ordering if no expressions will be used.

With SQLite COM you can design you own technique, but there is one that is most convenient of all:

The VT_DATE (vbDate) in COM is in fact double precision floating point number that represents the date and the time. So it is always possible to convert VT_R8 (vbDouble) value to VT_DATE (vbDate) value and reverse without loses. For instance CDate(CDbl(Now)) will be equal to Now in VBScript. The implicit conversions wherever they are required by the expressions used in the application will always succeed.

This makes the VT_R8 (vbDouble) numeric type most convenient for usage in the database. It will behave perfectly in any arithmetic expressions and also will keep the perscision if formatted in the SQL statements with maximum precision.

When writing data into the db: For that purpose you can use the Sprintf method and the %M escape (see the format specifications) to print the date/time values from the application into the SQL statements executed over the database.

When fetching data from the db: Using ParseNumeric or automatically (if auto typing is on) the values that contain date and/or time values will be converted to VT_R8 (vbDouble) which will implicitly convert to DATE wherever the expressions need it this way in your application. If auto typing is used all you need to do is to make sure your numeric types list (see above) contains the type names you use to indicate date and/or time values. The default configurations assumes DATE and TIME type names are used for this purpose.

Thus the back-and-forth example below:

db.AutoType = True
db.AddNumericType = "DATE"
db.Ecxecute("CREATE TABLE T (A DATE);")
Set r = db.Execute("INSERT INTO T (A) VALUES (%M);",Now)
addedID = r.Info
Set r = db.Execute("SELECT A FROM T WHERE OID=" & addedID)
Response.Write "Today is: " & r(1)("A")

Will display the same as simple Response.Write "Today is: " & Now

The DATE type is good for both date and time and only date or only time values. The arithmetic operations will produce correct results. So, only one date/time type is enough and you can remove one of the default date/time type names if you do not want to use it.