How to insert data into SQLite database in iPhone

Question

I am new to iPhone development. I want to insert certain data into my database and get it and display it in a table. I created a Database data.sqlite with a user table. The table has two values: id (varchar) and name (varchar). I inserted 3 rows of data into the table through "insert into user value" ("1", "xxxx"); in the terminal window. I created a navigation app called "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 content

 - (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 works great to display the contents of a table. But now I want to insert another row in my table and display the inserted content along with the previous content. Since I'm new to the iPhone, I learned from tutorials to read the database and display its contents. How do I get to my insert and display task again?

Answer 1

If you are really interested in learning how to use SQLite to manage data in an iPhone application, I would recommend that you complete the following tutorial as this is a very good introduction:

iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1

The tutorial handles selecting, updating, pasting, and deleting data in an iPhone application using SQLite.

Answer 2

Basically you have two recommended options (and using class C is not one of them). FMDB is used by many developers as a wrapper for Sqlite. http://code.google.com/p/flycode/source/browse/trunk/fmdb FMDB is lightweight and fairly easy to use.

Here is an example call:

 NSString *path = [documentsDirectory stringByAppendingPathComponent:@"mysqlite.db"]; appSettings.dao = [FMDatabase databaseWithPath:path]; FMResultSet *rs = [[appSettings dao] executeUpdate:@"SELECT * FROM mytable"]; while ([rs next]){ // do stuff ... } 

This assumes an instance of am ApplicationSettings inside your .m file of the AppDelegate file.

 ApplicationSettings *appSettings = [ApplicationSettings sharedApplicationSettings]; 

The inserts are also lightweight:

 sql = [NSString stringWithFormat:@"INSERT INTO table (intCol1, strCol2) VALUES ('%d','%@')", myObj.theNumber, myObj.theString]; [appSettings.dao executeUpdate:sql]; 

Another option is to use basic data (available with SDK 3.0). Core Data should be fairly fast and optimized, although it deprives you of the ability to perform non-standard materials. Although this can lead to slow SQL queries, if you do not pay due attention to how you use it.

There's a tutorial on the iPhone Dev Center here

Answer 3

To insert a user with specific values, you need to create an SQL insert statement, for example. Suppose that the User object has a first and last name, then it will look like this:

 NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO USER (FIRSTNAME, LASTNAME) VALUES (\"%@\", \"%@\")", user.firstName, user.lastName]; char *error; if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK) 

You can check the complete example: http://www.apptite.be/tutorial_ios_sqlite.php

Answer 4

check:

 dbName = @"db.sqlite"; NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDir = [documentPaths objectAtIndex:0]; dbPath = [documentsDir stringByAppendingPathComponent:dbName]; BOOL success; NSFileManager *fileManager = [NSFileManager defaultManager]; success = [fileManager fileExistsAtPath:dbPath]; if(success) return; NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:dbName]; [fileManager copyItemAtPath:databasePathFromApp toPath:dbPath error:nil]; 

and

  sqlite3 *database ; [app checkAndCreateDatabase]; // Open the database from the users filessytem if(sqlite3_open([app.dbPath UTF8String], &database) == SQLITE_OK) { sqlite3_stmt *compiledStatement1; NSString* query; query=[NSString stringWithFormat:@ " insert into test ( name , company , phone , email , address , country , state , city, zip , online_id , in_time , flag_delete, flag_update,type_id) values (?, ?, ?,?,?, ?, ?, ?, ?,?,?,? ,?,?)"]; const char *compiledStatement = [query UTF8String]; if(sqlite3_prepare_v2(database, compiledStatement, -1, &compiledStatement1, NULL) == SQLITE_OK) { sqlite3_bind_text(compiledStatement1, 1, [text UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_text(compiledStatement1, 2, [text UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_text(compiledStatement1, 3, [text UTF8String], -1, SQLITE_TRANSIENT); } // Loop through the results and add them to the feeds array if(SQLITE_DONE != sqlite3_step(compiledStatement1)) NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database)); sqlite3_finalize(compiledStatement1); sqlite3_close(database); }