SQLite not adding objects to my Array

[andrewtheeditor]

I followed this tutorial to learn how to use a SQLite: iPhone Programming Tutorial – Creating a ToDo List Using SQLite Part 1 | iCodeBlog

I created a working todo app just as they did. Obviously I don't want their app, so I implemented it in my own and it does not work. Here is the original code from the tutorial that I seem to be having an issue with:

// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase {
    NSMutableArray *todoArray = [[NSMutableArray alloc] init];
    self.todos = todoArray;
    [todoArray release];
    // The database is stored in the application bundle. 
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"Todos.sqlite"];
    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
        // Get the primary key for all books.
        const char *sql = "SELECT pk FROM todos";
        sqlite3_stmt *statement;
        // Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
        // The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.        
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
            // We "step" through the results - once for each row.
            while (sqlite3_step(statement) == SQLITE_ROW) {
                // The second parameter indicates the column index into the result set.
                int primaryKey = sqlite3_column_int(statement, 0);
                // We avoid the alloc-init-autorelease pattern here because we are in a tight loop and
                // autorelease is slightly more expensive than release. This design choice has nothing to do with
                // actual memory management - at the end of this block of code, all the book objects allocated
                // here will be in memory regardless of whether we use autorelease or release, because they are
                // retained by the books array.
                Todo *td = [[Todo alloc] initWithPrimaryKey:primaryKey database:database];
				[todos addObject:td];
				NSLog(@"todos %@", todos);
                [td release];
            }
        }
        // "Finalize" the statement - releases the resources associated with the statement.
        sqlite3_finalize(statement);
    } else {
        // Even though the open failed, call close to properly clean up resources.
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
}

I put a NSLog in to show it is getting an array. I also put an NSLog in the Todo.m file to spit out the text that will be put into the array. Here is what the console gives me:

2010-09-16 15:50:23.451 todo[7980:207] A todo was recieved called Todo 1
2010-09-16 15:50:23.453 todo[7980:207] todos (
    "<Todo: 0x5d443d0>"
)
2010-09-16 15:50:23.458 todo[7980:207] A todo was recieved called Todo 2
2010-09-16 15:50:23.459 todo[7980:207] todos (
    "<Todo: 0x5d443d0>",
    "<Todo: 0x5903910>"
)
2010-09-16 15:50:23.461 todo[7980:207] A todo was recieved called Todo 3
2010-09-16 15:50:23.462 todo[7980:207] todos (
    "<Todo: 0x5d443d0>",
    "<Todo: 0x5903910>",
    "<Todo: 0x590fad0>"
)
2010-09-16 15:50:23.463 todo[7980:207] A todo was recieved called Todo 4
2010-09-16 15:50:23.463 todo[7980:207] todos (
    "<Todo: 0x5d443d0>",
    "<Todo: 0x5903910>",
    "<Todo: 0x590fad0>",
    "<Todo: 0x5910a80>"
)

Now, here is the code from my application. The only thing that should be different, are the variables.

// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase {
    NSMutableArray *attractionArray = [[NSMutableArray alloc] init];
    self.attractions = attractionArray;
    [attractionArray release];
    // The database is stored in the application bundle. 
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"ParkTracker.db"];
    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
        // Get the primary key for all books.
        const char *sql = "SELECT pk FROM attractions";
        sqlite3_stmt *statement;
        // Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
        // The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.        
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
            // We "step" through the results - once for each row.
            while (sqlite3_step(statement) == SQLITE_ROW) {
                // The second parameter indicates the column index into the result set.
                int primaryKey = sqlite3_column_int(statement, 0);
                // We avoid the alloc-init-autorelease pattern here because we are in a tight loop and
                // autorelease is slightly more expensive than release. This design choice has nothing to do with
                // actual memory management - at the end of this block of code, all the book objects allocated
                // here will be in memory regardless of whether we use autorelease or release, because they are
                // retained by the books array.
                Attraction *attr = [[Attraction alloc] initWithPrimaryKey:primaryKey database:database];
                [attractions addObject:attr];
				NSLog(@"attractions %@", attractions);
                [attr release];
            }
        }
        // "Finalize" the statement - releases the resources associated with the statement.
        sqlite3_finalize(statement);
    } else {
        // Even though the open failed, call close to properly clean up resources.
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
}

And here is my console with the NSLog statements in the same places:

2010-09-16 16:00:32.077 ParkTracker[8038:207] An attractionName was recieved called uno
2010-09-16 16:00:32.079 ParkTracker[8038:207] attractions (
    (null)
)
2010-09-16 16:00:32.080 ParkTracker[8038:207] An attractionName was recieved called dos
2010-09-16 16:00:32.081 ParkTracker[8038:207] attractions (
    (null),
    (null)
)
2010-09-16 16:00:32.081 ParkTracker[8038:207] An attractionName was recieved called tres
2010-09-16 16:00:32.082 ParkTracker[8038:207] attractions (
    (null),
    (null),
    (null)
)
2010-09-16 16:00:32.083 ParkTracker[8038:207] An attractionName was recieved called quatro
2010-09-16 16:00:32.083 ParkTracker[8038:207] attractions (
    (null),
    (null),
    (null),
    (null)
)

I am pretty certain this is the line that isnt functioning:

Attraction *attr = [[Attraction alloc] initWithPrimaryKey:primaryKey database:database];

but I cant figure out why. The NSLog in my initWithPrimaryKey statement shows it should be sending a result.

[JDave]

I am not sure what you are retreiving from the database.

Your query is:

SELECT pk FROM attractions

so I am assuming you are pulling column pk from table attractions, from database Todos.sqlite

since you specified only a single column from your table named attractions, you should only have a pk returned in column 0 (counting 0, 1, 2,....)

your statement to retrieve pk is this:

int primaryKey = sqlite3_column_int(statement, 0);

which is implying that you are retrieving an int. I bet this is where you are going wrong. However, I am not sure what you are doing with that int value, as it is likely not of much use without additional data... but you are doing something with NSDictionary, so if you are looking something up via that id, then I guess it makes sense...

I would recommend running some queries using SQLite Manager (it is a firefox add-on) or from the OSX terminal, as both are great for getting the query statement correct.

BTW, if pk is a string, do this:

NSString *td = [NSString stringWithUTF8String:(char *) sqlite3_column_text(statement, 0)];
NSLog(@"Just read in: %@", td);

I am not sure what you are doing with the NSDictionary code... if this does not help, please post the database/table structure of attractions.

Good luck.

[Kalimba]

Hmm.. the 'attr' objects are most certainly valid when they're added to the array -- if they were somehow nil after the alloc/init, you would be getting an exception when adding them to the array. Also, when the array contents are logged, it appears to display the expected number of elements, so that's promising yet puzzling.

Does your Attraction class by any chance have a 'description' method?

[andrewtheeditor]

Thank you JDave. I appreciate your in depth response. I think something might be confusing about this. These are two separate projects I have given code from.

To answer the question you asked though: pk is an integer (the primaryKey) and is used to reference which entry in the table I want

Originally Posted by JDave

so I am assuming you are pulling column pk from table attractions, from database Todos.sqlite

Yes, I am. Kind of. These are two separate databases. Technically I am pulling pk from table attractions, from database ParkTracker.db in one and pk from table todos, from database Todos.sqlite from the other.

Originally Posted by JDave

since you specified only a single column from your table named attractions, you should only have a pk returned in column 0 (counting 0, 1, 2,....)

your statement to retrieve pk is this:

int primaryKey = sqlite3_column_int(statement, 0);

which is implying that you are retrieving an int. I bet this is where you are going wrong. However, I am not sure what you are doing with that int value, as it is likely not of much use without additional data... but you are doing something with NSDictionary, so if you are looking something up via that id, then I guess it makes sense...

I would recommend running some queries using SQLite Manager (it is a firefox add-on) or from the OSX terminal, as both are great for getting the query statement correct.

I receive pk and set it as primaryKey. I then use that to get each of the entries in the database. I'm sorry I cant explain this better. I got all that from the tutorial and I am simple explaining what I understand from what I read. I know it works when done correctly, as the tutorial works.

[andrewtheeditor]

Originally Posted by Kalimba

Hmm.. the 'attr' objects are most certainly valid when they're added to the array -- if they were somehow nil after the alloc/init, you would be getting an exception when adding them to the array. Also, when the array contents are logged, it appears to display the expected number of elements, so that's promising yet puzzling.

Haha, my sentiments exactly! I'm glad that I'm not crazy.

Originally Posted by Kalimba

Does your Attraction class by any chance have a 'description' method?

Why yes, it does! I plan to pass more than just attractionName later, I plan to pass a few other strings and one of them will be 'description.' I set those up already, but I haven't used them yet. Is that causing this issue?

[andrewtheeditor]

Originally Posted by Kalimba

Does your Attraction class by any chance have a 'description' method?

Okay, please explain! I removed description and now my console shows the proper items are being added to the Array! The app still isn't displaying the attractionName in the table view cell's as I asked it too, but the database is working! How did you do that?

[Kalimba]

Originally Posted by andrewtheeditor

Okay, please explain! I removed description and now my console shows the proper items are being added to the Array! The app still isn't displaying the attractionName in the table view cell's as I asked it too, but the database is working! How did you do that?

The 'description' method part of the NSObject protocol, and when a container (array, dictionary, etc.) is "NSLog'ed", that method is called on each object in the container, to obtain its description. Since you had overridden the method (with code that apparently returned a nil/NULL/0 value), the description was displaying as "(null)".

As far as how I knew that... decades of programming/debugging experience, intuition, examination of the symptoms, ... who knows?

[JDave]

I am still confused about the data you are extracting and the final form (int, string, blob, etc) of the data. But when you have 2 tables to pull from with 'linked' id's, then perhaps a JOIN or UNION command is what you want in your SELECT query. Sounds like you are on the right track.

FYI, your query could be returning a NULL; you can make your SELECT statement more robust by changing it to:

SELECT pk FROM attractions WHERE pk IS NOT NULL

alas if this is all working, then the way you are checking in your NSLog is likely incorrect.

Does the attractions array hold data that is not in a NSString/string? Have a look at how your Attraction class is configured to store values.