How to insert data into a SQLite database in iPhone
insert, iphone, objective-c, sqlite
Solution
If you're really interested in learning how to use SQLite to manage data in an iPhone app, I would recommend you to complete the following tutorial, as it's a very good introduction:
iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1
The tutorial handles Selecting, Updating, Inserting and Deleting data in an iPhone app, using SQLite.
Problem
I am new to iPhone development. I want to insert certain data into my database and retrieve it and display it in a table. I have created `Database data.sqlite` with table 'user'. The table has two values, id(varchar) and name(varchar). I have inserted 3 rows of data in the table via "insert into user value('1','xxxx');" in the terminal window. I have created a navigation based applicaton named "testDb". In AppDelegate I have two methods ``` - (void)createEditableCopyOfDatabaseIfNeeded { NSLog(@"Creating editable copy of database"); // First, test for existence. BOOL success; NSFileManager *fileManager = [NSFileManager defaultManager]; NSError *error; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"]; success = [fileManager fileExistsAtPath:writableDBPath]; if (success) return; // The writable database does not exist, so copy the default to the appropriate location. NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data.sqlite"]; success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error]; if (!success) { NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]); } } ``` and ``` +(sqlite3 *) getNewDBConnection{ sqlite3 *newDBconnection; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"]; // Open the database. The database was prepared outside the application. if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) { NSLog(@"Database Successfully Opened :)"); } else { NSLog(@"Error in opening database :("); } return newDBconnection; } ``` In rootviewcontroller ``` -(void)initializeTableData{ sqlite3 *db=[DatabaseTestAppDelegate getNewDBConnection]; sqlite3_stmt *statement=nil; const char *sql="select * from user"; if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL)!=SQLITE_OK) NSAssert1(0,@"error in preparing staement",sqlite3_errmsg(db)); else { while(sqlite3_step(statement)==SQLITE_ROW) [tableData addObject:[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement,1)]]; } sqlite3_finalize(statement); } - (void)viewDidLoad { [super viewDidLoad]; tableData=[[NSMutableArray alloc]init]; [self addMyObjectIntoDatabase]; [self initializeTableData]; self.title=@"Database test"; } ``` To display the contents ``` - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath { static NSString *CellIdentifier = @"Cell"; UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier]; if (cell == nil) { cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease]; } NSLog(@"before displaying data"); cell.textLabel.text=[tableData objectAtIndex:indexPath.row]; NSLog(@"after displaying data"); // Configure the cell. return cell; } ``` This is working fine to display the content of table. But now I want to insert another row into my table and display the inserted content along with previous content. Since I am new to iPhone I learnt from tutorials to read the database and display its contents. How do I proceed further to my task of inserting and displaying again?