Encoding problems during INSERT with Sqlite3 C++ and VisualStudio2010

Question

I'm developing a small wrapper for a project using sqlite3 with the C++ API and VisualStudio 2010. As far as it goes and checking with a tool like SQLiteDataBaseBrowser, the main problem is that the information I try to insert in the table appears corrupted / doesn't appear at all. The table appears to be correctly created with UTF8 encoding.

I tried using the Character Set configuration values in VS as "Use Multy-Byte Character set" and also tried with "Use Unicode Character set" but got no change in the result. Both gave me the same problem with the corrupted data. I use the typical std::strings converted to legacy c char* and as I've seen in several examples that it should work properly with sqlite3_bind_text(...) functions that the API provides.

    sqlite3_bind_int(stmt, 1, newShop.GetId());
    sqlite3_bind_text(stmt, 2, newShop.GetName().c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, newShop.GetLocation().c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 4, newShop.GetPicturePath().c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 5, newShop.GetRegisters().c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 6, newShop.GetMixes().c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 7, newShop.GetAllowedUsers().c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 8, newShop.IsAvailable() == true?1:0);

newShop is an instance of a class which contains the information in std::strings and int. I have to say that the bindings with int type, work perfectly and without problem, but the others look totally messed up. when the strings are hardcoded, they also look ok until I try to insert special characters such as "áàä" and such.

The table is created with the statement:

char *szSQL = "CREATE TABLE IF NOT EXISTS SHOPS (ID INTEGER PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, LOCATION TEXT, PICTUREPATH TEXT, REGISTERS TEXT, MIXES TEXT, USERS TEXT, AVAILABLE INTEGER NOT NULL);";
int rc = sqlite3_exec(db, szSQL, NULL, 0, NULL);

This is how it looks when I insert the data using the code snippet above:

sending it from a WINUNIT test that looks like:

Shop shOne = Shop(1234, "Its about nothing", "Manhattan", "seinfeld.jpg", "1111,2222,3333", "1000x1,2000x7", "10,11,12", true); 
Shop shTwo = Shop(4321, "Louie", "Manhattan", "", "1111,5555", "50000x1,10000x5", "60,70", true);

WIN_ASSERT_EQUAL(0, auxsql.InsertShop(shOne));
WIN_ASSERT_EQUAL(0, auxsql.InsertShop(shTwo));

Neither the insertion, the commit, the creation of the sql statement or any other function call to the sqlite3 API return an error code.

Answer 1

I found the solution. It was nothing related to encoding, as I thought because of the characters being strangely represented in the DB browser and also nothing related to the length.

The problem was in the last parameter of the binding. Where i pass SQLITE_STATIC, I should have passed SQLITE_TRANSIENT, as the object returned by the calls I want to bind are possibly destructed before the query is executed. Just as it is explained in this other question:

sqlite3_bind_text SQLITE_STATIC vs SQLITE_TRANSIENT for c++ string

Answer 2

I think sqlite reference is quite clear about the API usage.

In those routines that have a fourth argument, its value is the number of bytes in the parameter. To be clear: the value is the number of bytes in the value, not the number of characters.

As per your usage,

sqlite3_bind_text(stmt, 2, newShop.GetName().c_str(), -1, SQLITE_STATIC); the 4th arguement is -1. However it should be either newShop.GetName().length or strlen(newShop.GetName().c_str())`

Note : be careful when you deal with string with multi-wide chars. Where strlen(chinese string) ! = chinese string.len . Refer here for more details.