Sqlite Error inserting

Question

I am sorta new to sqlite especially in a C setting. I am trying to insert a record into my table.

Here is my code:

int InsertIntoFileActionTable(FileActionData* fad)
{
    sqlite3_stmt * stmt;
    char *ErrMsg;
    const char *pzTail;
    int result = 0;

    string sql;
    sql = "INSERT INTO FileActionData (RootName, RootDirectory, RelativePath, ParentPath, FileName, Stem, Extension, UserName, SignalFlag, ProcessID, ProtectionOn, BSSMember, ProcessTime) ";
    sql += "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    result = sqlite3_prepare(pDb, &sql[0], 1000, &stmt, &pzTail);//This reseult is x00001
    result = sqlite3_bind_text16(stmt, 1, (void*)(&fad->RootName[0]), fad->RootName.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 2, (void*)(&fad->RootDirectory[0]), fad->RelativePath.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 3, (void*)(&fad->RelativePath[0]), fad->RelativePath.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 4, (void*)(&fad->ParentPath[0]), fad->ParentPath.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 5, (void*)(&fad->FileName[0]), fad->FileName.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 6, (void*)(&fad->Stem[0]), fad->Stem.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 7, (void*)(&fad->Extension[0]), fad->Extension.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_text16(stmt, 8, (void*)(&fad->UserName[0]), fad->UserName.length(), SQLITE_STATIC);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_int(stmt, 9, fad->SignalFlag);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_int(stmt, 10, fad->ProcessID);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_int(stmt, 11, fad->ProtectionOn);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_int(stmt, 12, fad->BSSMember);//This reseult is x00015 SQLITE_MISUSE
    result = sqlite3_bind_int(stmt, 13, fad->ProcessTime);//This reseult is x00015 SQLITE_MISUSE

    result = sqlite3_step(stmt);//This reseult is x00015 SQLITE_MISUSE
    if (result == SQLITE_MISUSE)
    {
        printf("MISUSE error");
    }
    return result;
}

Here is my FileActionData definition:

class FileActionData
{
public:
    std::wstring RootName;
    std::wstring RootDirectory;
    std::wstring RelativePath;
    std::wstring ParentPath;
    std::wstring FileName;
    std::wstring Stem;
    std::wstring Extension;
    unsigned long SignalFlag;
    unsigned long ProcessID;
    BOOL ProtectionOn;
    BOOL BSSMember;
    std::wstring UserName;
    time_t ProcessTime;

};

The error I get is a 1 (Generic) on the Prepare and x15 (21)(Misuse) on the other calls.

Additional info added: I also tried:

        result = sqlite3_prepare16_v3(pDb, &sql[0], 1000, 0, &stmt, (const 
 void**)&pzTail);//This result is x00001

the stmt is null which is why the rest of my calls return Misuse.

pDb is opened elsewhere.

What am I doing wrong? TIA

It is a mix. Case is my prepare comes back null an I have idea why. — Jeff, Jan 19 '18 at 02:47
Call [sqlite3_errmsg()](http://www.sqlite.org/c3ref/errcode.html) to find out what went wrong. — CL., Jan 19 '18 at 09:25

Pablo · Answer 1 · 2018-01-19T03:15:53.623

Looking at the documentation of sqlite:

https://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
The third argument is the value to bind to the parameter. If the third parameter to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_blob() is a NULL pointer then the fourth parameter is ignored and the end result is the same as sqlite3_bind_null().

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. If the fourth parameter to sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of the string is the number of bytes up to the first zero terminator. If the fourth parameter to sqlite3_bind_blob() is negative, then the behavior is undefined. If a non-negative fourth parameter is provided to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter must be the byte offset where the NUL terminator would occur assuming the string were NUL terminated. If any NUL characters occur at byte offsets less than the value of the fourth parameter then the resulting string value will contain embedded NULs. The result of expressions involving strings with embedded NULs is undefined.

The third argument should be a string, and the forth argument the byte size, not the string length.

It should be¹

result = sqlite3_bind_text16(stmt, 1, (void*)(fad->RootName.c_str()),
        fad->RootName.size() * sizeof(wchar_t), SQLITE_STATIC);

Or as Mark Benningfield suggests:

result = sqlite3_bind_text16(stmt, 1, (void*)(fad->RootName.c_str()),
        -1, SQLITE_STATIC);

¹I don't use C++ too much, that's why I had to look up how to get the size of the std::wstring. I found this: How can I get the byte size of std::wstring?

The first error is on the `sqlite3_prepare()` call, which will fail all of the subsequent bind calls. — Mark Benningfield, Jan 19 '18 at 03:03
@MarkBenningfield aren't the `sqlite3_bind_text16` also wrong in the way the OP is calling them? — Pablo, Jan 19 '18 at 03:04
I hadn't got to that point yet; the thing with the little plus signs gives me a headache. — Mark Benningfield, Jan 19 '18 at 03:07
I understand the issues with the binding and have added * sizeof(wchar_t) to the length. I do not understand the issue with the prepare. — Jeff, Jan 19 '18 at 03:07
@MarkBenningfield It's been years since I programmed with sqlite, so I've forgotten a lot of details. Please let me know if my answer doesn't contribute anything significant to the question, so that I remove it if that's the case. — Pablo, Jan 19 '18 at 03:10
@Pablo: No, I think it's okay. While it's not specifically part of the question, we would have had to cover that ground anyway. Windows wide characters are valid UTF-16, because by default they ignore surrogate pairs, so once the length is fixed up, there should be no trouble with the encoding. — Mark Benningfield, Jan 19 '18 at 03:15

Mark Benningfield · Answer 2 · 2018-01-19T09:58:17.080

I had to do a little research to confirm my nagging suspicion. The internal representation of a std::string is not guaranteed to be contiguous in memory, or even to be null terminated (until C++11, which I'm not sure Visual Studio fully implements), so sending SQLite the address of the first character won't work.

Either define a char* for the SQL text, and pass that, or pass the result of a call to the .c_str() member function. The same applies to the subsequent parameter binding calls: you can't pass the address of the first character of a std::wstring either.

If you have a valid UTF-8 sql statement, pass -1 to have SQLite parse the text up to the null terminator, and the database handle is in fact open, it should work.

Sqlite Error inserting

2 Answers2