Save and retrieve AttributedString to SQLite3

Question

I have a project in Objective-c in which I am trying to find a way of saving the attributedText from a UITextView to a SQLite3 table. My Project Target OS is 12.1. I am using an object called "MMItem" with a NSData property called "notesAttributed". In my viewController Class I am using NSKeyedArchiver to encode the AttributedText into a NSdata format then copying to the object property.

NSData *data = [NSKeyedArchiver archivedDataWithRootObject:self.itemNotes.attributedText requiringSecureCoding:YES error:Nil];
self.item.notesAttributed = data;

I then call a method in my model to save the object

NSString *resultStr = [self.meetingModel saveAttributedItemNote:item];

In the model I'm attempting to save the attributed string to a field in the Item table setup as type 'blob'

- (NSString *)saveAttributedItemNote:(MMItem *)item{
NSString *errorMessage;
NSString *sql;
NSInteger result = 0;

if (item) {
    //create blob encoded data for Attributed notes
    NSInteger itemID = item.itemID;
    
    sql = [NSString stringWithFormat:
    @"UPDATE Item set noteAttributed = %@ WHERE itemID = %ld",item.notesAttributed, (long)itemID];
    
    char *err;
    // open DB and save
    if ([self openDB]){
        //NSLog(@"%@", NSStringFromSelector(_cmd));
        result = sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err);
        sqlite3_close(db);
    }
    
    if (result != SQLITE_OK)
    {
        errorMessage = @"Error";
    }
    else
    {
        //NSLog(@"Table updated");
        errorMessage  = @"OK";
        [self saveAttributedItemNote:item];
    }
    
}
item = nil;
return errorMessage;

}

The SQL Execute statement fails with error 1.

I suspect I'm meant to insert the blob into the table using 'binding' and not an Execute statement but I just find how to put this all together using Objective-c. Any help would be great thanks.

Answer 1

With acknowledgement to the following post iOS SQLite Blob data is saving NULL I managed to get a working solution. It's apparent that to save a blob into an SQLite you must use a Prepare statement with a sqlite3_bind_blob to insert the blob parameters into the statement, then to use sqlite3_step to deploy it. This then allows the bytes and length parameters to also be passed into the statement, which I don't think can be done using the execute method I was originally trying. Here is the code that works perfectly.

- (NSString *)saveAttributedItemNote:(MMItem *)item{
NSString *errorMessage;

if (item) {
    //create blob encoded data for Attributed notes
    int itemID = (int)item.itemID;
    
    if ([self openDB]) {
        
        const char *insert_stmt = "UPDATE Item set notesAttributed = ? WHERE itemID = ?";
        
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(db, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
            sqlite3_bind_blob(statement, 1, item.notesAttributed.bytes, (int)item.notesAttributed.length, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 2, itemID);
            
            if (sqlite3_step(statement) == SQLITE_DONE) {
                errorMessage  = @"OK";
                //update successfull
            }else{
                const char *err = sqlite3_errmsg(db);
                NSString *error = [[NSString alloc] initWithUTF8String:err];
                NSLog(@"Update error ID:%@",error);
                errorMessage = @"Error";
            }
            sqlite3_finalize(statement);
        }else{
            errorMessage = @"Error";
            NSLog(@"Unable to prepare stsement %s: %s",insert_stmt, sqlite3_errmsg(db));
        }
        sqlite3_close(db);
    }

}
item = nil;
return errorMessage;

}

The following code then shows how the field was retrieved from sqlite. Column 11 is the blob.

if (sqlite3_prepare(db, [sql UTF8String], -1, &statement, nil)==SQLITE_OK)
    {
        //NSLog(@"SQL Select OK");
        
        while (sqlite3_step(statement)==SQLITE_ROW) {
            MMItem *item = [[MMItem alloc]init];
            item.itemID = sqlite3_column_int(statement, 0);
            item.topicID = sqlite3_column_int(statement, 1);
            item.sequenceID = sqlite3_column_int(statement, 2);
            char *description = (char *) sqlite3_column_text(statement, 3);
            if(description) item.itemDescription = [[NSString alloc]initWithUTF8String:description];
            char *notes = (char *) sqlite3_column_text(statement, 4);
            if(notes) item.notes = [[NSString alloc]initWithUTF8String:notes];
            char *actionBy = (char *) sqlite3_column_text(statement, 5);
            if(actionBy) item.actionBy = [[NSString alloc]initWithUTF8String:actionBy];
            char *requiredBy = (char *) sqlite3_column_text(statement, 6);
            if (requiredBy) item.requiredBy = [[NSString alloc]initWithUTF8String:requiredBy];
            item.completed = sqlite3_column_int(statement, 7);
            char *proposedBy = (char *) sqlite3_column_text(statement, 8);
            if (proposedBy) item.proposedBy = [[NSString alloc]initWithUTF8String:proposedBy];
            char *secondedBy = (char *) sqlite3_column_text(statement, 9);
            if (secondedBy) item.secondedBy = [[NSString alloc]initWithUTF8String:secondedBy];
            item.carried = sqlite3_column_int(statement, 10);
            NSData *attributedTextData = [[NSData alloc]initWithBytes:sqlite3_column_blob(statement,11) length:sqlite3_column_bytes(statement, 11)];
            item.notesAttributed = attributedTextData;
            
            
            [items addObject:item];
            item = nil;
        }
    }

Then in the ViewController, the following was used to take the NSData property and decode this for the UITextView (self.itemNotes)

self.itemNotes.attributedText = [NSKeyedUnarchiver unarchivedObjectOfClass:([NSAttributedString class]) fromData:self.item.notesAttributed error:&error];

Weird how just posting the question lead me to finding the right solution. Thanks to El Tomato for your help bud.