+ 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
+-
+
- You cannot use parameters in the TYPES statement. +
- The TYPES statement must be immediately followed by a SELECT statement. +
- It is legal to pass multiple TYPES and SELECT statements in a multi-statement + command. +
- You may enclose datatypes in quotes "" or brackets []
+ or those `` thingies if you want.
+
+
+ +