newObjects (fromerly known as ZmeY soft) SQLite COM database (part of AXPack1)
Home Products Docs & Libs
pixel.gif (49 bytes)
Home
Products by category
ALP site
ISAPI filters
ActiveX
Forums (discussions)
Buy direct (RegNet)
Articles and samples
Documentation online
Links
ACOMTools

Read more ...
Click here
ALP is implemented as an Asynchronous Pluggable Protocol. It acts like a WEB server but without need of network. It executes WEB applications such as ASP pages and CGI applications. ALP makes Internet Explorer to be server and client. With ALP you can write stand-alone desktop applications, CD-ROM autoruns, use ASP for pure desktop software and still keep your code ready to run on WEB servers too.
Write desktop software in ASP and CGI!
download it


Software, Companies and Jobs at itLocation.com

Site navigation
Products
ActiveX components



ISAPI Authenticator

Low cost ISAPI custom authentication filter. It ships with a centralized administration tool and a separate tool that enables customers, hosting their site on your server, to manage user accounts for this site only. New version 1.51 is available (02 Jan 2001)


Highlights of the day
Active Label ActiveX
Barcode ActiveX? Much more - the design and printing inside your WEB application
SQLite3 COM
SQLite3 COM ActiveX embeds the SQLite3 database engine and interface to it. Supports paremeterized views and triggers.
Active Local Pages 1.2
Write desktop apps in ASP and CGI. Create wutorun CDs using WEB technologies - yes it is possible!
ActiveX Pack1 family
Desktop Windows, CE/CE.NET and PocketPC! About 50 powerful components for all the Windows platforms.
AXGate 1.1 (new)
Script dafely any ActiveX in Pocket IE. Build applications in HTML and use local resources and components in them.
IE ScriptBar
Create complex toolbars for Microsoft Internet Explorer easier than you may have expected.

Licensing types legend
(M) Single machine license
(U) Unlimited per-company license
(D) Unlimited development license
(S) Special type of licensing

TotalASP.co.uk

Quick contact
General
zmeyinc@newobjects.com
Support
support@newobjects.com
Sales
sales@newobjects.com

Jan

Active visitors
80
Suggested links
Suggest this page to the other visitors. Click the button below to place this page in the list above .

 SQLite COM database (part of AXPack1)   
Price: FREEWARE Discount for resalers FREEWARE
Information Download Buy
SQLite COM Overview
Database interface illustrated
Download

Database interface illustrated

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)



Copyright newObjects (ZmeY soft) 2001-2005