0

Working on a simple TimeTracker App. You start a timer, wait a certain time, stop the clock and save spent minutes to a SQLite3 database. All is working as expected, when I tap on save I get a confirmation that record was saved but I can't get the saved entries shown on the console. As seen in the screenshot below I have a show button which should show me all my saved entries in the console including timestamp but nothing happens when I tap the button.

I think the problem is related to the search query but I can't figure our whats wrong.

From my understanding the query is looking for the timestamp (column 0) and the spent minutes (column 1) but it doesn't work, please help.

   NSLog(@"Timestamp: %s - Timing: %s", sqlite3_column_text(statement, 0), sqlite3_column_text(statement, 1));

SQLite database is really simple, I only want to save one value > spent minutes. Maybe the database is not created correctly??

Database Creation:

- (void)viewDidLoad
{
    [super viewDidLoad];

    NSString *docsDir;
    NSArray *dirPaths;

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];

    databasePath = [[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent:@"timings.db"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath:databasePath] == NO) 
    {
        const char *dbPath = [databasePath UTF8String];

        if (sqlite3_open(dbPath, &timings) == SQLITE_OK) 
        {
            char *errormsg;
            const char *sql_stmt =
            "CREATE TABLE IF NOT EXISTS TIMINGS(ID INTEGER PRIMARY KEY AUTOINCREMENT, TIME INTEGER)";

            if (sqlite3_exec(timings, sql_stmt, NULL, NULL, &errormsg) != SQLITE_OK) 
            {
                self.statusLabel.text = @"Failed to create database";
            }
            sqlite3_close(timings);
        } 
        else 
        {
            self.statusLabel.text = @"Failed to open/create database.";
        }

    }
}

Show saved entries on console:

-(IBAction)showButton:(id)sender
{
    if (sqlite3_open([databasePath UTF8String], &timings) == SQLITE_OK)
    {
        NSString *queryStatement = [NSString stringWithFormat:@"SELECT TIMESTAMP, TIME FROM TIMINGS"];

        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(timings, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                NSLog(@"Timestamp: %s - Timing: %s", sqlite3_column_text(statement, 0), sqlite3_column_text(statement, 1));
            }
            sqlite3_finalize(statement);
            sqlite3_close(timings);
        }
    }
}

Screernshot

Improve this question
3
  • Are you sure showButton is getting called? Put an NSLog(@"in showButton") at the beginning of it to make sure. Commented Apr 1, 2012 at 2:47
  • Yes, showButton will be called. Put an NSLog at the beginning. Console Output: 2012-04-01 09:54:19.982 RangeManager[49985:f803] Show Button tapped Commented Apr 1, 2012 at 7:54
  • 1
    Try this [Link][1] [1]: stackoverflow.com/questions/9887785/… Commented Apr 1, 2012 at 15:31

3 Answers 3

Reset to default
1

Put NSLog(@"Error. '%s'", sqlite3_errmsg(database)); after each database operation completed. (ie; after, sqlite3_step, sqlite3_prepare etc). When you get data base, which was created by simulator, from Library/ApplicationSupport ....., you can open that using terminal and see the contents. Or using SQLite Manager plug in of mozilla you can open database and see contents graphically.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

NSLog(@"Error. '%s'", sqlite3_errmsg(database)); is very useful. thanks!
0

Do any rows get returned from your query? Open your sqlite database using a browser (http://sqlitebrowser.sourceforge.net/) and check the data is actually being saved.

Then rerun your sql in the browser to test the query, and finally put a breakpoint on -(IBAction)showButton:(id)sender and step thru

Improve this answer

5 Comments

Where is my SQLite3 database from iPhone Simulator stored? Can't find following folder: /User/Library/Application Support/iPhone Simulator/4.2/Applications/GUID/Documents > What I have is: /User/Library/Application Support but no subfolder called iPhone Simulator.
Finally found the folder. Had to remove hidden flag first via Terminal: chflags nohidden /users/[username]/library
Was able to verify with SQL browser that app does save data successfully. When I set a breakpoint on the IBAction showButton I see no strange behavior but I still get no rows listed at all with my query. Even an additional NSLog within the while is not shown on the console. When I put an NSLog BEFORE the while function I see an output.
Run your query "SELECT TIMESTAMP, TIME FROM TIMINGS" on your database using sqlite browser, does it return results?
Strange, when I run: SELECT TIMESTAMP, TIME FROM TIMINGS > I get: no such column: TIMESTAMP ... When I run: SELECT TIME FROM TIMINGS > I get my saved records listed (without the timestamp)
0

a friend of mine just realized that I made a big mistake, I totally forgot to CREATE the column 'TIMESTAMP' I'm trying to read out here. If you look at this line: "CREATE TABLE IF NOT EXISTS TIMINGS(ID INTEGER PRIMARY KEY AUTOINCREMENT, TIME INTEGER)"; You see that I have not created the timestamp column. Last but not least is TIMESTAMP a protected keyword in SQL you are not allowed to use. To save a timestamp I just created and renamed the column and now it's working. Thanks for all your help.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.