Cannot insert json string to sqlite in App

Question

I'm trying to insert a json string to sqlite in my app. But it doesn't complete the insert query. I think there might be a problem with the format of the json string maybe? Because when I insert a regular string it works. Please let me know if you can see where I wen't wrong. Here is the code:

    NSData *imageData = UIImageJPEGRepresentation(self.chosenPhoto, 0.5f);
    NSString *base64StringOfImage = [imageData base64EncodedStringWithOptions:0];
    MCYProduct *newProduct = [[MCYProduct alloc] initWithProductName:self.textFieldName.text ProductDescription:self.textViewDescription.text ProductPrice:@([self.textFieldPrice.text integerValue]) ProductSalePrice:@([self.textFieldSalePrice.text integerValue]) ProductColors:self.chosenColors ProductStores:dictionaryStores ProductPhoto:self.chosenPhoto];
    NSDictionary *dictionaryProduct = @{@"name": newProduct.name, @"description:": newProduct.description, @"price": newProduct.price, @"salePrice:": newProduct.salePrice, @"colors": newProduct.colors, @"stores:": newProduct.stores, @"photo": base64StringOfImage};

    NSError *writeError = nil;
    NSData *jsonData = [NSJSONSerialization dataWithJSONObject:dictionaryProduct options:NSJSONWritingPrettyPrinted error:&writeError];
    NSString *jsonString = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding];
    NSLog(@"JSON Output: %@", jsonString);

    sqlite3 *sqlite;
    NSString *directoryDocuments;
    NSArray *directoryPath;
    NSString *databasePath;
    directoryPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    directoryDocuments = [directoryPath objectAtIndex:0];
    databasePath = [[NSString alloc] initWithString: [directoryDocuments stringByAppendingPathComponent:@"products.db"]];
    const char *dbPath = [databasePath UTF8String];
    sqlite3_stmt *statement;

    if (sqlite3_open(dbPath, &sqlite) == SQLITE_OK)
    {
        NSMutableString *mutableJsonString = [[NSMutableString alloc]initWithData:jsonData encoding:NSUTF8StringEncoding];
        [mutableJsonString replaceOccurrencesOfString:@"\""  withString:@"\\\"" options:NSCaseInsensitiveSearch range:NSMakeRange(0, [mutableJsonString length])];

        jsonString = mutableJsonString;

        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO PRODUCTS (JSON) VALUES ('%@')",jsonString];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(sqlite, insert_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"Product added");
        } else {
            NSLog(@"Failed to add Product");
        }
        sqlite3_finalize(statement);
        sqlite3_close(sqlite);
    }

Never use stringWithFormat to bind values into a query. Do it properly using the sqlite3_bind_xxx functions. — rmaddy
– rmaddy, Commented May 19, 2014 at 19:49
it worked like this: sqlite3_stmt *statement; if (sqlite3_open(dbPath, &sqlite) == SQLITE_OK) { NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO PRODUCTS (JSON) VALUES (?)"]; const char *insert_stmt = [insertSQL UTF8String]; sqlite3_prepare_v2(sqlite, insert_stmt, -1, &statement, NULL); sqlite3_bind_text(statement, 1, [jsonString UTF8String], -1, SQLITE_TRANSIENT); Thanks rmaddy! — Nader Neyzi
– Nader Neyzi, Commented May 19, 2014 at 22:06
Note that there's no point in the stringWithFormat in your comment above -- it's just wasted motion. Assign the string literal directly to insertSQL. — Hot Licks
– Hot Licks, Commented May 19, 2014 at 23:34

rmaddy · Accepted Answer · 2019-04-05 13:35:30Z

1

The answer is to use bind like this:

sqlite3_stmt *statement;
if (sqlite3_open(dbPath, &sqlite) == SQLITE_OK) { 
    NSString *insertSQL = @"INSERT INTO PRODUCTS (JSON) VALUES (?)"; 
    const char *insert_stmt = [insertSQL UTF8String]; 
    sqlite3_prepare_v2(sqlite, insert_stmt, -1, &statement, NULL); 
    sqlite3_bind_text(statement, 1, [jsonString UTF8String], -1, SQLITE_TRANSIENT);
}

edited Apr 5, 2019 at 13:35

rmaddy

319k44 gold badges548 silver badges591 bronze badges

answered May 20, 2014 at 16:50

Nader Neyzi

616 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot insert json string to sqlite in App

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related