samedi 23 mai 2015

iOS SQLite SELECT with UTF 8 characters

I've been reading all questions related to SQlite encoding with no success, so I'll try to ask about my specific case.

I have an iOS app with a SQLite database prepopulated. This SQLite has been converted from a MySQL database. Both the SQLite and MySQL databases have UTF8 enconding (the conversion process set the default sqlite enconding to UTF8)

If I open my sqlite database with Firefox SQlite Manager I can read all the content without problems, and all UTF8 chars are shown properly, so I guess everything was converted correctly.

But, when I try to perfom a select with UTF8 chars inside, the query fails.

This is the method where I perform the queries:

- (NSArray *)performQuery:(NSString *)query
{
    static sqlite3 *db;
    sqlite3_stmt *resultado;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"mydatabase.sqlite"];
    const char *dbpath = [writableDBPath UTF8String];
    if (sqlite3_open(dbpath, &db) == SQLITE_OK)
    {
        printf("%s\n", [query UTF8String]);
        int codigo = sqlite3_prepare_v2(db,[query UTF8String],[query length],&resultado,NULL);
        if (codigo == SQLITE_OK)
        {
            NSMutableArray *result = [NSMutableArray array];
            while (sqlite3_step(resultado) == SQLITE_ROW)
            {
                NSMutableArray *row = [NSMutableArray array];
                for (int i = 0; i < sqlite3_column_count(resultado); i++)
                {
                    int colType = sqlite3_column_type(resultado, i);
                    id value;
                    if (colType == SQLITE_TEXT)
                    {
                        value = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(resultado, i)];
                        if ([value isEqualToString:@"(null)"] || [value isEqualToString:@""] || (value == nil) || [value isKindOfClass:[NSNull class]])
                        {
                            value = @"";
                        }
                    }
                    else if (colType == SQLITE_INTEGER)
                    {
                        int col = sqlite3_column_int(resultado, i);
                        value = [NSNumber numberWithInt:col];
                    }
                    else if (colType == SQLITE_FLOAT)
                    {
                        double col = sqlite3_column_double(resultado, i);
                        value = [NSNumber numberWithDouble:col];
                    }
                    else if (colType == SQLITE_NULL)
                    {
                        value = [NSNull null];
                    }
                    else
                    {
                        NSLog(@"ERROR: DataBase - performQuery - Campo desconocido");
                    }
                    [row addObject:value];
                }
                [result addObject:row];
                row = nil;
            }
            sqlite3_finalize(resultado);
            sqlite3_close(db);
            resultado = nil;
            return result;
        }
        else
        {
            NSLog(@"ERROR [%d](%s): %@", codigo, sqlite3_errmsg(db), query);
        }
    }
    else
    {
        NSLog(@"ERROR: No se pudo abrir la base de datos");
    }
    sqlite3_close(db);
    db = nil;
    resultado = nil;
    return nil;
}

And this is the method where I call performQuery:

- (int) getIdForItem:(NSString *)item
{
    NSString *query = [NSString stringWithFormat:@"SELECT item_id FROM table_items WHERE item_name=\"%@\"", item];
    NSLog(@"QUERY:\n%@", query);
    NSArray *answer = [self performQuery:query];
    if([answer count] > 0)
        return [[[answer objectAtIndex:0] objectAtIndex:0] intValue];
    return -1;
}

If I make this call:

[self getIdForItem:@"Camión"];

This is the output:

QUERY:
SELECT item_id FROM table_items WHERE item_name="Camión"
SELECT item_id FROM table_items WHERE item_name="Camión"
ERROR [1](unrecognized token: ""Cami√≥n"): SELECT item_id FROM table_items WHERE item_name="Camión"

This error is produced in the method:

sqlite3_prepare_v2(db,[query UTF8String],[query length],&resultado,NULL)

So, Why is Camión interpreted as Cami√≥n in sqlite_prepare_v2? Both the query and the sqlite database are using UTF8, so I guess I'm missing something...

Aucun commentaire:

Enregistrer un commentaire