Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Professional iOS Database Application Programming, 2nd Edition
This is the forum to discuss the Wrox book Professional iOS Database Application Programming, 2nd Edition by Patrick Alessi; ISBN: 978-1-118-39184-6-6
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional iOS Database Application Programming, 2nd Edition section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 4th, 2013, 08:53 PM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Unhappy Chapter 2 Problem with the Database

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.
  #2 (permalink)  
Old November 7th, 2013, 09:58 AM
Friend of Wrox
Points: 1,671, Level: 16
Points: 1,671, Level: 16 Points: 1,671, Level: 16 Points: 1,671, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
Default

If you haven't solved your problem, send your non-working project to
ipadhelper@me.com
and I should be able to point you in the right direction.

Bob
The Following User Says Thank You to thepianoguy For This Useful Post:
andy (November 10th, 2013)
  #3 (permalink)  
Old November 10th, 2013, 06:18 AM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thanks Bob. I have emailed my project code.

Andy
  #4 (permalink)  
Old November 10th, 2013, 02:53 PM
Friend of Wrox
Points: 1,671, Level: 16
Points: 1,671, Level: 16 Points: 1,671, Level: 16 Points: 1,671, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
Default

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
  #5 (permalink)   Spam!  
Old November 12th, 2013, 02:25 AM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default re: catalog.db file

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
  #6 (permalink)  
Old November 12th, 2013, 04:17 PM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
  #7 (permalink)  
Old November 13th, 2013, 01:43 AM
Friend of Wrox
Points: 1,671, Level: 16
Points: 1,671, Level: 16 Points: 1,671, Level: 16 Points: 1,671, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
Default

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

Last edited by thepianoguy; November 13th, 2013 at 09:22 AM..
  #8 (permalink)  
Old November 13th, 2013, 03:59 AM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default Problem Fixed!

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.

  #9 (permalink)  
Old November 13th, 2013, 09:28 AM
Friend of Wrox
Points: 1,671, Level: 16
Points: 1,671, Level: 16 Points: 1,671, Level: 16 Points: 1,671, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
Default

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

Bob


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 11 - Problem Opening Database dbcook8 BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 15 February 2nd, 2009 08:41 AM
Problem to restore database in C#2005 database acmuralee MySQL 0 March 25th, 2008 04:42 AM
Database Problem /Chapter 03 Wrox United Example dynamicarray BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 3 February 12th, 2007 09:24 AM
Database for chapter 6 visceras BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 2 March 3rd, 2005 08:35 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.