Chapter 2 Problem with the Database

andy · November 4th, 2013, 08:53 PM

Hello I am getting sqlite result code of 1 when trying to run my query against the database from DBAccess.m GetAllProducts. It does not have a problem opening the database and I have even tried copying the catalog.db and DBAccess.m files from the code download into my project however I am still getting the same result. Any help would be appreciated.

thepianoguy · November 7th, 2013, 09:58 AM

If you haven't solved your problem, send your non-working project to
[email protected]
and I should be able to point you in the right direction.

Bob

andy · November 10th, 2013, 06:18 AM

Thanks Bob. I have emailed my project code.

Andy

thepianoguy · November 10th, 2013, 02:53 PM

Your issue is most likely tied into how you added the Catalog.db file to your project.
If you dragged and dropped the file, the dialogue will present a couple of options:
Destination - with a "copy" check box - check this
Folders - radio buttons - Create Groups or Create references (groups is default)

Add to Targets - add a checkmark to Catalog

If you used File>Add Files the Add to Targets checkmark to Catalog is checked for you (or since I checked it in the past, maybe it is always checked for meâ¦)

The file must be added to the target.

If you run the program that you sent me with the following addition in the -initializeDatabase method

Code:

    NSString *path = [[NSBundle mainBundle]
                      pathForResource:@"catalog"
                      ofType:@"db"];
    
    // Open the database.
    NSLog(@"path is %@",path);////Added line

you will see that the path is NULL. For whatever reason sqlite3_open() doesn't return an error if the database does not exist. So since you made it to the "Opening database" comment you assumed that you actually had loaded the database. This was not true.

To fix this select your catalog.db file in the Project Navigator and hit "delete" selecting remove reference. Select File>Add Files and drill down in your project to select the catalog.db file. Make sure the Add to targets checkbox is selected. Click Add.
Now when you build and run the project will work. The only issue left to fix is in your -getAllProducts method. (DBAccess.old in the project you sent) Your â¦finalize is in the wrong place in the loop, so it will terminate after just one iteration. Move it down below the next closing brace '}'.

Code:

                       // Add the product to the products array
            [products addObject:product];
            
            // Finalize the statement to release its resources
////            sqlite3_finalize(statement);/////WRONG PLACE
            
        }
        sqlite3_finalize(statement);////RIGHT PLACE
    }
        else {
            NSLog(@"Problem with the database:");
            NSLog(@"%d", sqlResult);
        }
        return products;
    }

Bob

andy · November 12th, 2013, 02:25 AM

Hi Bob

I re-added the database file like you suggested however I am still not getting the desired results. I think the fault lies in the sqlite statement since that is what is failing the if statement and executing the else clause logging 'Problem with the database'. I can execute the sqlite statement in terminal successfully and have even tried a shorter query in the code but it still does not return true to the if statement:

Code:

- (NSMutableArray*) getAllProducts
{
    //  The array of products that we will create
    NSMutableArray *products = [[NSMutableArray alloc] init];

    //  The SQL statement that we plan on executing against the database
    const char *sql = "SELECT product.ID,product.Name, \
    Manufacturer.name,product.details,product.price,\
    product.quantityonhand, country.country, \
    product.image FROM Product,Manufacturer, \
    Country where manufacturer.manufacturerid=product.manufacturerid \
    and product.countryoforiginid=country.countryid";

    //  The SQLite statement object that will hold our result set
    sqlite3_stmt *statement;
    
    // Prepare the statement to compile the SQL query into byte-code
    int sqlResult = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);

    if ( sqlResult== SQLITE_OK) {
        // Step through the results - once for each row.
        while (sqlite3_step(statement) == SQLITE_ROW) {
            //  allocate a Product object to add to products array
            Product  *product = [[Product alloc] init];
            // The second parameter is the column index (0 based) in
            // the result set.
            char *name = (char *)sqlite3_column_text(statement, 1);
            char *manufacturer = (char *)sqlite3_column_text(statement, 2);
            char *details = (char *)sqlite3_column_text(statement, 3);
            char *countryOfOrigin = (char *)sqlite3_column_text(statement, 6);
            char *image = (char *)sqlite3_column_text(statement, 7);
            
            //  Set all the attributes of the product
            product.ID = sqlite3_column_int(statement, 0);
            product.name = (name) ? [NSString stringWithUTF8String:name] : @"";
            product.manufacturer = (manufacturer) ? [NSString
                                                     stringWithUTF8String:manufacturer] : @"";
            product.details = (details) ? [NSString stringWithUTF8String:details] : @"";
            product.price = sqlite3_column_double(statement, 4);
            product.quantity = sqlite3_column_int(statement, 5);
            product.countryOfOrigin = (countryOfOrigin) ? [NSString
                                                           stringWithUTF8String:countryOfOrigin] : @"";
            product.image = (image) ? [NSString stringWithUTF8String:image] : @"";
            
            // Add the product to the products array
            [products addObject:product];
 
        }
        // finalize the statement to release its resources
        sqlite3_finalize(statement);
    }
    else {
        NSLog(@"Problem with the database:");
        NSLog(@"%d",sqlResult);
    }
    
    return products;
    
}

Thanks
Andy

andy · November 12th, 2013, 04:17 PM

Hi Bob I tried re-adding the database file like you suggested but I'm still not getting the desired results. I have included the nslog to confirm the path in the application bundle and even tried copying the database out to the documents dir and confirmed it was there.
It seems as if the problem is with the sql statement because that is what is evaluating to false from the if condition:

Code:

- (NSMutableArray*) getAllProducts
{
    //  The array of products that we will create
    NSMutableArray *products = [[NSMutableArray alloc] init];

    //  The SQL statement that we plan on executing against the database
    const char *sql = "SELECT product.ID,product.Name, \
    Manufacturer.name,product.details,product.price,\
    product.quantityonhand, country.country, \
    product.image FROM Product,Manufacturer, \
    Country where manufacturer.manufacturerid=product.manufacturerid \
    and product.countryoforiginid=country.countryid";

    //  The SQLite statement object that will hold our result set
    sqlite3_stmt *statement;
    
    // Prepare the statement to compile the SQL query into byte-code
    int sqlResult = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);

    if ( sqlResult== SQLITE_OK) {
        // Step through the results - once for each row.
        while (sqlite3_step(statement) == SQLITE_ROW) {
            //  allocate a Product object to add to products array
            Product  *product = [[Product alloc] init];
            // The second parameter is the column index (0 based) in
            // the result set.
            char *name = (char *)sqlite3_column_text(statement, 1);
            char *manufacturer = (char *)sqlite3_column_text(statement, 2);
            char *details = (char *)sqlite3_column_text(statement, 3);
            char *countryOfOrigin = (char *)sqlite3_column_text(statement, 6);
            char *image = (char *)sqlite3_column_text(statement, 7);
            
            //  Set all the attributes of the product
            product.ID = sqlite3_column_int(statement, 0);
            product.name = (name) ? [NSString stringWithUTF8String:name] : @"";
            product.manufacturer = (manufacturer) ? [NSString
                                                     stringWithUTF8String:manufacturer] : @"";
            product.details = (details) ? [NSString stringWithUTF8String:details] : @"";
            product.price = sqlite3_column_double(statement, 4);
            product.quantity = sqlite3_column_int(statement, 5);
            product.countryOfOrigin = (countryOfOrigin) ? [NSString
                                                           stringWithUTF8String:countryOfOrigin] : @"";
            product.image = (image) ? [NSString stringWithUTF8String:image] : @"";
            
            // Add the product to the products array
            [products addObject:product];
 
        }
        // finalize the statement to release its resources
        sqlite3_finalize(statement);
    }
    else {
        NSLog(@"Problem with the database:");
        NSLog(@"%d",sqlResult);
    }
    
    return products;
    
}

I have successfully run the statement from sqlite in terminal and even tried a shorter query in the code but it still evaluates to false and outputs 'Problem with the Database'.
Andy

thepianoguy · November 13th, 2013, 01:43 AM

I successfully ran the project you sent me following what I outlined to you, both with your .m file and the author's. One step that I didn't mention that you should take, if you haven't, is to delete the the program from the simulator, and then do a clean and rebuild before running the project again.

Bob

andy · November 13th, 2013, 03:59 AM

Seems it must have been a problem with the way I created the sqlite database. I tried re-adding the one from the code download and it worked.

I did try this originally however I was not selecting the add to catalog project tick box as you suggested Bob so thanks again for your help here.

thepianoguy · November 13th, 2013, 09:28 AM

Glad you got it working. Good luck as you work your way through the book.

Bob