1

I'm curious in the differences that I've come across in using both an iOS Simulator/Device and the sqlite3 command line app that comes packaged with Mac OSX.

On a different machine (my Windows box), I created a sqlite database out of a text file. Here's the create statement for the SQLite file.

CREATE TABLE IF NOT EXISTS GeocodeData (ZipCode TEXT, City TEXT COLLATE NOCASE, State TEXT COLLATE NOCASE, Latitude REAL, Longitude REAL, PRIMARY KEY(ZipCode, City, State) ON CONFLICT INGORE);

I'm using the COLLATE NOCASE as I found on this SO question to have case insensitive searches.

When I move the DB to my Mac, running the sqlite3 app in the terminal, I can run the following query: SELECT * FROM GeocodeData WHERE City = 'elyria' AND State = 'oh'.

44035|Elyria|OH|41.3724|-82.1051
44036|Elyria|OH|41.4015|-82.0771

Running this statement returns 2 results from my database. Awesome.

Moving to the iPad app I'm creating, I'm using FMDatabase to wrap access to the Sqlite database.

Here's the code that accesses it.

+(CLLocationCoordinate2D)getCoordinateForFilter:(GeocodingFilter *)filter
{
    __block CLLocationCoordinate2D coordinate;
    FMDatabaseQueue *queue = [self databaseQueue];
    [queue inDatabase:^(FMDatabase *db)
    {
        //Determine if we're using zipcode or city/state.
        NSMutableString *query = [[NSMutableString alloc] initWithString:SELECT_STATEMENT];
        NSMutableDictionary *params = [NSMutableDictionary dictionary];
        if(![NSString isNilOrWhitespace:filter.zipCode])//Zipcode logic
        {
            [query appendFormat:WHERE_CLAUSE, @"ZipCode = :zipCode "];
            params[@"zipCode"] = filter.zipCode;
        }
        else
        {
            NSString *clause = nil;
            if(![NSString isNilOrWhitespace:filter.city])
            {
                clause = [NSString stringWithFormat:WHERE_CLAUSE, @"City = :city"];
                params[@"city"] = filter.city;
            }
            if (![NSString isNilOrWhitespace:filter.state])
            {
                if(clause)
                    clause = [NSString stringWithFormat:@"%@ AND", clause];
                clause = [NSString stringWithFormat:@"%@ State = :state", clause];
                params[@"state"] = filter.state;
            }
            [query appendString:clause];
        }
        FMResultSet *set = [db executeQuery:query withParameterDictionary:params];
        NSMutableArray *results = [NSMutableArray array];
        while ([set next])
        {
            double latitude = [set doubleForColumn:@"Latitude"];
            double longitude = [set doubleForColumn:@"Longitude"];
            [results addObject:[[CLLocation alloc] initWithLatitude:latitude longitude:longitude]];
        }

        [set close];
        [db close];

        __block double avgLat = 0.;
        __block double avgLong = 0.;
        [results enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
            CLLocation *loc = obj;
            avgLat += loc.coordinate.latitude;
            avgLong += loc.coordinate.longitude;
        }];

        coordinate = CLLocationCoordinate2DMake(avgLat/(double)results.count, avgLong/(double)results.count);
    }];

    return coordinate;
}

This runs the exact same query that I used in the sqlite3 command line, yet returns 0 results every time.

Now, I can fix the issue by changing these 4 lines of code.

clause = [NSString stringWithFormat:WHERE_CLAUSE, @"City LIKE :city"];
params[@"city"] = [NSString stringWithFormat:@"%%%@%%", filter.city];

and

clause = [NSString stringWithFormat:@"%@ State LIKE :state", clause];
params[@"state"] = [NSString stringWithFormat:@"%%%@%%", filter.state];

but I explicitly created the table to use COLLATE NOCASE in the create statement. Why doesn't this work?

EDIT: Apparently I need to re-iterate the problem. The problem is NOT the LIKE query!

I want to know why this query: SELECT * FROM GeocodeData WHERE City = 'elyria' AND State = 'oh' on the command line returns 2 results, while running THIS EXACT SAME QUERY in an iOS environment with FMDatabase return 0 results.

Community
  • 1
  • 1
DavidAndroidDev
  • 2,371
  • 3
  • 25
  • 41
  • Have you read the documentation at sqlite.org? – Hot Licks Jan 13 '14 at 21:08
  • I'm not sure what reading the documentation will give me. I've shown a case where the sqlite3 command line (which is version 3.7.13) returns a set of results, and then running on the device on iOS 7 (which runs the exact same version, gotten from this answer, http://stackoverflow.com/questions/14288128/what-version-of-sqlite-does-ios-provide), gives me an empty result. The only thing I could assume is there are implementation differences in the two, and I guess that's what I'm asking. Why is there a difference between these exact same version with the exact same query? – DavidAndroidDev Jan 13 '14 at 21:58
  • So you've memorized [this](http://www.sqlite.org/optoverview.html#like_opt)? – Hot Licks Jan 13 '14 at 22:08
  • I'm not sure you understand what I'm asking. I understand why `LIKE` is my solution. This is a question about the environments that the database lives in. Why is my query on iOS giving me different results than on the command line? Its the exact same query on the exact same database, running the exact same version of sqlite. – DavidAndroidDev Jan 14 '14 at 14:05
  • With the same pragmas? – Hot Licks Jan 14 '14 at 16:50
  • I checked the value of a couple `PRAGMA`'s, namely the 'collation_list' since it seemed the most relevant, but all of them matched for the dozen or so I checked. – DavidAndroidDev Jan 14 '14 at 19:48
  • Did you check `case_sensitive_like`? – Hot Licks Jan 14 '14 at 19:49
  • I reitereated the question at the end. I think you misinterpreted what I was asking. – DavidAndroidDev Jan 14 '14 at 20:26
  • Have you tried `SELECT * FROM GeocodeData WHERE City = 'elyria' COLLATE NOCASE AND State = 'oh' COLLATE NOCASE`? – Hot Licks Jan 14 '14 at 20:46
  • So, that DOES fix the problem, but that's side stepping the issue. The collation is given during the creation of the table. Why is it that its only working when included in the query? – DavidAndroidDev Jan 14 '14 at 20:58
  • Are you sure the column-level specifications are working at all? Do they affect ORDER BY? – Hot Licks Jan 14 '14 at 21:03
  • Yea. This is a pure programmer error. I'm retarded. – DavidAndroidDev Jan 14 '14 at 21:29
  • If you could, post an answer stating that there should be zero differences in the environments, since there's not. It must have escaped my mind in the fact that I must not have copied the latest version of the database to my project, and just made a horrible assumption that the copy I had in my project was the same that I had sent from my other computer. In short, my device queries were working on an old version of the DB before I added the collation definitions in the create statement. Sigh. It's always the simple things that get you. – DavidAndroidDev Jan 14 '14 at 21:33
  • Well, when it comes to SQLite I'm never confident that there will be zero differences -- I know I've run across a few (though can't remember what they are). – Hot Licks Jan 14 '14 at 21:48
  • Well, I'd still like to give you credit for as least assisting me. – DavidAndroidDev Jan 15 '14 at 01:21

1 Answers1

1

I'll admit that the SQLite documentation is poorly organized in places, but it's about as accurate and precise as one can hope for, for an open source project.

Consulting the documentation suggested that you should not have had the problem you described, leading to the eventual conclusion that something was not quite as you described it.

Hot Licks
  • 47,103
  • 17
  • 93
  • 151