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

November 4th, 2013, 08:53 PM
|
Registered User
|
|
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
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.
|

November 7th, 2013, 09:58 AM
|
Friend of Wrox
|
|
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
|
|
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
|
The Following User Says Thank You to thepianoguy For This Useful Post:
|
andy (November 10th, 2013)
|

November 10th, 2013, 06:18 AM
|
Registered User
|
|
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Thanks Bob. I have emailed my project code.
Andy
|

November 10th, 2013, 02:53 PM
|
Friend of Wrox
|
|
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
|
|
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
|

November 12th, 2013, 02:25 AM
|
Registered User
|
|
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
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
|

November 12th, 2013, 04:17 PM
|
Registered User
|
|
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
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
|

November 13th, 2013, 01:43 AM
|
Friend of Wrox
|
|
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
|
|
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..
|

November 13th, 2013, 03:59 AM
|
Registered User
|
|
Join Date: Nov 2013
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
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.

|

November 13th, 2013, 09:28 AM
|
Friend of Wrox
|
|
Join Date: Aug 2010
Posts: 298
Thanks: 1
Thanked 62 Times in 60 Posts
|
|
Glad you got it working. Good luck as you work your way through the book.
Bob
|
|
 |
|