SQLite not ordering data by date in iOS?

Question

I have a CITIES table in which I store cityname and modifieddate. I update the date of a city by current date/time combination and when I retrieve the city names again , I am ordering the data by DATE in DESCENDING order.Now I have tried the queries on Firefox SQLite Manager and they work fine.However I can't find what going wrong here?.

This is how I retrieve data

   //Retrieve data

-(NSMutableArray *)getCities{

const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &citiesDB) == SQLITE_OK)
{

    NSString *querySQL = [NSString stringWithFormat:@"SELECT cityname FROM CITIES ORDER BY modifieddate DESC"];

    const char *query_stmt = [querySQL UTF8String];
     NSMutableArray *resultArray = [[NSMutableArray alloc]init];
    if (sqlite3_prepare_v2(citiesDB,
                           query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString *name = [[NSString alloc] initWithUTF8String:
                              (const char *) sqlite3_column_text(statement, 0)];
            [resultArray addObject:name];

            NSLog(@"Result Array : %@",resultArray);

        }

        sqlite3_finalize(statement);
        sqlite3_close(citiesDB);
        return resultArray;


    }else{
            NSLog(@"No Cities Found");

        sqlite3_finalize(statement);
        sqlite3_close(citiesDB);
         return nil;
        }

}


return nil;

}

And this is how I update the dates for city names.

//save our data / Update the date

-(void)viewDataBaseForCity:(NSString *)cityName addedDate:(NSString *)dateAdded{
@try {
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &citiesDB)==SQLITE_OK)
    {

        NSString *querySQL = [NSString stringWithFormat:@"SELECT COUNT(CITYNAME) FROM CITIES WHERE CITYNAME ='%@'", cityName];

        const char *query_stmt = [querySQL UTF8String];

        if (sqlite3_prepare_v2(citiesDB, query_stmt, -1, &statement, NULL)!= SQLITE_OK)
        {

            NSAssert(0, @"Failed to open database");
            NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(citiesDB), sqlite3_errcode(citiesDB));

            sqlite3_finalize(statement);
            sqlite3_close(citiesDB);

        }
        else{

            if (sqlite3_step(statement)==SQLITE_ROW)
            {
                int count = sqlite3_column_int(statement, 0);
                NSLog(@"Found city count is : %d",count);
                if (count==0) {

                    //insert
                    NSString *insert_sql = [NSString stringWithFormat:@"INSERT INTO CITIES(cityname,modifieddate)VALUES (\'%@\',\'%@\')",cityName,dateAdded];
                    const char *insert_stmt = [insert_sql UTF8String];
                    sqlite3_prepare_v2(citiesDB, insert_stmt, -1, &statement, NULL);
                    if (sqlite3_step(statement)==SQLITE_DONE)
                    {
                        NSLog(@"Insert Successfull");
                        sqlite3_finalize(statement);
                        sqlite3_close(citiesDB);

                    }

                    else{
                        NSLog(@"Insert Failed"); //I also get database locked error here when tried multiple times. 
                        NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(citiesDB), sqlite3_errcode(citiesDB));
                        sqlite3_finalize(statement);
                        sqlite3_close(citiesDB);

                    }


                }else{

                    //update

                    NSString *querySQL = [NSString stringWithFormat:@"UPDATE CITIES set modifieddate ='%@' WHERE cityname='%@'", dateAdded, cityName];
                    const char *query_stmt = [querySQL UTF8String];
                    if (sqlite3_prepare_v2(citiesDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
                    {
                        NSLog(@"updated successfully");


                    }
                    sqlite3_finalize(statement);
                    sqlite3_close(citiesDB);

                }

            }else{

                NSLog(@"Not Found");

            }
        }


    }

}
@catch (NSException *exception) {

    NSLog(@"SQlite exception : %@",[exception reason]);

}
@finally {



  }


}

Now the date is stored as string in following format

-(NSString *)dateForCity{

NSDate *today = [NSDate date];
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"YYYY-MM-dd hh:mm:ss"];
NSString *dateString=[dateFormat stringFromDate:today];
return dateString;

}

Modified update query part.Which update only once and then gives database is locked error on next attempt.

   //update

    NSString *querySQL = [NSString stringWithFormat:@"UPDATE CITIES set modifieddate ='%@' WHERE cityname='%@'", dateAdded, cityName];
    NSLog(@"Update Query : %@",querySQL);
    const char *query_stmt = [querySQL UTF8String];
    if (sqlite3_prepare_v2(citiesDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
       if (sqlite3_step(statement)==SQLITE_DONE){
           NSLog(@"updated successfully");
           sqlite3_finalize(statement);
           sqlite3_close(citiesDB);
       }else{
             NSLog(@"%s Update failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(citiesDB), sqlite3_errcode(citiesDB));
             sqlite3_finalize(statement);
             sqlite3_close(citiesDB);
    }


 }

SQLite has no datatype as date. so it take date as TEXT. Try SELECT cityname FROM CITIES ORDER BY date(modifieddate) DESC. Let me know what u get. — CRDave, Oct 10 '14 at 11:10
Same result still.Order of cities don't change.Although it changes the order of cities in SQLITE Manager in Firefox (but I guess without considering time). — iSD, Oct 10 '14 at 11:19
Possibly unrelated but: Use `yyyy` (lowercase) instead of `YYYY`. The uppercase format can give the "wrong" year. See http://stackoverflow.com/questions/15133549/difference-between-yyyy-and-yyyy-in-nsdateformatter and http://stackoverflow.com/questions/4467173/nsdateformatter-dateformatfromtemplateoptionslocale-bug. — , Oct 10 '14 at 11:59
I checked both lowercase and uppercase.Both give the same date as `2014-10-10 05:46:24` — iSD, Oct 10 '14 at 12:17
@iSD, it won't work for all dates. Difference generally appears between the Dec-Jan period. — , Oct 10 '14 at 12:22
i have used (NewDate **DATETIME** NOT NULL) datatype for one of my databse table. the FORMATE of date is like **yyyy-MM-dd HH:mm:ss** ... and this is working perfectly in SELECT (Getting Results) Query ORDER BY NewDate. try with this and let me know the results... — M Zubair Shamshad, Oct 10 '14 at 12:30

score 2 · Accepted Answer · answered Oct 10 '14 at 13:02

2

You are preparing the query, but not executing it for your update. Your DB is never getting the updated dates.

Additionally, you should be using arguments instead of putting the values into SQL.

answered Oct 10 '14 at 13:02

Holly

5,270
1
24
27

This would have been obvious had the OP used sqlite3 in a command window to examine the actual DB in the simulator workspace. – Hot Licks Oct 10 '14 at 13:05
And, as a general rule, when you have an N-stage "pipe" and the data out is not as expected, don't assume that the error is in the very last stage (or the very first). Validate every stage. – Hot Licks Oct 10 '14 at 13:07
I got the update query working fine however it only updates once.And in subsequent attempts gives database is locked error. – iSD Oct 10 '14 at 14:03

SQLite not ordering data by date in iOS?

1 Answers1