System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 6ed567d61976f0be62f3a39cc5f3fdc79eb8a0c2
Title: SQLiteDataReader.GetInt64 method incorrectly throws InvalidCastException
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Works_As_Designed
Last Modified: 2013-03-15 01:07:23
Version Found In: 1.0.84.0
User Comments:
anonymous added on 2013-03-13 17:07:34:
CREATE TABLE TEST_MASTER ('ID' INTEGER PRIMARY KEY, 'NAME' TEXT);
CREATE TABLE TEST_2 ('MID' NOT NULL, 'COMMENT' TEXT, FOREIGN KEY (MID) REFERENCES TEST_MASTER(ID) ON DELETE RESTRICT ON UPDATE RESTRICT);

 SQLiteCommand sql_cmd;  //assume created properly
 SQLiteDataReader dr;

      sql_cmd.CommandText = "SELECT \"MID\" FROM TEST_2;";
      dr = sql_cmd.ExecuteReader();
      if (dr.Read())
      {
        var x = dr.GetValue(0);  //return rowid as string
        var y = dr.GetInt64(0);  //throws InvalidCastException, instead of (properly) returning an Int64
      }

mistachkin added on 2013-03-13 19:31:09:
What are the actual values in the database tables being used?

mistachkin added on 2013-03-15 00:07:58:
Issue reproduced locally.

mistachkin added on 2013-03-15 00:15:54:
Steps to reproduce using the attached database:

set fileName {C:\full\path\to\test.sqdb}
set c [sql open -type SQLite [subst {Data Source=$fileName}]]
set dr [sql execute -alias -execute reader -format datareader \
    $c "SELECT \"MID\" FROM test_2;"]
$dr Read
$dr GetValue 0
$dr GetInt64 0; # exception thrown here.

mistachkin added on 2013-03-15 00:21:07:
Further research reveals that this issue appears to be "by design".  The
"MID" column has no declared type; therefore, it is simply treated as TEXT
by System.Data.SQLite (i.e. "String").

Calling the GetValue method works because that method does not attempt to
validate the declared column type against anything; however, calling the
GetInt64 method fails because a column with no declared type cannot be
validated against the Int64 type (via the VerifyType method).

In theory, this behavior could be changed; however, it would require a break
with backwards compatibility.

The workaround is to call the GetValue method and then perform any necessary
conversion to Int64 via application code -OR- modify the declared type of the
"MID" column to something with an integer type affinity.

Attachments: