| Related
products and tools |
ActiveX
Pack1
Core of the AXPack1 family - SQLite COM needs it. About 30
components for CE and desktop.
NetStreams
Networking addition to ActiveX Pack1 family.
Active Local Pages
Write desktop applications in ASP and CGI. (the pack is also included
with it).
AXGate
Script any ActiveX in Pocket IE
CE App manager Inovker
You may need this for your Pocket PC installations.
Script service
If you want to build Windows NT/XP service in script.
NDL
newObjects Development Library - combined documentation.
ASP Compiler
Compile scripts in DLL through VB or/and Create active projects to
generate static content. Uses ActiveX Pack1 as run-time library. |
AXPack1 family makes it easy to work with SQLite
COM database
The sample lines below are in VBscript and assume ASP/ALP environment,
but they can be written in any active scripting language and any scripting
environment. SQLite COM also supports some features to simplify the usage
with languages lacking error handling (like JScript 3.X). For other
environments such as WSH, MicroHost and so on the only vital differences
would be the way you create the objects and map the local file system
paths - the actual database usage will be exactly the same.
Opening a database:
Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
db.Open Server.MapPath("mydb.db")
The above does it in the most simple manner. If you want to handle
possible errors directly by yourself this can be written this way:
Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
If Not db.Open(Server.MapPath("mydb.db")) Then
Response.Write "Error occured:" & db.LastError
' Deal further with the error
End If
The LastError property contains the last error message - in both
cases when you check for the success manually (If Not ... Then) and if you
rely on the script's error handling (On Error Resume Next for instance).
However when you rely on the script's error handling the error text is
also available in the Err.Description (VBScript), so it is up to you to
choose the way that fits you best.
How about creating a new database?
Nothing special! Just specify non-existent database file name in the Open
method and it will be created.
Executing a query: (assuming a database is already opened and
contains whatever objects we refer in the queries below).
Set r = db.Execute("SELECT * FROM MyTable")
%>
<TABLE>
<TR>
<TH>Name</TH>
<TH>Age</TH>
</TR>
<%
For Row = 1 To r.Count
%>
<TR>
<TD><%= r(Row)("Name") %></TD>
<TD><%= r(Row)("Age") %></TD>
</TR>
<%
Next
%>
</TABLE>
<%
The query is executed and the results from it are returned as a
collection which contains one sub-collection for each row of the result.
Thus we can go through the results by just indexing the collections. They
are collections and we can also use For Each statements as well. However
it is more interesting that the fields can be accessed by name or index as
desired. For example the above can be written this way: Set r = db.Execute("SELECT Name, Age FROM MyTable")
%>
<TABLE>
<TR>
<TH>Name</TH>
<TH>Age</TH>
</TR>
<%
For Row = 1 To r.Count
%>
<TR>
<TD><%= r(Row)(1) %></TD>
<TD><%= r(Row)(2) %></TD>
</TR>
<%
Next
%>
</TABLE>
<%
Here instead of using r(Row)(field_name) we use r(Row)(field_index).
All the indices are 1 - based. So, as it is seen above each row is a
collection that can be enumerated or indexed to reach each field from it.
Each row is in turn a member of the results collection which you can call
a collection of records/rows. This resembles what one would get from ADO's
GetRows method - when numeric indices are used the result is accessed
almost like an array - r(R)(F) where R is the Row index and F is the Field
index (all 1- based). Still, when it comes to the fields the field index
can be replaced by the field name - r(R)("SomeFieldName"). All
is well, but what if the results expected are too much? The Execute
method has two optional parameters: Execute(query [, firstrow [, rowcount]]).
The first row specifies from which row the result will be returned - all
the result's rows before it are skipped. The row count specifies how many
rows will be returned starting from the firstrow. The rest are skipped
from the results. Thus the application can limit the result to the data
that is actually needed. For example if in an ASP page the results must be
shown in pages containing 10-20 rows per page you just need to set the firstrow
to (pageNumber - 1) * pageSize + 1 assuming that 1 - based page numbering
is used. And also set the rowcount to the pageSize. Which is even
more simple than with recordsets. The total number of the records in the
result can be obtained by executing additional query such as "SELECT
COUNT(*) FROM MyTable". In fact the recordsets do this implicitly
when you refer their properties, so it is not better, nor worse. The
types By default (as in the above examples) the results will all be
strings no matter what is contained in the table fields. Each result field
will be converted to string. This is most inconvenient as it requires the
script to use additional conversion functions and somehow deal with the
locale specifics (such as decimal sign which can be "." or
"," while in the result it will be always "." - dot).
SQLite COM gives you what is needed to receive these values already
converted to the appropriate types. All you need is set one property:
Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
db.AutoType = True
db.Open Server.MapPath("mydb.db")
To perform the operations correctly SQLite COM maintains a list of
types that are treated as numeric. Then when a query is executed each
resulting field which is of one of these types is converted to a numeric
value - short, long integer or double precision floating point which ever
represents the value in it better. Thus lets assume the "Age"
field in the above example is a result of a calculation. For example the
table may be defined as: CREATE TABLE MyTable (
Name TEXT,
Born DATE
);
Then we can execute query and use the result more directly:
Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
db.AutoType = True
db.Open Server.MapPath("mydb.db")
Set r = db.Execute("SELECT Name, Born FROM MyTable")
%>
<TABLE>
<TR>
<TH>Name</TH>
<TH>Age</TH>
</TR>
<%
For Row = 1 To r.Count
%>
<TR>
<TD><%= r(Row)(1) %></TD>
<TD><%= Year(Date) - Year(CDate(r(Row)(2))) %></TD>
</TR>
<%
Next
%>
</TABLE>
<%
the r(Row)(2) returns the field "Born". If the DATE type is
listed in the numeric types list it will be returned as double precision
floating point variant sub-type. This type is in fact OLE DATE which
converts directly to a date value. See in the documentation how to set the
list of the numeric types.
Thus SQLite COM database treats all the types internally as numeric
or text whichever is appropriate according to the expression/field
that produces the value. If the field has numeric nature it can be served
to the application in a numeric variant type and thus the application does
not need to use string-to-number conversions in order to use it further.
In most script expressions the returned field value will be able to
participate directly without need of any conversion (such as CLng, CDbl
and so on). A conversion may needed only where you want to ensure that the
numeric values are of the numeric type you want.
How to inspect the field type name? As it is seen above the
SQLite COM maintains list of types treated as numeric. You can change that
list, instruct the engine (AutoType = True) to return all the fields or
expression results converted to numeric values in the result collection so
that your script can use them in expressions without need of
string-to-number conversions. Still the application may need to learn the
name of the field's type in order to assign some application specific
meaning to it. for instance you may have DATE and TIME type names and
while they both may contain full date/time specifications you may want to
use only part of it - for example only the date from the DATE values and
only the time part from the TIME values. Most often this is not even
needed as you know what role is assigned to each field and you can just
include each of them only in the expressions where it belongs. But if you
are building more universal routine or module which works with various
results without prior knowledge about the structure and the field roles,
then you will need something else in order to recognize the role of the
filed. The popular practice is the field type name. Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
db.AutoType = True
db.TypeInfoLevel = 4
db.Open Server.MapPath("mydb.db")
Set r = db.Execute("SELECT * FROM MyTable")
For I = 1 To r.Count
' Something else ....
For J = 1 To r(I).Count
If r(I).Info(J) = "DATE" Then
Response.Write Year(CDate(r(I)(J))) & "-" & Month(CDate(r(I)(J))) & "-" & Day(CDate(r(I)(J)))
ElseIf r(I).Info(J) = "TIME" Then
Response.Write Hour(CDate(r(I)(J))) & ":" & Minute(CDate(r(I)(J)))
Else
' Something else ....
End If
Next
' Something else ....
Next
The above (partial) sample code uses the TypeInfoLevel 4. In this mode
the type names of the fields are attached in a collection attached to each
row. It is accessible through the Info property of the row and can
be addressed by index (as in the sample) or name as like the fields
themselves. In mode 4 the type names are stripped from any details expect
the base name. For example if a field is defined as TEXT(30) only TEXT
will be returned. This allows the application perform simple comparison of
the type name avoiding the troubles with the full type specification.
There are other modes such as 2 in which the type name is not stripped and
application receives it as it is specified in the table definition. Thus
the application is able to request the type information in the form that
suits its needs best. How about inserting/updating data in the
databse? Here comes handy the Sprintf/SCprintf and SAprintf methods
from StringUtilities object which is part of the AXPack1 core DLL. This
object implements string formatting capabilities based on the standard
introduced by the printf methods from the standard C libraries, but it
extends it so that it can serve database oriented usage and in some other
directions. StringUtilities is independent of the machine locale
settings. Its default behavior represents the sometimes called C-locale in
which the decimal point is always "." dot for example. Thus by
default this object produces strings compatible with the SQL language and
if needed for other non-database purposes the application can change its
behavior dynamically as needed. But we are interested in its
database usage mostly. Lets take an example: Set su = Server.CreateObject("newObjects.utilctls.StringUtilities")
db.Execute su.Sprintf("INSERT INTO Table1 (ID,Field1,Field2) VALUES (%d,%Nq,%q)",var1,var2,var3)
Assuming that the ID is some kind of ID %d puts the variable var1
converted to a decimal integer value in the string, %Nq puts
the var2 converted to string and enclosed in quotes - e.g. if var2
= "John" in the string its value will appear as 'John'.
If it happens that var2 is Null then in the string the keyword Null
will be put in the place of %Nq which will insert NULL in the
corresponding column. %q and var3 will produce the same as
var2 but the absence of the N in the escape sequence means that
Null values are not allowed and if var3 happens to be Null an error will
occur indicating that wrong value is being assigned. The StringUtilities
supports many format specifications and goes even further if you use one
of the special forms such as SCprintf. Instead of variable number of
arguments it accepts only two arguments - the format specification string
and a collection object. Then it extracts values from the collection
wherever an escape string appears (that is string beginning with % is
found in the format string) and it will convert them according to escape
sequence. If the conversion is impossible due to incompatible value type
an error will occur to inform the user/developer that the source
collection contains a wrong value. Furthermore with SCprintf an extended
escape sequence syntax can be used to fetch values from it not in the
natural order (i.e. 1-st element for the first sequence, 2-nd for the 2-nd
sequence and so on): Set su = Server.CreateObject("newObjects.utilctls.StringUtilities")
Set r1 = db.Execute("SELECT * FROM Table2")
' Some cycle or/and something else ....
db.Execute su.SCprintf("INSERT INTO Table1 (ID,Field1,Field2) " & _
"VALUES (%[PersonId]d,%[FirstName]Nq,%[LastName]q)",r1(RowIndex))
Here the words enclosed in [ ] brackets after the % sign specify an
element name from the collection passed as argument to SCprintf. Thus if
we have a result from another query and we want to extract certain fields
from it for usage in the new query we can do it this way - by referring
them by name instead of relying on their order in the row. This way such a
code may be used in more places without change and if the database
structure changes so that the order of the fields in the row supplied as
source changes it will continue to work correctly! StringUtilities offer
many other interesting features such as date/time formatting and automatic
type conversion that are very useful in pieces of code designed to work in
scenarios where not everything is pre-defined. Date and Time While
SQLite in its original source code (see www.sqlite.org
) supports some date and time functions in Windows environment the OLE
DATE type is the most convenient one as it is precise without y2k or
similar problems and most importantly it can be passed directly from the
script to the database and reverse. Thus SQLite COM defines several
functions to produce and parse OLE DATE values internally. OLE DATE is
also a double precision floating point numeric value which makes it
automatically compatible with any numeric expressions - you can perform
arithmetic operations with OLE DATE-s as with other numbers. So,
internally the OLE DATE values are preserved in the database "as
is" i.e. as double precision (8-byte) real numbers. Wherever you need
to extract part of them (Year, Month, Hour etc.) you can use the
corresponding OLE DATE function: OleYear, OleMonth OleHour and so on. Also
there are functions that convert OLE DATE values to text and from text.
They work with the widely accepted string date/time representation for
database usage YYYY-MM-DD hh:mm:ss. And finally there are DATE arithmetic
functions for the operations that are specific to date expressions - such
as OleDateAdd which can add a specified amount of units (for example
Years, Days, Months, Minutes and so on.) to one date and return the
result, or OleDateDiff which returns the difference between two dates in
the specified units (Days, Months, Years etc.). Wherever the application
needs to put a date/time value into a query it can use the %M
format escape sequence in Sprintf, SCprintf, SAprintf which converts the
argument to maximum precision double value and thus fully preserves the
value's precision. Alternatively the application can put in the queries
date/time strings formatted as it was mentioned above and rely on
ParseOleDate to convert them when the query is executed. A couple of
example SQL lines: SELECT OleDate(ParseOleDate("2001-12-22
14:30:10"));
will return "2001-12-22"
SELECT OleTime(ParseOleDate("2001-12-22 14:30:10"));
will return "14:30:10"
The full documentation of SQLite COM is included in newObjects
Development Library (NDL)
|
