Problem using a SQLite database on Xcode

Question

I'm quite new to Xcode and I have a huge problem with a tutorial I'm working on.

I'm trying to write a simple shopping list, using a sql database.

Actually i finished with the project. I triple checked every single line of code, but for some reason it doesn't want to show me the content of the DB nor write something inside.

Really I have no idea what's wrong with the code... Writing in the DB:

-(IBAction)addShoppingListItem:(id)sender {
//apriamo il database
if (([itemNameField.text length] == 0) || ([priceField.text length] == 0) || ([priceField.text doubleValue] == 0.0)) {
    return;
}
sqlite3 *db;
int dbrc; //Codice di ritorno del database (database return code)
DatabaseShoppingListAppDelegate *appDelegate = (DatabaseShoppingListAppDelegate*) [UIApplication sharedApplication].delegate;
const char *dbFilePathUTF8 = [appDelegate.dbFilePath UTF8String];
dbrc = sqlite3_open(dbFilePathUTF8, &db);
if (dbrc) {
    NSLog(@"Impossibile aprire il Database!");
    return;
}
//database aperto! Inseriamo valori nel database.
sqlite3_stmt *dbps; //Istruzione di preparazione del database
NSString *insertStatementsNS = [NSString stringWithFormat: @"insert into \"shoppinglist\" (item, price, groupid, dateadded) values (\"%@\", %@, %d, DATETIME('NOW'))", name_field, price_field, [groupPicker selectedRowInComponent:0]];
const char *insertStatement = [insertStatementsNS UTF8String];
dbrc = sqlite3_prepare_v2(db, insertStatement, -1, &dbps, NULL);
dbrc = sqlite3_step(dbps);
//faccio pulizia rilasciando i database
sqlite3_finalize(dbps);
sqlite3_close(db);

// Pulisci i campi e indica successo nello status
statusLabel.text = [[NSString alloc] initWithFormat: @"Aggiunto %@", itemNameField.text];
statusLabel.hidden = NO;
itemNameField.text = @"";
priceField.text = @"";
}

Loading from the database:

-(void)loadDataFromDb {
//apriamo il database
sqlite3 *db;
int dbrc; //Codice di ritorno del database (database return code)
DatabaseShoppingListAppDelegate *appDelegate = (DatabaseShoppingListAppDelegate *) [UIApplication sharedApplication].delegate;
const char *dbFilePathUTF8 = [appDelegate.dbFilePath UTF8String];
dbrc = sqlite3_open(dbFilePathUTF8, &db);
if (dbrc) {
    NSLog(@"Impossibile aprire il Database!");
    return;
}
//database aperto! Prendiamo i valori dal database.
sqlite3_stmt *dbps; //Istruzione di preparazione del database
NSString *queryStatementNS = @"select key, item, price, groupid, dateadded from shoppinglist order by dateadded";
const char *queryStatement = [queryStatementNS UTF8String];
dbrc = sqlite3_prepare_v2(db, queryStatement, -1, &dbps, NULL);

//Richiamo la funzione sqlite3_step() finché ho righe nel database
while ((dbrc = sqlite3_step(dbps)) == SQLITE_ROW) {
    int primaryKeyValueI = sqlite3_column_int(dbps, 0);
    NSNumber *primaryKeyValue = [[NSNumber alloc] initWithInt:primaryKeyValueI];
    NSString *itemValue = [[NSString alloc] initWithUTF8String:(char*) sqlite3_column_text(dbps, 1)];
    double priceValueD = sqlite3_column_double(dbps, 2);
    NSNumber *priceValue = [[NSNumber alloc] initWithDouble:priceValueD];
    int groupValueI = sqlite3_column_int(dbps, 3);
    NSNumber *groupValue = [[NSNumber alloc] initWithInt:groupValueI];
    NSString *dateValueS = [[NSString alloc] initWithUTF8String:(char*) sqlite3_column_text(dbps, 4)];
    NSDate *dateValue = [dateFormatter dateFromString: dateValueS];

    NSMutableDictionary *rowDict = [[NSMutableDictionary alloc] initWithCapacity:5];
    [rowDict setObject:primaryKeyValue forKey: ID_KEY];
    [rowDict setObject:itemValue forKey: ITEM_KEY];
    [rowDict setObject:priceValue forKey: PRICE_KEY];
    [rowDict setObject:groupValue forKey: GROUP_ID_KEY];
    [rowDict setObject:dateValue forKey: DATE_ADDED_KEY];
    [shoppingListItems addObject: rowDict];

    //rilascio tutti gli elementi
    [dateValue release];
    [primaryKeyValue release];
    [itemValue release];
    [priceValue release];
    [groupValue release];
    [rowDict release];
}
}

For the brave who wants to check all the project (there is just another class) here there is the link with all my work. http://www.mediafire.com/?qxfde723r29nhtb

Thanks in advance.

I would like to suggest that you should use core data instead of this low level sqlite code. In fact core dat does lot of thing for you like managing cache and other database related stuff. And also it's very simple to learn. — Darshan Prajapati
– Darshan Prajapati, Commented Aug 14, 2011 at 6:31
I really appreciate your suggestion, and I'll learn it asap. But now i must work with sql, cos my teacher assigned me a work on it. — Oiproks
– Oiproks, Commented Aug 14, 2011 at 17:57

user478681 · Accepted Answer · 2011-08-14 08:47:34Z

1

You have too many mistakes in your code. Let's briefly look at them:

1) In your application delegate you place db initialization code in applicationDidFinishLaunching method, but according to Apple's manuals:

This method is used in earlier versions of iOS to initialize the application and prepare it to run. In iOS 3.0 and later, you should use the application:didFinishLaunchingWithOptions: instead.

so, I just moved initializeDb method into - (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions

2) In ListViewController you have a method loadDataFromDb but I didn't find its usage. You never call this method. In the same controller you have viewWillAppear method that I used to initialize your shoppingListItems array that will be shown in tableView as follows:

-(void)viewWillAppear:(BOOL)animated {
    shoppingListItems = [[NSMutableArray alloc] init];
    [self loadDataFromDb];
    [tableView reloadData];
}

tableView is a outlet to UITableView control that you place in your nib file. I added its declaration in ListViewController.h:

@interface ListViewController : UIViewController {
    UITableViewCell *nibLoadedCell;
    IBOutlet UITableView *tableView;
}
@property(nonatomic, retain) IBOutlet UITableViewCell *nibLoadedCell;

@end

and bind in interface builder with the actual control.

3) AddItemViewController's method addShoppingListItem didn't work because didn't pass this condition:

if (([itemNameField.text length] == 0) || ([priceField.text length] == 0) || ([priceField.text doubleValue] == 0.0)) {
        return;
    }

You didn't bind your outlets in interface builder for itemNameField, priceField, groupPicker.

By fixing this I got working solution, but actually I didn't check memory usage and suppose you have also some problem in memory management here.

answered Aug 14, 2011 at 8:47

user478681

8,3294 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oiproks Over a year ago

WoW man! You are amazing! Thanks a lot for your help! Seems like I truly have to improve my knowledge of xcode (English language too). Just one more question: do you think it would be easy to add a photo of the item directly from the camera to the DB? I mean, I'll create a new field with where to store the path of the image saved in the Documents folder. Thanks again for your help! Finally now i can go to sleep after 2 days. I owe you a beer!

user478681 Over a year ago

:) Everything is possible (almost everything:). You can store the photo even directly in DB using BLOB datatype. But actually it's better to learn some CoreData staff as recommended by Darshan in comment to your question. The same functionality with CoreData can be implemented with minimum code writing.

Collectives™ on Stack Overflow

Problem using a SQLite database on Xcode

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related