System.Data.SQLite

System.InvalidCastException
  HResult=0x80004002
  Message=Unable to cast object of type 'System.DBNull' to type 'System.String'.
  Source=System.Data.SQLite
  StackTrace:
   at System.Data.SQLite.SQLiteDataReader.GetSchemaTable(Boolean wantUniqueInfo, Boolean wantDefaultValue) in x:\SQLiteDataReader.cs:line 1493

// Get the column meta data
          _command.Connection._sql.ColumnMetaData(
            (string)row[SchemaTableOptionalColumn.BaseCatalogName],
            (string)row[SchemaTableColumn.BaseTableName],
            strColumn,
            ref dataType, ref collSeq, ref bNotNull, ref bPrimaryKey, ref bAutoIncrement);

By default, BaseTableName and BaseCatalogName should be set to an empty string to prevent throwing the exception.
row[SchemaTableColumn.BaseTableName] = "";
row[SchemaTableOptionalColumn.BaseCatalogName] = "";

Can you provide the database schema involved in this issue?  It could be quite
helpful.

I've created an initial test case that is able to reproduce the exception;
however, in order to be confident in the fix, I would really still like to
see the original schema that triggered the issue.

https://github.com/ErikEJ/SqlCeToolbox/issues/641

Thanks for the information.

Are you able to test the changes on trunk to see if they fix the issue in your
environment?

I've tried using the SQL from the linked GitHub issue and it does not cause the
exception.

The problem has been fixed but there is still one issue when I try to get the ViewColumn of vwGroupBibs.

System.Data.SQLite.SQLiteException
  HResult=0x800007BF
  Message=SQL logic error
no such table column: tab.idGroup
  Source=System.Data.SQLite
  StackTrace:
   at System.Data.SQLite.SQLite3.ColumnMetaData(String dataBase, String table, String column, String& dataType, String& collateSequence, Boolean& notNull, Boolean& primaryKey, Boolean& autoIncrement) in x:\SQLite3.cs:line 2099


The following schema is from the file test.db of the linked GitHub
CREATE TABLE [GroupBibs] ( 
  [idGroupBib] uniqueidentifier NOT NULL 
, [idGroup] uniqueidentifier NOT NULL 
, [BibFrom] bigint  NOT NULL 
, [BibTo] bigint  NOT NULL 
, CONSTRAINT [sqlite_autoindex_GroupBibs_1] PRIMARY KEY ([idGroupBib]) 
);

CREATE VIEW [vwGroupBibs] AS 
WITH RECURSIVE 
    [tab] AS( 
        SELECT [BibFrom],  
               [BibTo],  
               [idGroup] 
        FROM   [GroupBibs] 
        UNION ALL 
        SELECT [BibFrom] + 1,  
               [BibTo],  
               [idGroup] 
        FROM   [tab] 
        WHERE  [BibFrom] < [BibTo] 
    ) 
SELECT DISTINCT [idGroup] AS [idGroup],  
       [BibFrom] AS [BibNumber] 
FROM   [tab]

This appears to be a different issue.  I will look into it.

For the original issue, do you have any additional SQL that I might be able to
try and replicate it?

Interestingly, the new SQL causes the InvalidCastException to be raised when run
against 1.0.108.0; however, it does not appear to cause any exception when run
against trunk.

Ok, it *does* cause the new exception when querying for VIEWCOLUMNS; however,
it does not cause any exception when querying for COLUMNS.

You can use the following code to reproduce this issue:

using (SQLiteConnection conn = new SQLiteConnection(@"data source=""x:\test.db"""))
{
    conn.Open();
    using (DataTable dt = conn.GetSchema("Columns", new string[]{null, null, "vwGroupBibs", null}))
    {
        MessageBox.Show(dt.Rows.Count.ToString()); //No Exception but result is empty.
    }
    using (DataTable dt = conn.GetSchema("ViewColumns", new string[] { null, null, "vwGroupBibs", null }))
    {
        MessageBox.Show(dt.Rows.Count.ToString()); //Throwing the following error message.
        /*
        System.Data.SQLite.SQLiteException
          HResult=0x800007BF
          Message=SQL logic error
        no such table column: tab.idGroup
          Source=System.Data.SQLite
          StackTrace:
           at System.Data.SQLite.SQLite3.ColumnMetaData(String dataBase, String table, String column, String& dataType, String& collateSequence, Boolean& notNull, Boolean& primaryKey, Boolean& autoIncrement) in x:\SQLite3.cs:line 2099
        */
    }
}

Changes on trunk should fix the remaining issues reported here.  Please let us
know if they work for you.

Thanks! Now everything is working fine but this issue.

using (SQLiteConnection conn = new SQLiteConnection(@"data source=""G:\My CodeLib\test1.db"""))
{
    conn.Open();
    using (DataTable dt = conn.GetSchema("Columns", new string[]{null, null, "vwGroupBibs", null}))
    {
        foreach (DataRow dr in dt.Rows)
        {
            System.Console.WriteLine(dr["DATA_TYPE"].ToString());
            //idGroup --> uniqueidentifier --> ok
            //BibFrom --> "" --> Unable to return the datatype 'bigint'
        }
    }
}

Unfortunately, given how the view is built, the metadata for the columns is not
generally available.  That the "idGroup" column returns valid metadata is not a
documented feature as much as it is an internal implementation detail, which may
change in the future.  In general, these types of calculated columns do not have
associated metadata.

The "no such column" bug here was that the SQLiteDataReader class was attempting
to query the metadata for non-table columns.  That has now been fixed.

Check-in [c956230a3c387977].