Rusqlite not returning the data type specified in the table

help
1

So I am having an issue in that when I am trying to recall data from an SQLite database the variable type changes depending on what is being returned. I have a table that looks like this
table

and inside the Zone column, I have this data. As you can see the data type of the column is a string. And it looks something like '2' or like '4/5/6'.

zoneData

#[derive(Debug)]
struct StationStringZone{
    station_id: i32,
    zone: String };

let mut command = conn.prepare("SELECT stationId, zone FROM Stations")?;

let stations = command.query_map(NO_PARAMS, |row|{
    Ok(StationStringZone{
        station_id: row.get(0)?,
        zone: row.get(1)?,
        })
})?;

for row in stations{
    println!("{:?}", row.unwrap());
}

When running this piece of code I get this error.
"thread 'main' panicked at 'called Result::unwrap() on an Err value: InvalidColumnType(1, "Zone", Integer)', src\main.rs:71:30"
From what I can work out the query_map() is trying to convert the String "3" into an int even though it is stored as a string in the table and is being put into a string.

This feels like a bug with rusqlite as surely it shouldn't convert from string to int. And with some other testing, it works fine if I set the type of zone as int and only crashes when gets to a row with something like "2/3/4" which means that it can convert it into an integer fine but when it meets something that is an actual string it crashes and doesn't like it.

Is there a way of getting around this at all? And am i missing something or is this a bug?

2

I don't know how to fix this, but it seems pretty weird.

3

It’s possible that you actually have a mix of integers and strings stored in that column. Sqlite considers the column type to be only a hint, and tracks the type of each individual value separately. From the Sqlite docs:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

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

1 Like
4

That is very weird. I have never heard of SQLite having this quirk. I have done some testing with it in python as I have done a lot more work with SQLite in that language and you are right, it seems to return back the data type that best suits what's in it.

To fix this I have had to make the table like I probably should have in the first place and make the zones into their own table. Thank you for the help

5

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.