[soci-users] SQLite and type safety

[Krause Felix]

Hi everyone,

I use soci with sqlite3 backend and have some really nasty problem that has been bugging me for days. In short, my application autogenerates a high-level OOP/ORM interface for my SQLite DB. This interface uses soci internally. In one table, I store a timestamp coming from boost::posix_time::ptime into the database. I convert the value as follows:


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


(Helper::Time converts the timestamp into seconds since 1.1.1970 and back)

Writing a row into the DB looks 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);


What happens is that bo.timestamp (which is a boost::posix_time::ptime) sometimes gets inserted as string, not as int. This leads to std::bad_cast() when I try to load from the DB. I already asked on Stackoverflow, someone told me there that SQLite has dynamic typing and it's perfectly possible to insert a string into a numeric column. However, I would expect that this code always inserts the value as integer.

I didn't find any way to reliably reproduce the problem. It just sometimes happens that a string gets written into the DB. May it be that this is a problem on the soci side? Is it possible that a value changes its type before it gets inserted? How can I further debug this? As for now, I can only notice the problem when I reload the row. Is there a way to hook into soci and check for the correct data types before inserting, so I can at least track down the origin of the problem?

Thanks,
Felix