System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: baf42ee1359faa6407de2a57e33ce9849e8482b2
Title: Unable to cast object of type 'System.DBNull' to type 'System.String'
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Fixed
Last Modified: 2018-05-29 01:04:50
Version Found In: 1.0.108.0
User Comments:
anonymous added on 2018-03-08 13:56:42:
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] = "";

mistachkin added on 2018-03-08 14:03:29:
Can you provide the database schema involved in this issue?  It could be quite
helpful.

mistachkin added on 2018-03-08 16:13:47:
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.

anonymous added on 2018-03-08 22:02:42:
https://github.com/ErikEJ/SqlCeToolbox/issues/641

mistachkin added on 2018-03-08 22:58:52:
Thanks for the information.

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

mistachkin added on 2018-03-09 01:24:10:
I've tried using the SQL from the linked GitHub issue and it does not cause the
exception.

anonymous added on 2018-03-09 01:49:03:
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]

mistachkin added on 2018-03-09 01:57:45:
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?

mistachkin added on 2018-03-09 02:01:32:
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.

mistachkin added on 2018-03-09 02:03:05:
Ok, it *does* cause the new exception when querying for VIEWCOLUMNS; however,
it does not cause any exception when querying for COLUMNS.

anonymous added on 2018-03-09 04:55:11:
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
        */
    }
}

mistachkin added on 2018-03-09 17:45:55:
Changes on trunk should fix the remaining issues reported here.  Please let us
know if they work for you.

anonymous added on 2018-03-10 09:49:38:
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'
        }
    }
}

mistachkin added on 2018-03-10 17:23:59:
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.

mistachkin added on 2018-05-29 01:04:50:
Check-in [c956230a3c387977].