As you may have already noticed the SQLite COM can be configured to act in different ways. For instance the AutoType and the TypeInfoLevel properties together with the numeric types list allow you to configure it to do some conversions automatically, to return column type information in different with the results of query(ies) execution. However these operations require additional system resources and while this consideration is not important when the database and the results extracted from it are small it may be of importance when huge amount of data is fetched. Thus the object defaults are set to minimize the resources needed and still provide most of the functionality. 

In simple applications written manually where a few simple pages or a simple script is written for a few minutes to test something or transfer something for example you may not need type information nor automatic type conversions based on the column types. In such case it may been enough just to interact with the database and receive all the columns as strings. In contrast in a more complex application which you plan to distribute you must be sure any numbers, dates and other types are retrieved as values of appropriate type. See SQLite types for examples on how the things may go wrong when  the locale settings on certain machines require "," to be used for decimals separator. 

Depending on what you are going to do you should pay attention to the properties mentioned above and to the numeric types list. It is recommended to make the object configuration adjustments immediately after creating it. For example:

Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
db.AutoType = True
db.TypeInfoLevel = 4

Is the most recommended configuration for applications that will target other users on machines with configurations you know little about..Also you should check the numeric types list to see if it contains all the types you want to treat as numeric values, but the defaults should be ok in the most cases and especially if you are designing the application from the beginning.

So if you want to have the full functionality turned on the 3 lines above should be the first thing you do. In ASP applications it may be convenient to create the SQLite COM object in the global.asa file and use it from the pages. This will save you the need to include a file or insert database connection code in each page. However note that this may be inappropriate for some applications (especially under IIS) if you want to be able to close the DB when it is not used. A sample global.asa file may look like this:

<OBJECT RUNAT=SERVER ID="db" PROGID="newObjects.sqlite.dbutf8" SCOPE="Application"></OBJECT>
<OBJECT RUNAT=SERVER ID="su" PROGID="newObjects.utilctls.StringUtilities" SCOPE="Application"></OBJECT>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
Sub Session_OnStart
 ' Nothing important here - put session init code if you need such
End Sub
Sub Application_OnStart
  ' Configure the database for convenient usage in the application
  db.AutoType = True
  db.TypeInfoLevel = 4
  ' Open the application's database
  db.Open Server.MapPath("/db/mydb.db")
End Sub
</SCRIPT>

This way in any ASP page in the application you will be able to refer to the db variable without need to create the object or open the database - this is already done when the application has been started. As the database is closed when the object is disposed there is no need to handle the Application_OnEnd event - the database will automatically close when the application is unloaded.

Note the other object created in the global.asa. Its ID is su (short for StringUtilities). When working with SQLite COM you will need it frequently. It is strongly recommended when you create queries (see SQLite types for some additional samples). In general you will need the Sprintf SAprintf and the SCprintf methods from the StringUtilities object to format the queries in a single line of code. For example:

Set r = db.Execute(su.Sprintf("INSERT INTO SomeTable (Field1,Field2,Field3) VALUES (%d,%q,%a)",v1,v2,v3)

Will generate an insert query where the variables are formatted automatically as specified - v1 as integer, v2 as string enclosed in quotes and any internal quotes escaped, v3 automatically.

This way you have safe query string that can be executed over the database. Note that the StringUtilities support customization and many advanced features. For example the auto formatting may be adjusted to apply the formats in certain order (try integer, try date then try text etc. for example). Also the SCprintf method allows you to extract the arguments from a collection by name which may prove extremely convenient when creating a query over the results from another:

Set r = db.Execute(su.Sprintf("SELECT * FROM Table1 WHERE ID=%d",curID))
Set r2 = db.Execute(su.SCprintf("INSERT INTO Table2 (F1,F2) VALUES (%[name]q,%[family]q);",r(1))

In the above we create a record in another table filled with some fields (name and family) from the results of another query.

With SQLite COM the enumeration of the result is like enumerating/indexing a collection. Instead of using a recordset and While .. Wend cycle for example you use For ... Next cycle to access the rows (see Execute for examples). To restrict the result you use the optional Execute parameters and you can create paged results with very little effort. As the SQL language is designed initially for direct usage (from an SQL console for example) this technique requires much less code than the recordset oriented database interfaces. It has certain limitations (for instance it is not convenient for binary data blocks stored in the database), but for the most of the database related activities it is simpler to understand and simpler to implement.

While Sprintf and the other similar methods have been proposed as helpers for query generation they can be used also to format user readable output as well. Very often the locale settings are inconvenient. For instance the date formats for many countries are real trouble even in such products like Microsoft Office (what you get printed out is not accepted back - parsed correctly). The StringUtilities object allows you configure the formatting as you want in spite of the locale settings. If you want to deal with this on your own and avoid the OS supplied routines (for example FormatDateTime in VBScript uses the locale information and this is so for all the formatting functions in it) you can create a separate instance of it and configure it to meet your needs.