SQLite3: Insert BLOB with NULL characters in C++

1,098

Solution 1

You'll want to use this function with a prepared statement.

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

In C/C++, the standard way of dealing with NULLs in strings is to either store the beginning of the string and a length, or store a pointer to the beginning of a string and one to the end of the string.

Solution 2

Thank you all again for your feedback. This time I'm reporting how I solved the problem with the help of the indications provided here. Hopefully this will help others in the future.

As suggested by the first three posters, I did use prepared statements — additionally because I was also interested in getting the columns' data types, and a simple sqlite3_get_table() wouldn't do.

After preparing the SQL statement in the form of the following constant string:

INSERT INTO table VALUES(?,?,?,?);

it remains the binding of the corresponding values. This is done by issuing as many sqlite3_bind_blob() calls as the columns. (I also resorted to sqlite3_bind_text() for other "simple" data types because the API I'm working on can translate integers/doubles/etc into a string). So:

void* blobvalue[4];
int blobsize[4];
char *tail, *sql="INSERT INTO table VALUES(?,?,?,?)";
sqlite3_stmt *stmt=0;
sqlite3 *db;
/* ... */
db=sqlite3_open("sqlite.db");
sqlite3_prepare_v2(db, 
                   sql, strlen(sql)+1, 
                   &stmt, &tail);
for(int i=0; i<4; i++)
    sqlite3_ bind_ blob(stmt, 
                        i+1, blobvalue[i], blobsize[i], 
                        SQLITE_TRANSIENT);
if(sqlite3_step(stmt)!=SQLITE_DONE) 
    printf("Error message: %s\n", sqlite3_errmsg(db));
sqlite3_finalize(stmt);
sqlite3_close(db);

Note also that some functions (sqlite3_open_v2(), sqlite3_prepare_v2()) appear on the later SQLite versions (I suppose 3.5.x and later).

Solution 3

You want to precompile the statement sqlite_prepare_v2(), and then bind the blob in using sqlite3_bind_blob(). Note that the statement you bind in will be INSERT INTO table VALUES (?).

Share:
1,098
j_d
Author by

j_d

Updated on September 27, 2022

Comments

  • j_d
    j_d over 1 year

    I am building an availability calendar. This is a monthly view, and days are essentially a boolean, they can be either available or unavailable. There are multiple "products" that can exist on the calendar. Relatively simple.

    I store these "availability ranges" as an array of objects to be as terse as possible. So a possible data set for a single product looks like this:

    [
      {
        "startDate": "2016-11-08",
        "endDate": "2016-11-08"
      },
      {
        "startDate": "2016-11-11",
        "endDate": "2016-11-14"
      },
      {
        "startDate": "2016-11-20",
        "endDate": "2016-11-22"
      }
    ]
    

    The UI looks very similar to this calendar:

    enter image description here

    The real trouble comes when users update their availability. They have the choice to "update all" or "update one". For example, if Room 1 was already unavailable on January 5th, and the user now wants to make all rooms unavailable from January 1st to January 10th, I need to remove the Room 1 January 5th object from the array, because it overlaps with the new data.

    Additionally, I'd like to merge any timespans that are contiguous, eg:

    [
      {
        "startDate": "2016-11-08",
        "endDate": "2016-11-08"
      },
      {
        "startDate": "2016-11-09",
        "endDate": "2016-11-09"
      },
    ]
    

    Should be merged to:

    [
      {
        "startDate": "2016-11-08",
        "endDate": "2016-11-09"
      },
    ]
    

    I realise this is a relatively complex question, but surely there must be a pre-existing solution, or at least something similar?

    I have access to momentJs.

    • Admin
      Admin over 7 years
      What is your question?