SQLite Blob insertion c++

Question

After visiting dozens of websites containing info about SQLite I still cannot find a solution to fix an error while binding a blob. Here is the table decleration:

CREATE TABLE ONE ( 
ID    INTEGER     PRIMARY KEY AUTOINCREMENT
                  NOT NULL,
NAME  CHAR( 50 )  NOT NULL,
LABEL CHAR( 50 ),
GRP   CHAR( 50 ),
FILE  BLOB 
);

And here is the code for insertion:

int InsertFile(string name)
{
const char* dbname = name.c_str();
sqlite3 *database;
int rc = sqlite3_open(dbname, &database);
char *zErrMsg = 0;
unsigned char *buffer = (unsigned char*) malloc(sizeof(char)*MAX);

ifstream file;
file.open("Sql.pdf", ios::in|ios::binary);

    if ( ! file )
{
        cout << "An error occurred opening the file" << endl;
}

int count = 0;

const void* fileptr = NULL;


fileptr = buffer;

while(file.good())
{
    char c=file.get();
    buffer[count]=c;
    count++;
}
file.close();


sqlite3_stmt *stmt = NULL;

char* statement = "INSERT INTO ONE(     ID,    NAME,    LABEL,    GRP,    FILE ) VALUES (     NULL,    'fedfsdfss',    NULL,    NULL,  ?);";

rc = sqlite3_prepare_v2(database, statement, 0, &stmt, NULL);


rc = sqlite3_bind_blob(stmt, 1, fileptr, sizeof(char)*count, SQLITE_TRANSIENT);


const char* result = sqlite3_errmsg(database);


rc = sqlite3_step(stmt);

result = sqlite3_errmsg(database);

sqlite3_close(database);


free(buffer);

fileptr=NULL;

return 0;

} EDIT: Pasted full function, the amount of characters im trying to insert is about 350K.

The result from binb_blob is always 21, error code contains nothing. buffer contains binary file data, which most probably isn't too big hence the error code. Any hints would be apprieciated.

It's also possible that `count` is bogus -- we don't see how that is set. — Hot Licks, Aug 07 '13 at 00:30
count is the amount of characters in the buffer, and whats wrong with fileptr? Placing the buffer itself doesnt change anything imo — Ghostli, Aug 07 '13 at 00:51
(As an experiment, replace "fileptr" with "NULL" in the bind statement. I'm betting it will run without error.) (At least assuming that the DB was successfully opened -- did you check the return code from open??) (And did you check the return code from prepare???) — Hot Licks, Aug 07 '13 at 00:56
Every return code before bind returns 0, thus it's open. Actually with NULL values it doesnt go errorfree aswell (same issue). Assumed placing NULL as fileptr. — Ghostli, Aug 07 '13 at 01:28
I'm using the pointer since i'm trying to stick to the official syntax: http://www.sqlite.org/c3ref/bind_blob.html — Ghostli, Aug 07 '13 at 01:30
Is there another way to alocate more than 65536 bytes in an array than by using malloc? char ptr seems to be the only choice to me — Ghostli, Aug 07 '13 at 01:44
You also have an unnecessary `;` in the SQL statement, but I don't think that's necessarily causing a problem. — Hot Licks, Aug 07 '13 at 01:45
I never said there's anything wrong with the malloc. It's `fileptr = &buffer;` that's wrong. — Hot Licks, Aug 07 '13 at 01:46
The semicolon is also present in the statement used to create a table, doesn't seem troublesome. — Ghostli, Aug 07 '13 at 01:47
well i changed it to fileptr = buffer which didn't resolve the problem. — Ghostli, Aug 07 '13 at 01:49
Well, the above is obviously not your real code, so hard to say what you mucked up. (FWIW, 21 is "misuse" -- a bit of a catch-all for bad parameters, using statements in the wrong sequence, etc.) — Hot Licks, Aug 07 '13 at 02:12
You say above you pasted the full function, but obviously not, since there's no code for checking the return codes from open and prepare. — Hot Licks, Aug 07 '13 at 02:29

CL. · Accepted Answer · 2015-03-02T17:01:59.203

Your code has too many errors to count.

Try something like this:

int InsertFile(const string& db_name)
{
    ifstream file("Sql.pdf", ios::in | ios::binary);
    if (!file) {
        cerr << "An error occurred opening the file\n";
        return 12345;
    }
    file.seekg(0, ifstream::end);
    streampos size = file.tellg();
    file.seekg(0);

    char* buffer = new char[size];
    file.read(buffer, size);

    sqlite3 *db = NULL;
    int rc = sqlite3_open_v2(db_name.c_str(), &db, SQLITE_OPEN_READWRITE, NULL);
    if (rc != SQLITE_OK) {
        cerr << "db open failed: " << sqlite3_errmsg(db) << endl;
    } else {
        sqlite3_stmt *stmt = NULL;
        rc = sqlite3_prepare_v2(db,
                                "INSERT INTO ONE(ID, NAME, LABEL, GRP, FILE)"
                                " VALUES(NULL, 'fedfsdfss', NULL, NULL, ?)",
                                -1, &stmt, NULL);
        if (rc != SQLITE_OK) {
            cerr << "prepare failed: " << sqlite3_errmsg(db) << endl;
        } else {
            // SQLITE_STATIC because the statement is finalized
            // before the buffer is freed:
            rc = sqlite3_bind_blob(stmt, 1, buffer, size, SQLITE_STATIC);
            if (rc != SQLITE_OK) {
                cerr << "bind failed: " << sqlite3_errmsg(db) << endl;
            } else {
                rc = sqlite3_step(stmt);
                if (rc != SQLITE_DONE)
                    cerr << "execution failed: " << sqlite3_errmsg(db) << endl;
            }
        }
        sqlite3_finalize(stmt);
    }
    sqlite3_close(db);

    delete[] buffer;
}

SQLite Blob insertion c++

1 Answers1

Linked