System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 458738515e70f99f0ce997894c9602bfce9cb2ed
Title: Invalid cast exception when using DataReader's GetString method on a string value that happens to be a string representation of an integer
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Works_As_Designed
Last Modified: 2018-11-07 02:15:29
Version Found In: 1.0.109.0
User Comments:
anonymous added on 2018-08-26 10:28:19:
I have a table with a field of type string. I access the data using a DataReader and retrieve the value using the GetString() method. If the value happends to be a a string representation of an integer, eg "17500", then GetString() throws an InvalidCastException. An easy workaround is to use dr.GetValue(0).ToString() but it still seems to me that this should not happen. Here is a copy of commands and output typed in the Visual Studio immediate window at a breakpoint just before this problem occurs:

? dr.GetValue(0)
"17500"
? dr.GetDataTypeName(0)
"String"
? dr.GetString(0)
'dr.GetString(0)' threw an exception of type 'System.InvalidCastException'
    Data: {System.Collections.ListDictionaryInternal}
    HResult: -2147467262
    HelpLink: null
    InnerException: null
    Message: "Specified cast is not valid."
    Source: "System.Data.SQLite"
    StackTrace: "   at System.Data.SQLite.SQLiteDataReader.VerifyType(Int32 i, DbType typ)\r\n   at System.Data.SQLite.SQLiteDataReader.GetString(Int32 i)"
    TargetSite: {System.Data.SQLite.TypeAffinity VerifyType(Int32, System.Data.DbType)}

mistachkin added on 2018-08-26 21:56:42:
It would be very helpful to know the type of the column in question, as declared
in the schema.

mistachkin added on 2018-08-26 21:58:35:
Also, as a temporary workaround, you can add the "NoVerifyTextAffinity"
connection flag in the connection string.

anonymous added on 2018-08-27 08:10:50:
Many thanks for looking at this.

The Type of the field in the schema is String. In this case it is also the Primary Key for the table but I don't think this makes a difference.

mistachkin added on 2018-08-28 01:00:54:
The type name in the database schema is literally "String"?  Also, what is an
example of a query where this issue is seen?

anonymous added on 2018-08-29 12:52:09:
Here's some quick code that reproduces the error (on 64-bit Windows). The directory c:\test must exist.

System.Data.SQLite.SQLiteConnection SQLiteConn = new System.Data.SQLite.SQLiteConnection(@"Data Source=c:\test\test.db;Version=3;Pooling=True;Max Pool Size=100;");
            System.Data.SQLite.SQLiteCommand cmd = SQLiteConn.CreateCommand();
            SQLiteConn.Open();
            IDataReader dr = null;
            string s;
            try
            {
                cmd.CommandText = "Create Table  test (Code String NOT NULL DEFAULT '')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TEST (Code) VALUES ('Test')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TEST (Code) VALUES ('174500')";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT CODE FROM TEST";
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    s = dr.GetString(0); //invalid cast exception here on 2nd row
                    Console.WriteLine(s);
                }

            }
            finally
            {
                dr.Close();
                cmd.CommandText = "DROP table test";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                SQLiteConn.Close();
                SQLiteConn.Dispose();
            }

mistachkin added on 2018-09-02 17:51:20:
I'm able to reproduce the exception here.  The root cause of this issue is that
the SQLite core library returns 'integer' affinity for the second value, partly
due to its not considering the declared column type ('String') to have textual
affinity.

Everything appears to be working as designed.  There are two potential solutions
to this issue:

1. Change the column type to be 'TEXT' instead of 'String', which should cause
   the SQLite core library to return 'TEXT' affinity for the column value, even
   if it looks like an integer.

2. Use the connection flag 'NoVerifyTextAffinity', which will prevent exceptions
   from being thrown by the System.Data.SQLite type conversion subsystem.

The #1 solution is preferred in this case, if possible, because it reduces
the amount of unnecessary type conversion work performed by the SQLite core
library and the System.Data.SQLite code.