How to insert and retrieve QString from sqlite using native sqlite library instead of QSql

Question

I am using native sqlite library (amalgamation 1file) within a C++ project that uses Qt, I created a simple wrapper around it, but I am having troubles inserting and selecting long UTF-8 texts.

I use this code to insert data:

SqlResult *result = new SqlResult();
sqlite3_stmt *statement;
int current_parameter = 1;
this->LastStatement = sql;
int x = sqlite3_prepare_v2(this->db, sql.toUtf8().constData(), sql.length() + 1, &statement, NULL);
foreach (QString text, parameter)
{
    ////////////// here is problem ///////////////
    x = sqlite3_bind_text(statement, current_parameter++, text.toUtf8().constData(), -1, SQLITE_STATIC);
    if (!this->Evaluate(x))
    ...

As you can see I am using SQL variable ? for which text is bound to using function sqlite3_bind_text(statement, current_parameter++, text.toUtf8().constData(), -1, SQLITE_STATIC) which should take value of QString text and turn it into utf8 const char *. However the text that I get in database is partially garbage (when I use some sqlite browser application I can see some weird symbols).

This is code I use to convert const char * which I get from sqlite3_column_text

static QString StringFromUnsignedChar( const unsigned char *str )
{
    std::string temp = std::string(reinterpret_cast<const char*>(str));
    return QString::fromUtf8(temp.c_str());
}

The text I get is same "garbage" I see in sqlite manager. So I suppose the problem is during insertion and that select probably works. What is wrong? How can I properly use that sqlite3_bind_text function with QString?

P.S. I would prefer not to use Qt's own sqlite implementation of sqlite, mainly for compatibility purposes (on linux I use Qt4 and on windows Qt5 and I would like to have identical sqlite library everywhere for portability)

Can it be a problem of [double UTF-8 encoding](http://stackoverflow.com/questions/4853134/what-is-qstringtoutf8-doing)? — Sga, Feb 06 '15 at 13:59
It seems I already found the bug, problem is that I need to use `SQL_TRANSIENT` so that sqlite creates a copy of const char * array, which as it was allocated on stack, gets deleted as soon as function call ends. sqlite stored the deleted string into DB which was reason for most characters to turn into garbage. — Petr, Feb 06 '15 at 14:02

Petr · Answer 1 · 2015-02-06T17:42:32.533

Problem in my case was with my own code, the usage is correct but implementation not. This line:

sqlite3_bind_text(statement, current_parameter++, text.toUtf8().constData(), -1, SQLITE_STATIC);

needs to be

sqlite3_bind_text(statement, current_parameter++, text.toUtf8().constData(), -1, SQLITE_TRANSIENT);

As documentation states:

The fifth argument to the BLOB and string binding interfaces is a destructor used to dispose of the BLOB or string after SQLite has finished with it. The destructor is called to dispose of the BLOB or string even if the call to bind API fails. If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns.

So the correct way to use QString with sqlite using its native API's is:

// Create and evaluate a statement
sqlite3_stmt *statement;
// Note: return value should be checked for error here
sqlite3_prepare_v2(pointer_to_sqlite_db, sql.toUtf8().constData(), sql.length() + 1, &statement, NULL);
// Bind QString to SQL statement - it must be transient so that sql text persist even after function call is over and data is destructed
sqlite3_bind_text(statement, current_parameter++, text.toUtf8().constData(), -1, SQLITE_TRANSIENT);
// step
sqlite3_step(statement);
// clean up
sqlite3_finalize(statement);

How to convert const char * back to QString

static QString StringFromUnsignedChar( const unsigned char *str )
{
    return QString::fromUtf8(reinterpret_cast<const char*>(str));
}

QString result = StringFromUnsignedChar(sqlite3_column_text(statement, column));

that conversion to std::string is unneeded and if you do want it you could just use QString::fromStdstring — ratchet freak, Feb 06 '15 at 14:54

How to insert and retrieve QString from sqlite using native sqlite library instead of QSql

1 Answers1