System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: c8119ec190cb371f42842c91dd4728786f802924
Title: SQLiteDataAdapter fails to fill data set with contents of sqlite_stat4.
Status: Closed Type: Incident
Severity: Minor Priority: Medium
Subsystem: Data_Adapter Resolution: Works_As_Designed
Last Modified: 2014-11-18 22:09:09
Version Found In: 1.0.94.0
User Comments:
anonymous added on 2014-11-12 22:10:08:
I'm attempting to fill a DataSet using a query against sqlite_stat4, and receive the following exception:

System.InvalidOperationException: Inconvertible type mismatch between SourceColumn 'sample' of Byte[] and the DataColumn 'sample' of Object.
   at System.Data.Common.DataColumnMapping.GetDataColumnBySchemaAction(String sourceColumn, String dataSetColumn, DataTable dataTable, Type dataType, MissingSchemaAction schemaAction)
   at System.Data.Common.DataColumnMappingCollection.GetDataColumn(DataColumnMappingCollection columnMappings, String sourceColumn, Type dataType, DataTable dataTable, MissingMappingAction mappingAction, MissingSchemaAction schemaAction)
   at System.Data.Common.DataTableMapping.GetDataColumn(String sourceColumn, Type dataType, DataTable dataTable, MissingMappingAction mappingAction, MissingSchemaAction schemaAction)
   at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithoutKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
   at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

----------------------

The following code sample demonstrates the problem:

using System;
using System.Data;
using System.Data.SQLite;

namespace PossibleSQLiteBug
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
                sb.DataSource = ":memory:";

                using (SQLiteConnection connection = new SQLiteConnection(sb.ToString()))
                {
                    connection.Open();

                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "create table TEST (col1 int)";
                        command.ExecuteNonQuery();

                        command.CommandText = "insert into TEST values (1)";
                        command.ExecuteNonQuery();

                        command.CommandText = "create index my_index on TEST(col1)";
                        command.ExecuteNonQuery();

                        command.CommandText = "analyze";
                        command.ExecuteNonQuery();

                        using (DataSet ds = new DataSet())
                        {
                            ds.Locale = System.Globalization.CultureInfo.InvariantCulture;

                            using (SQLiteDataAdapter adapter = new SQLiteDataAdapter("select * from sqlite_stat4", connection))
                            {
                                adapter.FillSchema(ds, SchemaType.Source);
                                adapter.Fill(ds);
                            }
                        }
                    }
                }
            }
            catch (Exception err)
            {                
                Console.WriteLine(err.ToString());
            }

            Console.WriteLine("Press a key...");
            Console.ReadKey();
        }
    }
}

mistachkin added on 2014-11-13 18:14:35:
This is not a bug, per se.  There are no type names for the columns in the
"sqlite_stat4" table.  By default, the System.Data.SQLite type conversion
subsystem assumes a type of "Object" and this appears to conflict with the
values returned from the query.

mistachkin added on 2014-11-13 19:22:14:
The workaround for this issue appears to be adding the following line of code
after creating the connection:

    connection.DefaultDbType = DbType.Binary;