0

I have a field in a sqlite database, we'll call it field1, on which I'm trying to iterate over each record (there's over a thousand records). The field type is string. The value of field1 in the first four rows are as follows:

DEPARTMENT
09:40:24
PARAM
350297

Here is some simple code I use to iterate over each row and display the value:

while (sqlite_datareader.Read())
{
     strVal = sqlite_datareader.GetString(0);
     Console.WriteLine(strVal);
}

The first 3 values display correctly. However, when it gets to the numerical entry 350297 it errors out with the following exception on the .getString() method

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.SQLite.dll

I've tried casting to a string, and a bunch of other stuff. But I can't get to the bottom of why this is happening. For now, I'm forced to use getValue, which is of type object, then convert back to a string. But I'd like to figure out why getString() isn't working here.

Any ideas?

EDIT: Here's how I currently deal with the problem:

 object objVal;  // This is declared before the loop starts...

 objVal = sqlite_datareader.IsDBNull(i) ? "" : sqlite_datareader.GetValue(i);
 if (objVal != "")
 {
     strVal = (string)objVal;
 }
Improve this question
11
  • Did you try GetInt() instead of GetString(), for the one that's an integer? What is returned by GetValue(index).GetType()? What is the type of the actual value in that column? Commented Oct 18, 2019 at 17:35
  • @EdPlunkett I can't switch to GetInt(), because it's a string data type in the database. When I run GetType as you suggested, each type resolves as System.String I almost feel there's something inherently flawed here... Commented Oct 18, 2019 at 17:39
  • The exception is telling you that you are trying to cast something to a string that is not a string. Maybe its DBNull.Value. I don't know. But it's not a string. You can use GetValue() to find out what it actually is, and act accordingly. How do you "convert back to a string"? (string)obj, or System.Convert.ToString()? As far as "inherent flaws" go, by a vast margin the least tested and most poorly-understood code getting executed there is your own, and it's written by the least experienced developer. Commented Oct 18, 2019 at 17:46
  • 1
    ... and the reason I ask it because it would seem that even though your sqllite column is defined as a string, the method sqlite_datareader.GetString() seems to take the raw value (in your case 350297) and assume it's whatever that value's native type might be. In the case of 350297 it seeme to think it's an int, so sqlite must be offering up that value as 350297 instead of "350297". So when you use GetString() it throws an error. If you KNOW this is happening, then it seems like relying on GetValue() might be a better option. But, I don't know what type it returns by default. Commented Oct 18, 2019 at 19:03
  • 1
    ... and one last thing. Are you SURE that the sqlite column is defined as a string / varchar()? In sqlite, if you don't define the type of a column, it will allow any type. Commented Oct 18, 2019 at 19:04

1 Answer 1

Reset to default
1

What the question should have included is

  1. The table schema, preferrably the CREATE TABLE statement used to define the table.
  2. The SQL statement used in opening the sqlite_datareader.

Any time you're dealing with data type issues from a database, it is prudent to include such information. Otherwise there is much unnecessary guessing and floundering (as apparent in the comments), when so very useful, crucial information is explicitly defined in the schema DDL. The underlying query for getting the data is perhaps less critical, but it could very well be part of the issue if there are CAST statements and/or other expressions that might be affecting the returned types. If I were debugging the issue on my own system, these are the first thing I would have checked!

The comments contain good discussion, but a best solution will come with understanding how sqlite handles data types straight from the official docs. The key takeaway is that sqlite defines type affinities on a column and then stores actual values according to a limited set of storage classes. A type affinity is a type to which data will attempt to be converted before storing. But (from the docs) ...

The important idea here is that the type is recommended, not required. Any column can still store any type of data.

But now consider...

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

So even though values of any storage class can be stored in any column, the default behavior should have been to convert any numeric values, like 350297, as a string before storing the value... if the column was properly declared as a TEXT type.

But if you read carefully enough, you'll eventually come to the following at the end of section 3.1.1. Affinity Name Examples:

And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

So if the question details are taken literally and field1 was defined like field1 STRING, then technically it has NUMERIC affinity and so a value like 350297 would have been stored as an integer, not a string. And the behavior described in the question is precisely what one would expect when retrieving data into strictly-typed data model like System.Data.SQLite.

It is very easy to cuss at such an unintuitive design decisions and I won't defend the behavior, but

  1. at least the results of "STRING" type are clearly stated so that the column can be redefined to TEXT in order to fix the problem, and
  2. "STRING" is actually not a standard SQL data type. SQL strings are instead defined with TEXT, NTEXT, CHAR, NCHAR, VARCHAR, NVARCHAR, etc.

The solution is either to use code as currently implemented: Get all values as objects and then convert to string values... which should be universally possible with .Net objects since they should all have ToString() method defined.

Or, redefine the column to have TEXT affinity like

CREATE TABLE myTable (
   ...
   field1 TEXT,
   ...
)

Exactly how to redefine an existing column filled with data is another question altogether. However, at least when doing the conversion from the original to the new column, remember to use a CAST(field1 AS TEXT) to ensure the storage class is changed for the existing data. (I'm not certain whether type affinity is "enforced" when simply copying/inserting data from an existing table into another or if the original storage class is preserved by default. That's why I suggest the cast to force it to a text value.)

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

3 Comments

Man, this was very enlightening. I've done a good deal of work in SQL Server, but this is my first go-around with sqlite. This 'affinity' thing is new to me. I'm actually importing the data from a pdf with the pdftotext cli tool. Then I plan on cleaning the imported table up and moving to another table. When I began the cleaning, I ran into this issue. I actually tried defining the column as varchar and I got the same error. I haven't tried text yet though, but I will. I also tried CAST but not with the text type. Thanks again I hope this answer eventually gets the upvotes it deserves.
UPDATE: This is 100% correct. String's affinity for Numeric causes the problem. Casting or changing the data type to varchar or text solves the issue. I tried varchar before and got a false negative because I didn't check for nulls. The interesting thing is that there weren't any nulls, but I'm assuming this goes back to the 'affinity' issue.
I'm glad it worked! :) I feel for you in getting to know sqlite, especially after using a robust database server. As an embedded, server-less database I have learned to really enjoy sqlite, but type type affinity and automatic conversions can be a real pain if not careful. I tend to be a data-type purist since I'd rather take my time coding for strict types than trying to debug rouge data like your example. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.