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
