soci does not always return the same type for a NUMERIC column

Question

I use the soci library to access an sqlite3 database:

rowset<row> rs = (sql.prepare << "select * from my_table");
for (auto it = rs.begin(); it != rs.end(); ++it) {
    int some_value = it->get<int>(1);
    // ...
}

(This is not my actual code; it's heavily simplified to describe the problem.)

Now, most of the time, this works. However, sometimes, the value at index 1 in the row is a string, not an int (leading to a std::bad_cast exception). The SQL type of the corresponding column is NUMERIC(8) NOT NULL. I did quite some debugging and it looks like the number of entries in the row and their values are always correct. The only issue is that the value of this one row sometimes is a string.

This can either be a bug in soci or a false assumption on my side. My assumption is that the returned data type for a column is always the same. The type switch may happen in one run of my application - the value may be an int in one query result and a string in the next one.

As I don't know the underlying sqlite3 API, I am not even sure whether this problem originates in sqlite3 or in soci. The workaround seems to be straightforward: Check for the type of the row value and if it's a string, convert it to int. But it would be important to know whether this is expected behavior or a bug. Can someone comment on this?

Edit:

New rows get inserted like this:

soci::session sql(Connetion::pool);
sql << "INSERT INTO " << NAME << " (REP_TIMESTAMP, REP_EVENT, REP_LTU_ID, REP_SEC_ID) VALUES (:timestamp, :event, :ltuId, :secId)",
        use (bo.timestamp),
        use (bo.event),
        use (bo.ltuId),
        use (bo.secId);

Where bo.timestamp is a boost::posix_time::ptime with the following mapping:

template<> struct type_conversion<boost::posix_time::ptime> {
    typedef long long base_type;
    static void from_base(const long long& t, indicator& i, boost::posix_time::ptime& target) {
        target = Helper::Time::timeFrom(t);
    }
    static void to_base(const boost::posix_time::ptime& d, long long& target, indicator& i) {
        target = Helper::Time::secondsSinceEpoch(d);
        i = i_ok;
    }
};

Helper::Time transform a ptime to the number of seconds since 1.1.1970 and back. I don't see how I can possibly get a string into the column REP_TIMESTAMP with this code.

Answer 1

SQLite uses dynamic typing; the type you declare for a column has almost no effect.

You get a string value from the database because your program wrote a string value into the database. If you want to enforce column types, you have to add a separate check constraint:

CREATE TABLE MyTable(
    MyColumn INTEGER NOT NULL CHECK(typeof(MyColumn) = 'integer'),
    ...
)