Working as coded (and as documented): https://sqlite.org/c3ref/blob_open.html
In particular read the paragraph that says: If the row that a BLOB handle points to is modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the BLOB handle is marked as "expired". This is true if any column of the row is changed, even a column other than the one the BLOB handle is open on. Calls to sqlite3_blob_read() and sqlite3_blob_write() for an expired BLOB handle fail with a return code of SQLITE_ABORT. Changes written into a BLOB prior to the BLOB expiring are not rolled back by the expiration of the BLOB. Such changes will eventually commit if the transaction continues to completion. Even though you do not bother to say what the error code is, it is 4. Which means SQLITE_ABORT ... > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Yuri > Sent: Saturday, 4 March, 2017 00:21 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] [BUG REPORT] Open blob is invalidated by the update to > unrelated fields in the same row > > The write operation using the open sqlite3_blob object fails after some > other field in the same row is updated. > > The testcase below illustrates the problem. > > > Yuri > > > > ---testcase--- > > #include <stdio.h> > #include <stdlib.h> > #include "sqlite3.h" > > int main(int argc, char **argv) { > if(argc < 2) { > fprintf(stderr, "Usage: %s <dbname> <filename>\n", > argv[0]); > exit(1); > } > > // file to insert > FILE *f = fopen(argv[2], "rb"); > if(NULL == f) { > fprintf(stderr, "Couldn't open file %s\n", argv[2]); > exit(1); > } > // Calculate size of file > fseek(f, 0, SEEK_END); > long filesize = ftell(f); > fseek(f, 0, SEEK_SET); > > // Table name we're going to use > char tablename[] = "testblob"; > char columnname[] = "blobby"; > > // Actual database handle > sqlite3 *db = NULL; > > // Database commands > char create_sql[1024]; > snprintf(create_sql, sizeof(create_sql), "CREATE TABLE IF NOT > EXISTS %s (" > "id INTEGER PRIMARY KEY, fld INTEGER, %s > BLOB)", tablename, columnname); > > // Going to insert a zeroblob of the size of the file > char insert_sql[1024]; > snprintf(insert_sql, sizeof(insert_sql), "INSERT INTO %s (%s) > VALUES (?)", tablename, columnname); > > // SQLite return value > int rc; > // Open the database > rc = sqlite3_open(argv[1], &db); > if(SQLITE_OK != rc) { > fprintf(stderr, "Can't open database %s (%i): %s\n", > argv[1], rc, sqlite3_errmsg(db)); > exit(1); > } > > char *exec_errmsg; > rc = sqlite3_exec(db, create_sql, NULL, NULL, &exec_errmsg); > if(SQLITE_OK != rc) { > fprintf(stderr, "Can't create table (%i): %s\n", rc, > sqlite3_errmsg(db)); > sqlite3_close(db); > exit(1); > } > sqlite3_stmt *insert_stmt; > rc = sqlite3_prepare_v2(db, insert_sql, -1, &insert_stmt, NULL); > if(SQLITE_OK != rc) { > fprintf(stderr, "Can't prepare insert statment %s (%i): > %s\n", insert_sql, rc, sqlite3_errmsg(db)); > sqlite3_close(db); > exit(1); > } > // Bind a block of zeros the size of the file we're going to > insert later > sqlite3_bind_zeroblob(insert_stmt, 1, filesize); > if(SQLITE_DONE != (rc = sqlite3_step(insert_stmt))) { > fprintf(stderr, "Insert statement didn't work (%i): > %s\n", rc, sqlite3_errmsg(db)); > exit(1); > } > > sqlite3_int64 rowid = sqlite3_last_insert_rowid(db); > printf("Created a row, id %i, with a blank blob size %i\n", > (int)rowid, (int)filesize); > > sqlite3_blob *blob; > rc = sqlite3_blob_open(db, "main", tablename, columnname, > rowid, 1, &blob); > if(SQLITE_OK != rc) { > fprintf(stderr, "Couldn't get blob handle (%i): %s\n", > rc, sqlite3_errmsg(db)); > exit(1); > } > > const int BLOCKSIZE = 1024; > int len; > void *block = malloc(BLOCKSIZE); > int offset = 0; > while(0 < (len = fread(block, 1, BLOCKSIZE, f))) { > if(SQLITE_OK != (rc = sqlite3_blob_write(blob, block, > len, offset))) { > fprintf(stderr, "Error writing to blob handle. > Offset %i, len %i, rc=%d\n", offset, len, rc); > exit(1); > } > offset+=len; > printf("... wrote block @off=%d\n", offset); > > // update that breaks the following sqlite3_blob_write > > if(SQLITE_OK != (rc = sqlite3_exec(db, "UPDATE testblob > SET fld=1", NULL, NULL, &exec_errmsg))) { > fprintf(stderr, "Error while updating the > integer field, error=%d\n", rc); > exit(1); > } > > } > > sqlite3_blob_close(blob); > > printf("Successfully wrote to blob\n"); > > free(block); > > fclose(f); > sqlite3_finalize(insert_stmt); > sqlite3_close(db); > return 0; > } > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
