std::string is stored as blob?

[eismue]

Hello.
Maybe there is a quick answer, because someone has more knowledge than me (new to c++, completely new to sqlite and of course sqlite_orm).

I store a std::string in a table using sqlite_orm. In the UI tool 'DB Browser for SQLite' I don't see the actual stored text in the grid as a query result, but the placeholder 'Blob'. So sqlite_orm saves the std::string as a binary value. How can I get it to be saved as TEXT?
If I insert data from another application into the database, it is saved normally as text, which also displays the query results in the DB Browser as the saved text and not as a 'blob'.
I'm sure this is just a small thing I'm overlooking. I hope so...

Some information:

• SQLite 3.35.5
• DB Browser for SQLite 3.12.2
• MSVC/C++17
• current sqlite_orm version
• 'database first' design

Table:

CREATE TABLE "Observations" (
	"id"	INTEGER NOT NULL,
	"conditions"	TEXT NOT NULL COLLATE NOCASE,
	PRIMARY KEY("id" AUTOINCREMENT)
)

c++:

struct Observation
	{
		int id;
		std::string conditions;
	};

auto storage = sqlite_orm::make_storage(path,
	sqlite_orm::make_table<Observation>("Observations",
		sqlite_orm::make_column("id", &Observation::id, sqlite_orm::primary_key().autoincrement()),
		sqlite_orm::make_column("conditions", &Observation::conditions)
);

Any hint is much appreciated.

[fnc12]

@eismue looks like sqlite_orm has nothing to do with this cause std::string is saved as text by sqlite_orm, not blob. You can check by running

SELECT TYPEOF(conditions)
FROM Observations

Try to use a different SQLite client

[eismue]

I have found the actual cause (but no solution): if the text contains umlauts, the text data is saved as a blob by sqllite_orm. If the text does not contain umlauts, it is saved as TEXT as expected.
The SQLite client used to view the data doesn't matter either: even in SQLiteStudio (which I got separately) it's the same problem with viewing the data, as it is saved differently by sqlite_orm.

Oh,

SELECT TYPEOF(conditions), conditions
FROM Observations

always returns 'text' (regardless of whether the text is readable in the client or not ('blob')).

Surely there is something very simple that can be added so that texts with umlauts are not saved as a blob like data. But I have no idea where to start. Maybe someone knows the problem and can give a hint...
Thanks.

[fnc12]

if TYPEOF returns TEXT it means the data is text no matter how SQLite clients represent this data. I am still sure it is not related to sqlite_orm. If you blame sqlite_orm try to replace sqlite_orm with libsqlite3 for text saving and check whether the issue exists

[eismue]

I will try this, but I will be away for a few days.
Maybe this can give you a hint: if I execute the following SQL

update Observations set condition = 'äüöß' where id = 1

the value in the programme after retrieving the data will look like this: 'äüöß'.

As I said, I'm not yet so deep into c++ that I'm really familiar with all the subtleties. It may well be that sqlite_orm is only apparently the 'cause' and only a victim of some side effect (this is why the post was opened in Discussions and not in Issues). My hope is still that other people are immediately aware of the behaviour and can provide a hint.

[trueqbit]

Hi @eismue,

I can explain to you why you see something different from what you expect.
sqlite_orm really doesn't do anything special here.
Your expectation that everything "just works" is perfectly understandable, but you need to know that text is always subject to "encoding".

There are several things and levels that need to be considered:

1. If you look at SQLite's fundamental datatypes you will see that a text column is “stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)”.
2. When you have an SQL statement, sqlite_orm binds the string as a parameter to that statement. The documentation for sqlite3_bind_text() says that the string “should be a pointer to well-formed UTF8 text”.
3. An std::string can be any narrow character string and as a programmer you must know the encoding of the string at all times. Since sqlite_orm simply passes the specified string to SQLite3 with sqlite3_bind_text(), the encoding UTF-8 is expected.

I suspect that your string is ISO-8859-1 encoded, depending on where you got the input from.

[eismue]

Thank you for your comments. I guess I recognize the direction now and will take the topic to consideration. Unfortunately, I can't get to it immediately. I appreciate the detailed reply. Thanks again!