SQLite inserting string data with parameters leads to invalid cast exception

Question

Good morning,

I was working on some unit tests for our SQLite 3 database code, and ran into this interesting problem. Here is a unit test that passes with flying colors:

[Fact]
public void TestDB()
{
    FileInfo fi = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".sqlite");
    using (SQLiteConnection conn = new SQLiteConnection($"Data Source={fi.FullName};Version=3;"))
    {
        conn.Open();
        conn.ExecuteNonQuery("CREATE Table Temp(A INTEGER NOT NULL PRIMARY KEY, B STRING);");

        string sql = "INSERT INTO Temp (A, B) VALUES (@A, @B)";
        string s = "Test";
        using (SQLiteCommand command = new SQLiteCommand(sql, conn))
        {
            command.Parameters.Add("@A", System.Data.DbType.Int32).Value = 1;
            command.Parameters.Add("@B", System.Data.DbType.String).Value = s;
            command.ExecuteNonQuery();
        }

        sql = $"SELECT B FROM Temp WHERE A = 1";
        using (SQLiteCommand command = new SQLiteCommand(sql, conn))
        using (SQLiteDataReader reader = command.ExecuteReader())
        {
            bool read = reader.Read();
            Assert.True(read);
            if (read)
            {
                Assert.False(reader.IsDBNull(0));
                string b = reader.GetString(0);
                Assert.Equal(s, b);
            }
        }
    }
}

but if I change one line:

string s = "1";

An InvalidCastException is thrown on the reader.GetString(0) line. Thinking maybe an additional hint is necessary by specifying the column, I also tried this:

SQLiteParameter a = new SQLiteParameter("@A", System.Data.DbType.Int32, "A") { Value = 1 };
SQLiteParameter b = new SQLiteParameter("@B", System.Data.DbType.String, "B") { Value = s };
command.Parameters.Add(a);
command.Parameters.Add(b);

Nothing changed.

Anyone have any ideas how to use parameters and insert data into a string column where the data is a numeric string?

Not sure a data type of STRING is really recognised by SQLite (see 3.1 Determination of column affinity sqlite.org/datatype3.html, sounds like it would default to numeric for the affinity). I haven't tested that theory but maybe try TEXT instead of STRING. — steve16351
– steve16351, Commented Jan 21, 2019 at 19:09
STRING is a perfectly legal column type for SQLite, the same problem has been known to happen with TEXT as well as VARCHAR columns when using .GetString(). The built in converter is kicking in at a wrong time I think. One solution is to use reader[0].ToString() — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 21, 2019 at 20:55

AlfieJ · Accepted Answer · 2019-01-21 19:15:40Z

1

@steve16351 had the answer.

The problem was actually in CREATE TABLE where the B column was type STRING. SQLite uses type TEXT.

Changing that to: CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT); fixed the problem.

Thank you!

answered Jan 21, 2019 at 19:15

AlfieJ

3691 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite inserting string data with parameters leads to invalid cast exception

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related