SQLite3: Insert BLOB with NULL characters in C++
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 (?).
j_d
Updated on September 27, 2022Comments
-
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:
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 over 7 yearsWhat is your question?
-