SQL As Understood By SQLite (sortof)

TYPES

sql-statement ::= TYPES [datatype name][,datatype name][,datatype name][,...] ; select-stmt
select-stmt ::= see SELECT

Use the TYPES keyword before a SELECT statement to provide the SQLite ADO.NET provider a list of return datatypes to expect from the subsequent SELECT statement. 

This is a language extension (aka hack) to SQLite specifically for the ADO.NET data provider.  It is a pseudo-statement, meaning only the ADO.NET provider understands it.

Background

Due to SQLite's typeless nature, there are certain kinds of queries for which the ADO.NET provider cannot determine the proper return data type.  Scalar and aggregate functions pose a particular problem because there is no requirement for a given scalar or aggregate function to return any particular datatype.  As a matter of fact, scalar functions could theoretically return a different datatype for every row or column in a query and this is perfectly legal from SQLite's point of view.

Since ADO.NET is designed around a typed system and we're shoe-horning SQLite into it, this keyword helps the provider out in cases where the return type cannot be easily determined.

This command must be used in conjunction with a SELECT statement.  It only works when both the TYPES keyword and its value(s) are passed along with a SELECT statement as a single semi-colon separated unit.

Examples

TYPES [bigint], [int], [smallint], [tinyint];
SELECT 1, 2, 3, 4;

The above query would return the columns as types System.Int64, System.Int32, System.Int16 and System.Byte respectively.

TYPES [bigint], [int], , [tinyint];
SELECT 1, 2, 3, 4;

In this sample, only columns 1, 2 and 4 would have explicit typing.  Column 3's datatype would pass though the system and be discovered normally.

TYPES real;
SELECT SUM(Cost) FROM [Products];

The above query explicitly tells the provider that the SUM aggregate function returns a System.Double.

Usage Notes