Iphone SQLite Databse with german umlauts results in NULL value

Question

Hi guys,

I'm quite new to the Iphone development and after search for an answer for 3 hours now, I hope that you guys can give me a hand.

My problem is that I have a SQLite Database with german umlauts. Looking at it with a SQLite browser tool shows me that the data is stored with german umlauts, correctly. But selecting fields with german umlauts in it results in a NULL value. I'm already using "stringWithUTF8String", so I don't get the point where the problem is placed.

Here is my code:

-(void) readSearchFromDatabase {

searchFlag = YES;

// Setup the database object
sqlite3 *database;

// Init the SCode Array
searchSCodes = [[NSMutableArray alloc] init];

// Open the database from the users filessytem
if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {

    NSString *wildcard =@"%";

    // Setup the SQL Statement and compile it
            NSString *sql = [NSString stringWithFormat:@"SELECT * FROM scode WHERE ta LIKE '%@%@%@' OR descriptionde LIKE '%@%@%@' OR descriptionen LIKE '%@%@%@'", wildcard, searchBar.text, wildcard, wildcard, searchBar.text, wildcard, wildcard, searchBar.text, wildcard, wildcard, searchBar.text, wildcard];

    //Creating a const char SQL Statement especially for SQLite
    const char *sqlStatement = [sql UTF8String];

    sqlite3_stmt *compiledStatement;

    if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

        // Loop through the results and add them to the feeds array
        while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

            // Read the data from the result row
            NSString *aTa = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
            NSString *aReport = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
            NSString *aDescriptionDE = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
            NSString *aDescriptionEN = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];

                            //Results a NULL value
            NSLog(@"Output: %@", aDescriptionDE);

            // Create a new SCode object with the data from the database
            SCode *searchSCode = [[SCode alloc] initWithTa:aTa report:aReport descriptionDE:aDescriptionDE descriptionEN:aDescriptionEN];

            // Add the SCode object to the SCodes Array
            [searchSCodes addObject:searchSCode];

            [searchSCode release];
        }
    }
    // Release the compiled statement from memory
    sqlite3_finalize(compiledStatement);

}
sqlite3_close(database);

}

Answer 1

I can't help you, I know nothing about neither sqlite nor Iphones.

However, it's not as if there's nothing out there. Try Googling sqlite connection Umlaut, for example, or even better sqlite umlaut null.

I can't see any magic quick fix, and most examples are from other languages/platforms, but I'm sure if you dig your way through, you will come up with a solution that you can apply to your situation.

I am assuming you are a german speaker so this may give you at least the comfort that you're not the only one with the problem.

Answer 2

Hi, [excuse my language] have the same problem. But 'NOT NULL' don't give results. Next code perfect work with latin symbols, but never find something with letter 'Ü' in DB with thousands cities like Zürich.

NSMutableString *statementNSMS = [NSString stringWithString:
        @"select city from table_cities where upper(city) like '%Ü%'"];
const char *statement = [statementNSMS UTF8String];

int dbrc; // database return code
sqlite3_stmt    *dbps;
dbrc = sqlite3_prepare_v2 (db, statement, -1, &dbps, NULL); 
dbrc = sqlite3_step (dbps);

for(int i=0; dbrc == SQLITE_ROW; i++) {
    [resultCity insertObject:
         [NSString stringWithUTF8String:(char*)sqlite3_column_text (dbps, 0)]
                     atIndex:i];
    dbrc = sqlite3_step (dbps);
}
sqlite3_finalize(dbps);

have any ideas?