Different behavior of .NET client 1.0.108.0 SQLiteDataAdapter / DataReader on literal int values in select list.

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Different behavior of .NET client 1.0.108.0 SQLiteDataAdapter / DataReader on literal int values in select list.

micha
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Different behavior of .NET client 1.0.108.0SQLiteDataAdapter / DataReader on literal int values in select list.

Joe Mistachkin-3

[hidden email] wrote:
>
> 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
>

Since no rows are returend, there is no type information available
based on the value of the returned data (as there is no data).

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users