How to create sqlite database programmatically?

Question

I am new to iPhone development. I have created the database using the terminal in mac os x. Now i am trying to create the database programatically in iPhone application using objective- C. Can somebody tell me a good site where i can view the sample applications which are worked and is there any site for learning the tutorial? Please somebody help me.

I suggest you to search in google. Stack-overflow should not your search engine. As you are new in stack overflow so i didn't mark down-vote. — Nitin, May 11 '12 at 04:43

score 15 · Accepted Answer · edited Nov 15 '12 at 12:49

From a tutorial on Techtopia:

NSString *docsDir;
NSArray *dirPaths;

// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];

// Build the path to the database file
databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"contacts.db"]];

NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: databasePath ] == NO) {
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
        char *errMsg;
        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";

        if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK) {
            status.text = @"Failed to create table";
        }
        sqlite3_close(contactDB);
    }
    else {
        status.text = @"Failed to open/create database";
    }
}
[filemgr release];

`dbpath == databasePath`? `contactDB` declared where? – wcochran Mar 29 '14 at 18:20 — wcochran, Mar 29 '14 at 18:20

score 3 · Answer 2 · edited Jul 01 '17 at 06:37

#pragma mark - SQLite

-(void)createDatabase
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); 
    NSString *documentsDirectory = [paths objectAtIndex:0];

    databasePath = [[NSString alloc]initWithString:[documentsDirectory stringByAppendingPathComponent:@"timings.db"]];

    if ([[NSFileManager defaultManager] fileExistsAtPath:databasePath] == FALSE)
    {
        if (sqlite3_open([databasePath UTF8String], &timingsDatabase) == SQLITE_OK)
        {
            const char *sqlStatement = "CREATE TABLE IF NOT EXISTS TIMINGS (ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT, TIMING TEXT)";
            char *error;
            sqlite3_exec(timingsDatabase, sqlStatement, NULL, NULL, &error);
            sqlite3_close(timingsDatabase);
        }
    }
}

-(void)storeTiming
{
    if (sqlite3_open([databasePath UTF8String], &timingsDatabase) == SQLITE_OK)
    {
        NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
        [dateFormatter setDateFormat:@"HH:mm:ss.SSS"];

        NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO TIMINGS (TIMESTAMP, TIMING) VALUES (\"%@\", \"%@\")", [dateFormatter stringFromDate:startDate], stopWatchLabel.text];

        char *error;
        sqlite3_exec(timingsDatabase, [insertStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(timingsDatabase);

        [dateFormatter release];
    }
}

-(void)getTimings
{
    if (sqlite3_open([databasePath UTF8String], &timingsDatabase) == SQLITE_OK)
    {
        NSString *queryStatement = [NSString stringWithFormat:@"SELECT TIMESTAMP, TIMING FROM TIMINGS"];

        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(timingsDatabase, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                NSLog(@"Timestamp: %s Timing: %s", sqlite3_column_text(statement, 0), sqlite3_column_text(statement, 1));
            }
            sqlite3_finalize(statement);
            sqlite3_close(timingsDatabase);
        }  
    }
}

Check these examples:

score 2 · Answer 3 · edited Aug 27 '15 at 09:44

Create Sqlite Database in xcode

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    // Override point for customization after application launch.

    [self createDb];

    return YES;
}


-(void)createDb
{
    NSArray *dir=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *dbPath=[NSString stringWithFormat:@"%@/userDb.sqlite",[dir lastObject]];

    sqlite3 *db;

    NSFileManager *fm=[NSFileManager new];

    if([fm fileExistsAtPath:dbPath isDirectory:nil])
    {
        NSLog(@"Database already exists..");
        return;
    }

    if (sqlite3_open([dbPath UTF8String],&db)==SQLITE_OK)
    {
        const char *query="create table user (userName VARCHAR(20),userAdd VARCHAR(20), userPass VARCHAR(20))";

        if (sqlite3_exec(db, query, NULL, NULL, NULL)==SQLITE_OK)
        {
            NSLog(@"User table created successfully..");
        }else
        {
            NSLog(@"User table creation failed..");
        }

    }else
    {
        NSLog(@"Open table failed..");
    }
    sqlite3_close(db);

}

CStreel · Answer 4 · 2012-05-11T05:08:59.240

If you want to be using the the Database for Data Persistance look into CoreData it is a good resource for storing large amounts of data for an app, keep in mind though CoreData is not Database.

You can use SQLite quite easily on an iOS platform, but you are not provided with the visual tools to develop it as you do with CoreData

If you are storing smaller amounts of data look into storing information into plists.

If you are storing a few strings, ints, or bool you should use NSUserDefaults

Without more information i couldn't say for certain what would be the best approach for you

Other Resources:

score 1 · Answer 5 · edited Oct 29 '13 at 11:22

To creat a data base for your iPhone app is a little bit lengthy but it has many steps but it is so simple :

First of all you should to download mozilla fire fox and download its add ons and you can find sqlite database option by clicking Tools Option. And you can creat a new Databse file with columns and rows . Its just simple process to creat a data base file and after that you can include that file into your X code project.

For example :- just like an image which you commonly included in your Xcode project.

after that write the following code it is working nice for me :)

In your . m file write the following sqlite code

#import "YourHeaderFile"

static sqlite3 *database = nil;
static sqlite3_stmt *addStmt = nil;
static sqlite3_stmt *updateStmt = nil;


@implementation 'YourImplementClass'



- (NSString *) getDBPath {

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"YourDataBaseFileName.sqlite"];
}

- (void) insertValueToDatabase {

    NSString *dbPath =[self getDBPath];

    NSString *select_Meal = dummy_select_Meal;
    NSString *dateTime = dummy_date_Time;
    NSString *location = dummy_location;
    NSString *mealItem = dummy_mealItem;

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

        const char *sqlDB = "Insert into iHad(Mealtype,DateAndTime,Location,Mealitem) VALUES (?,?,?,?)";

        if(sqlite3_prepare_v2(database, sqlDB, -1, &addStmt, NULL) != SQLITE_OK)
            NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));

        sqlite3_bind_text(addStmt, 1, [select_Meal UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 2, [dateTime UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 3, [location UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 4, [mealItem UTF8String], -1, SQLITE_TRANSIENT);

        if(SQLITE_DONE != sqlite3_step(addStmt))
            NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
        //else
            //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
            //coffeeID = sqlite3_last_insert_rowid(database);

        //Reset the add statement.
        sqlite3_reset(addStmt);

    }
    else
        sqlite3_close(database); 

}

and for save you can creat a method like this

-(void)saveButtonClick
{

    if (![dummy_select_Meal length] || ![dummy_mealItem length])
    {
        UIAlertView *alert = [[UIAlertView alloc] init];
        [alert setTitle:@"Select Meal Type/Meal Item"];
        [alert setDelegate:self];
        [alert addButtonWithTitle:@"Ok"];
        [alert show];
        [alert release];
    }

    else
    {
        indicator = [[UIActivityIndicatorView alloc] initWithFrame:CGRectMake(30,30,50,50)];
        indicator.activityIndicatorViewStyle = UIActivityIndicatorViewStyleGray;//UIActivityIndicatorViewStyleWhiteLarge;//

        [self insertValueToDatabase];-----------------------> To insert data into data base

        iHadAppDelegate *delegate = [[UIApplication sharedApplication]delegate];
        [delegate readMealsFromDatabase];

        [rootController reloadTable_SaveButton];

    }
    [rootController showTablePopupAction:nil];

}

Still if you have any problem then reply me i will do help you :)

score 0 · Answer 6 · answered Jun 16 '14 at 18:02

Call this Function at didFinishLaunchingWithOptions:

  -(void)createdb
  {
       NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,      NSUserDomainMask, YES);
       NSString *documentsDirectory = [paths objectAtIndex:0];
       NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"emp.sqlite"];

       NSURL *dbURL = [NSURL fileURLWithPath:dbPath];

       // copy a database from the bundle if not present
       // on disk
       NSFileManager *fm = [NSFileManager defaultManager];
       if (![fm fileExistsAtPath:dbPath])
       {
             NSURL *bundlePath = [[NSBundle mainBundle] URLForResource:@"emp" withExtension:@"sqlite"];
             NSError *error = nil;
            if (!bundlePath || ![fm copyItemAtURL:bundlePath toURL:dbURL error:&error])                         
            {
               NSLog(@"error copying database from bundle: %@", error);
            }
       }
       else
       {
             NSLog(@"Success in Copy file");
       }
  }

Click here to Download Full Example

score -1 · Answer 7 · answered Dec 16 '16 at 02:23

Call this function after memmory warning.

-(IBAction)saveData:(UIButton *)sender {

sqlite3_stmt *statement;
const char *dbPath = [databasePath UTF8String];

if(sqlite3_open(dbPath, &contactDB)== SQLITE_OK){

    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO CONTACTS(NAME,ADDRESS,PHONE)VALUES(\"%@\",\"%@\",\"%@\")",_name.text,_address.text,_phoneNo.text];

    const char *insert_stmt = [insertSQL UTF8String];
    sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
    if (sqlite3_step(statement)== SQLITE_DONE) {
        _status.text = @"Contact added";
        _name.text = @"";
        _address.text  = @"";
        _phoneNo.text = @"";
    }else{
        _status.text = @"Failed to add contact";
    }
    sqlite3_finalize(statement);
    sqlite3_close(contactDB);

}

}

How to create sqlite database programmatically?

7 Answers7

Linked