Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #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.
Reply With Quote
  #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
Reply With Quote
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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
  #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.

Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 05: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



All times are GMT -4. The time now is 02:37 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.