Reported datatype can be wrong (using SQLite)

[Krzmbrzl]

The datatype that soci reports for a given entry in our query can be completely wrong, when using SQLite. Based on the code (and in accordance to my experience)

soci/src/backends/sqlite3/statement.cpp

Lines 484 to 489 in d7af321

	// This is a hack, but the sqlite3 type system does not
	// have a date or time field. Also it does not reliably
	// id other data types. It has a tendency to see everything
	// as text. sqlite3_column_decltype returns the text that is
	// used in the create table statement
	char const* declType = sqlite3_column_decltype(stmt_, colNum-1);

the reported type is that of the column the fetched value has originated from. This is okay, if the value is fetched as-is (though there is still the issue that SQLite doesn't really have proper types - e.g. it will probably be impossible to figure out that a given column was a date).
However, if the queried value has been transformed (e.g. via a cast), then the reported type can end up being completely wrong.

Example:

CREATE TABLE my_table(my_col DATE);
INSERT INTO my_table(my_col) VALUES ("2002-06-23");

SELECT CAST(strftime('%1', my_col) AS INTEGER) FROM my_table;

soci will think that the fetched value is of type date, whereas in reality the type is integer.
However, when asked SQLite will even report the correct type in this particular case.

Fiddle

[Krzmbrzl]

@vadz this issue is not actually solved by my commit. I only encountered the timestamp mismatch while skimming through the related code parts.
For this to be fixed, I guess we'd somehow have to leverage SQLite's typeof capability programmatically through the API in order for SQLite to at least tell us integers, reals and text apart.
Maybe it is enough to perform the check that in the current code is only used as a fallback, first and only if that returns text, would we move on to the column-type lookup 🤔

[vadz]

Oh sorry for misunderstanding.