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
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
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.
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
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.
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
db = Server.CreateObject("newObjects.sqlite.dbutf8")
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
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
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.
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
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
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
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
The types treated as numeric can be changed
to fit your needs. Using the following methods and properties you
can change that list: AddNumericType,
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:
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.
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
specifications) to print the date/time values from the application
into the SQL statements executed over the database.
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
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