Hello everyone, i found out, that the following select statement produces different schemata in DataTable when using SQLiteDataAdapter.Fill and data rows are returned or not returned from the SELECT statement.
SELECT 1 AS literal FROM anTable WHERE 1<1 Especially i need those literals in UNION selects to identify the source select the particular data row came from. If data rows are present, then the data type of the column in DataTable is System.Int64. If no data rows are present, then the data type of the column in DataTable is System.Object. When using FillSchema-Method then in both cases the data type of the column in DataTable is System.Object. Best greetings Michael Sample code: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Data; using System.Data.SQLite; namespace SQLiteTest { class Program { static void Main(string[] args) { var file = Path.GetTempFileName(); var uri = "Data Source=:memory:"; var con = new SQLiteConnection(uri); con.Open(); using (var cmd = con.CreateCommand()) { Console.WriteLine("New database " + con.ConnectionString); cmd.CommandText = "CREATE TABLE t1 (f1 BLOB)"; Console.WriteLine(cmd.CommandText); cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO t1(f1) VALUES(NULL)"; Console.WriteLine("Insert one row: " + cmd.CommandText); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT 1 AS constVal FROM t1 WHERE 1<1"; Console.WriteLine("create SQLiteDataAdapter with SELECT: " + cmd.CommandText); var ada = new SQLiteDataAdapter(cmd); var set = new DataSet(); ada.SelectCommand = cmd; Console.WriteLine("new DataSet -> call DbDataAdapter.Fill() while no data in table"); ada.Fill(set); LogTypes(set); Console.WriteLine("!!! wrong data type for column constVal !!!\n"); LogSchemaTable(cmd); cmd.CommandText = "SELECT 1 AS constVal FROM t1 WHERE 1=1"; set = new DataSet(); ada.SelectCommand = cmd; Console.WriteLine("new DataSet -> call DbDataAdapter.Fill() while one row in table"); ada.Fill(set); LogTypes(set); Console.WriteLine("!!! correct data type for column constVal !!!\n"); LogSchemaTable(cmd); Console.WriteLine("\nSame test with SQLiteDataAdapter.FillSchema method\n\n" + cmd.CommandText + "\n"); cmd.CommandText = "SELECT 1 as constVal FROM t1 WHERE 1<1"; Console.WriteLine("create SQLiteDataAdapter with SELECT: " + cmd.CommandText); ada = new SQLiteDataAdapter(cmd); set = new DataSet(); ada.SelectCommand = cmd; Console.WriteLine("new DataSet -> call DbDataAdapter.FillSchema() while no data in table"); ada.FillSchema(set, SchemaType.Source); LogTypes(set); Console.WriteLine("!!! wrong data type for column constVal !!!\n"); cmd.CommandText = "INSERT INTO t1(f1) VALUES(NULL)"; Console.WriteLine("Insert one row: " + cmd.CommandText); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT 1 AS constVal FROM t1 WHERE 1=1"; set = new DataSet(); ada.SelectCommand = cmd; Console.WriteLine("new DataSet -> call DbDataAdapter.FillSchema() while one row in table"); ada.FillSchema(set, SchemaType.Source); LogTypes(set); Console.WriteLine(@"!!! STILL wrong data type for column constVal !!! conclusion: When using DataAdapter.Fill method with a SELECT statement that includes literal int values in select list, then DataColumn-objects with different data types are generated, wether the SELECT statement returns rows or not. When no rows are returned, System.Object type is used (wrong). Whenat least on row is returned, System.Int64 is used (right). The FillSchema-Method in both cases generates System.Object column instead of System.Int64 column."); Console.ReadLine(); } } private static void LogSchemaTable(SQLiteCommand cmd) { cmd.CommandText = "SELECT 1 as constVal FROM t1"; using (var rdr = cmd.ExecuteReader()) using (var sw = new StringWriter()) { var t = rdr.GetSchemaTable(); t.WriteXml(sw); Console.WriteLine("Schema table:\n" + sw.ToString()); } } private static void LogTypes(DataSet set) { var t = set.Tables[0]; Console.Write("Datatypes in DataRow: "); foreach (DataColumn c1 in t.Columns) { Console.Write(c1.ColumnName + "=" + c1.DataType.ToString()+", "); } Console.WriteLine(); } } } _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users