Search code examples
sqlitec++-cli

Writing Image to SQLite Database always fails

I am attempting to write a png to a SQLite3 database in C#. I have managed to correctly import the external DLL function sqlite3_bind_blob thanks to the answer here. But now I am getting an error when I write the image to my SQLite3 database.

Can you tell me what I am doing wrong? The function function sqlite3_bind_blob is returning the error 21 - SQLITE_MISMATCH 20 - Data type mismatch. I am unsure what exactly is going wrong.

Heres my code: 

string query = string.Format("INSERT OR REPLACE INTO myTable(lat, lon, image) VALUES({0}, {1}, ?1);", lat, lon);

if (sqlite3_prepare_v2 (_connection, query, query.Length, out stmHandle, IntPtr.Zero) != SQLITE_OK) {
    IntPtr errorMsg = sqlite3_errmsg (_connection);
    throw new SqliteException (Marshal.PtrToStringAnsi (errorMsg));
}

IntPtr SQLITE_TRANSIENT = new IntPtr(-1); // Represents SQLITE_TRANSIENT
int res = sqlite3_bind_blob (stmHandle, 1, blob, blob.Length, SQLITE_TRANSIENT);

// res always equals 21

From my debugging I know that the blob correctly contains valid png data because I can write it out to a file and open that file. I also know that the length of the blob is correct aswell. Maybe its my query string INSERT OR REPLACE INTO myTable(lat, lon, image) VALUES({0}, {1}, ?1);?

Solution

  • You are mixing managed with unmanaged code.

    int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

    You are providing managed IntPtr blob instead of unmanaged const void*blob.

    You should be very sure about what you're doing when mixing managed with unmanaged code - and it seam you're not...

    You may workaround this situation something like this (I don't have compiler right now) using:

    unsafe { //this is for C#... probably not needed in C++/CLI
    int res = sqlite3_bind_blob (stmHandle, 1, (const void*) blob.ToPointer(), (int) blob.Length, SQLITE_TRANSIENT);
}

    Anyways, unless you're pretty sure you need this stuff, I recommend you to go on managed and abandon SQLite3 CAPI.