StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POObjective C - iPhone - Inserting record into sqlite database not working as expected
text
Body
copied!<p>I use the below code to create and read my database in the app delegate:</p> <pre><code>- (void) checkAndCreateDatabase { //Check if the database has been saved to the users phone, if not then copy it over BOOL l_Success; //Create a file manager object, we will use this to check the status //of the databse and to copy it over if required NSFileManager *l_FileManager = [NSFileManager defaultManager]; //Check if the database has already been created in the users filesystem l_Success = [l_FileManager fileExistsAtPath:m_DatabasePath]; //If the database already exists then return without doing anything if(l_Success) return; //If not then proceed to copy the database from the application to the usrs filesystem //Get the path to the database in the application package NSString *l_DatabasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:m_DatabaseName]; //Copy the database from the package to the usrrs filesystem [l_FileManager copyItemAtPath:l_DatabasePathFromApp toPath:m_DatabasePath error:nil]; } - (void) readProductsFromDatabase { //Setup the database object sqlite3 *l_Database; //Init the products array m_Products = [[NSMutableArray alloc] init]; NSLog(@"%@", m_DatabasePath); //Open the database from the users filessystem if(sqlite3_open([m_DatabasePath UTF8String], &l_Database) == SQLITE_OK) { //Set-up the SQL statement and compile it for faster access const char *sqlStatement = "select *from products"; sqlite3_stmt *compiledStatement; if(sqlite3_prepare_v2(l_Database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) { //Loop through the results and add them to the feeds array while(sqlite3_step(compiledStatement) == SQLITE_ROW) { //Read the data from the results row NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)]; NSLog(@"%@",[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)]); NSString *aCategory = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)]; NSString *aCalories = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)]; NSString *aFat = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)]; NSString *aSaturates = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)]; NSString *aSugar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)]; NSString *aFibre = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 7)]; NSString *aSalt = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)]; NSString *aImageURL = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)]; //Create a new animal object with the data from the database Product *l_Product = [[Product alloc] initWithName:aName category:aCategory calories:aCalories fat:aFat saturates:aSaturates sugar:aSugar fibre:aFibre salt:aSalt imageURL:aImageURL ]; //Add the animal object to the animals array [m_Products addObject:l_Product]; } } //Release the compiled statement from memory sqlite3_finalize(compiledStatement); } sqlite3_close(l_Database); } </code></pre> <p>The above code is called as follows:</p> <pre><code>- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions { //Set-up some globals m_DatabaseName = @"ProductDatabase.sql"; //Get the path to the documents directory and append the databaseName NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; m_DatabasePath = [documentsDirectory stringByAppendingPathComponent:@"ProductDatabase.sql"]; //Execute the "checkAndCreateDatabase" function [self checkAndCreateDatabase]; //Query the databse for all animal records and construct the "animals" array [self readProductsFromDatabase]; ..unrelated code.. return YES; } </code></pre> <p>I then populate a table view using the following code:</p> <pre><code>- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath { NSLog(@"Cell for row"); static NSString *CellIdentifier = @"Cell"; UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier]; if (cell == nil) { cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier]; } if([self.productType isEqualToString:@"ALL"]){ Product *product = (Product *) [appDelegate.m_Products objectAtIndex:indexPath.row]; cell.textLabel.text = product.name; // Configure the cell. return cell; } else { //Instead of using appDelegate.products use the new array that will be filled //by the numberOfReowsInSection method Product *product = (Product *)[[m_AlphabetDictionary objectAtIndex:indexPath.section] objectAtIndex:indexPath.row]; cell.textLabel.text = product.name; // Configure the cell. return cell; } } </code></pre> <p>My problem arises when I try to insert some test data as follows:</p> <pre><code>-(void)insertData{ sqlite3 *database; sqlite3_stmt *statement; //Get the path to the documents directory and append the databaseName AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate]; NSString *databasePath = [appDelegate m_DatabasePath]; NSLog(@"%@", [appDelegate m_DatabasePath]); if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { NSString *insertSQL = @"INSERT INTO products (name,category,calories, saturates,fat,fibre,sugar,salt, image) VALUES ('Snickers',' Confectionary','300','55','55','55','55','55', 'http://upload.wikimedia.org/wikipedia/en/8/88/Snickers_wrapped.jpg');"; const char *insert_stmt = [insertSQL UTF8String]; if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL)== SQLITE_OK) { NSLog(@"Here 1"); if(sqlite3_step(statement)==SQLITE_DONE) { UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Added" message:@"" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil]; [alert show]; } else { UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Not Added" message:@"An error has occured" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil]; [alert show]; alert=nil; } } // Release the compiled statement from memory sqlite3_finalize(statement); sqlite3_close(database); } } </code></pre> <p>After the above code is called I would expect to see a new cell in the table view representing the new record, however this doesn't happen. Does anyone know what I'm doing wrong? I re-read in the database in the viewDidAppear method of the table view.</p> <p>Thanks,</p> <p>Jack</p> <p>EDIT:</p> <p>Performing an NSLog outputting the path to the database in both the insertData method and readProductsFromDatabase gives:</p> <p>/Users/me/Library/Application Support/iPhone Simulator/5.0/Applications/4868830F-E02A-4A81-93AD-2BEAF8B85971/Library/ProductDatabase.sql</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload