CODEWITHSUNDEEP
iossqlitenon-ascii-characters
gdm
gdm

Reputation: 7938

Store accented word into Sqlite - iOS

I coulnd not find a solution. I store data into a local SQLITE DB. Everything works except for accented words. As in the figure.

enter image description here

However, if I try to store accented word by using SqliteManager (Firefox plugin) everything works. In conclusion: when I store accented word by using my app, strange chars appear. I use the following code to write data (same for read). Basically, all strings are UTF8 encoded.

-(NSInteger)writeData:(NSDictionary* )data table:(NSString* )table
{
    sqlite3_stmt    *sqlStatement;
    NSMutableArray  *columns   = [[NSMutableArray alloc] init];
    NSMutableArray  *values    = [[NSMutableArray alloc] init];
    NSMutableDictionary *temp = [[NSMutableDictionary alloc] initWithDictionary:data];
    @try {
        assert([data count] != 0);
        if ([[data allKeys] count] == 0) return 1;


        [temp removeObjectForKey:@"id"];
        [columns addObjectsFromArray:[temp allKeys]];
        NSString        *cols      = [columns componentsJoinedByString:@","];
        NSMutableString *colNames  = [[NSMutableString alloc] initWithString:
                                      [NSString stringWithFormat:@"INSERT INTO %s (",[table UTF8String]]];

        [colNames appendString:cols];
        [colNames appendString:@")"];

        // VALUES FOR  INSERT
        [values addObjectsFromArray:[temp allValues] ];
        NSMutableString *s = [[NSMutableString alloc] init];
        for(int i = 0; i < [values count]; i++)
        {
            [s setString:[NSString stringWithFormat:@"%@",[values objectAtIndex:i]]];
            const char* currentValue = [s UTF8String];
            [values setObject:[NSString stringWithFormat:@"\"%s\"",currentValue] atIndexedSubscript:i];
        }
        NSString        *vals       = [values componentsJoinedByString:@","];
        NSMutableString *valNames   = [[NSMutableString alloc] initWithString:@" VALUES ("];
        [valNames appendString:vals];
        [valNames appendString:@")"];

        [colNames appendString:valNames];
        const char *sql = [colNames UTF8String];
#ifdef DEBUG
         NSLog(@"avvDB writeDATA insert string %@",colNames);
#endif

        if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
        {
            NSLog(@"Problem with prepare statement write %s",sqlite3_errmsg(db));
            return 0;

        }
        if (sqlite3_exec(db, sql, NULL, NULL, NULL) == SQLITE_OK)
        {
           // NSLog(@"Last id %llu %s",sqlite3_last_insert_rowid(db),sqlite3_errmsg(db));
        }

    }// end try
    @catch(NSException* e)
    {
        NSLog(@"Eccezione in write %@",[e reason]);
    }
    @finally {
        sqlite3_reset(sqlStatement);
        sqlite3_finalize(sqlStatement);
        sqlStatement = nil;
        return sqlite3_last_insert_rowid(db);
    }


}

Upvotes: 1

Views: 353

Answers (1)

Rob
Rob

Reputation: 437632

The %s operator does not support unicode characters. As the String Format Specifiers of the String Programming Guide says, it is "Null-terminated array of 8-bit unsigned characters."

Frankly, for other reasons, you shouldn't be using stringWithFormat anyway (what if one of the strings had a quotation mark in it ... your SQL statement would fail; you're even exposed to SQL injection attacks). You should be using a ? placeholder instead (with no quotation marks), and then call sqlite3_bind_text for each of the parameters you want to bind to the respective question mark (note, sqlite3_bind_xxx functions use a 1-based index, unlike sqlite3_column_xxx which use a 0-based index).

Upvotes: 2

Related Questions