Sets/gets the level of type info reported by the Execute method.

Syntax:

object.TypeInfoLevel = value
v = object.TypeInfoLevel

Parameters:

The property is numeric and the may contain the following constants in the current version. The result when used below is assumed to contain the results returned by object.Execute(some_query).

0 - No type info is returned. The result(rowNumber).Info is empty.
1 - (default). Type info is returned as a single string containing semicolon ";" delimited list of type names for each column in the result.
2 - The type info is returned as a collection of type names. I.e. result(rowNumber).Info is a collection which contains equal number of items as the row collection itself. Each item in the collection is a string containing the type name of the corresponding column. The type name can be referred by the field index or field name as appropriate.
3 - Like 1 but the type names are stripped from any insignificant characters (see StripTypeName for more information on how the type names are stripped)
4 - Like 2 but the type names in the collection are stripped (see StripTypeName).

Examples:

Assume we have the following table declaration:

CREATE TABLE T (
  ID INTEGER PRIMARY KEY,
  A NUMERIC,
  B DATE,
  C INTEGER,
  D TEXT(20)
);

and we have  some data in it so that the queries below return some results. Assume the db is an initialized SQLite COM object and the database is opened. Lets use this query:

Set r = db.Execute("SELECT ID,A,B,C,D,A+C AS E FROM T")

Then if we have several rows in the result. Assume RowN contains a valid row index (e.g. smaller then the total number of the returned rows).

If we want to display the values in this row together with their types and row names we can use the following code lines:

If the TypeInfoLevel is set to 1 or 3:

arr = Split(result(RowN).Info,";")
For FieldN = 1 To result(RowN).Count
  %>
  <%= result(RowN).Key(FieldN) %>:<B><%= result(RowN)(FieldN) %></B> [<%= arr(FieldN - 1) %>]<BR>
  <%
Next

If the TypeInfoLevel is set to 2 or 4:

For FieldN = 1 To result(RowN).Count
  %>
  <%= result(RowN).Key(FieldN) %>:<B><%= result(RowN)(FieldN) %></B> [<%= result(RowN).Info(FieldN) %>]<BR>
  <%
Next

In both cases the result will look like this:

ID:3 [INTEGER]
A:6.25 [NUMERIC]
B:38327.5695717593 [DATE]
C:25 [INTEGER]
D:Some text [TEXT(20)]
E:31.25 [numeric]

However the highlighted column will differ when the types are stripped. When TypeInfoLevel is set to 3 or 4 it will look like this:

D:Some text [TEXT] 

Stripping the types helps you simplify the code that needs to know the column type in order to do something - no need to get rid of the brackets on your own.

How the types can be used? For example above we have one DATE column which is recorded in the database as double precision floating point number. If we want to display it as formatted date we will need to change our code a little. Let's use the second sample with TypeInfoLevel set to 4.

For FieldN = 1 To result(RowN).Count
  If result(RowN).Info(FieldN) = "DATE" Then
  %>
  <%= result(RowN).Key(FieldN) %>:<B><%= CDate(result(RowN)(FieldN)) %></B> [<%= result(RowN).Info(FieldN) %>]<BR>
  <%
  Else
  %>
  <%= result(RowN).Key(FieldN) %>:<B><%= result(RowN)(FieldN) %></B> [<%= result(RowN).Info(FieldN) %>]<BR>
  <%
  End If
Next

Of course in a real application we should pack such functionality in routines more convenient to use. For example we can create a formatting routine like this one (note that it will work only if AutoType is set to True and TypeInfoLevel is set to 4) :

Function FmtField(row,field)
  Dim f,t
  Set f = row(field)
  Set t = row.Info(field)
  Select Case UCase(t)
    Case "DATE"
      FmtField = FromatDateTime(CDate(f))
    Case "TEXT"
      FmtField = f
    Case "UCASETEXT"
      FmtField = UCase(f)
    ... etc. ...
  End Select
End Function

Using such a routine can help you automate the formatting and apply certain custom type formatting techniques based on the column type name.

Remarks:

Note that the component default behavior is not in favor of extensive type information usage. I.e. AutoType and TypeInfoLevel properties are by default set to False and 1. This is so because the features they control may need additional processing and memory consumption. For the most small (a few ASP simple pages for instance) applications such features are not needed. Thus turning them on by default will require you take care to turn them off when you write something in haste (to test something for example). On the other hand when you write something more complex the effort to set two simple properties justifies itself and allows you simplify the more complex operations you perform throughout the application.

Applies to: SQLite COM object

See also: AddNumericType, RemoveNumericType, NumericType, CountNumericTypes

Supported on:

Windows 95/NT and later
Windows CE 3.0 and later
Pocket PC/Windows Mobile 2003 and later
Windows CE.NET 4 and later