how to connect with sqlite in iphone? [closed]

Question

I am developing an application in iphone and am new to iphone. Kindly let me know the code for open/close/update/delete database record in iphone using sqlite?

Regards, Vijaya

Answer 1

ViewController.h

#import <UIKit/UIKit.h>
#import "sqlite3.h"

@interface ViewController
{

     IBOutlet UITextField *no;
     UITextField *name,*address;

     IBOutlet UIButton *save,*update,*del,*clear,*Find,*showdata;

     sqlite3 *contactDB;

     IBOutlet UILabel *status;

}
@property (strong, nonatomic) IBOutlet UITextField *no;
@property (strong, nonatomic) IBOutlet UITextField *name;
@property (strong, nonatomic) IBOutlet UITextField *address;
@property (retain, nonatomic) IBOutlet UILabel *status;
- (IBAction)SaveData:(id)sender;
- (IBAction)FindData:(id)sender;
- (IBAction)UpdateData:(id)sender;
- (IBAction)DeleteData:(id)sender;
- (IBAction)ClearData:(id)sender;
- (IBAction)ShowData:(id)sender;

@end


ViewController.m


#import "ViewController.h"
#import "ShowRecord.h"
@interface ViewController ()
{
    NSString *docsDir;
    NSArray *dirPaths;
    NSString *databasePath;
}
@end

@implementation ViewController
@synthesize name, address,status,no;

- (void)viewDidLoad
{
    [self LoadDB];
    [status setHidden:TRUE];
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}
- (void)viewDidUnload {
    self.name = nil;
    self.address = nil;

    self.status = nil;
}

-(void)LoadDB
{
    // 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:@"student.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 stud (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT)";

            if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                status.text = @"Failed to create table";
            }
            else
            {
                status.text = @"success Created table";
            }

            sqlite3_close(contactDB);

        }
        else
        {
            status.text = @"Failed to open/create database";
        }
    }
}
- (IBAction)SaveData:(id)sender
{
    [status setHidden:false];
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *insertSQL = [NSString stringWithFormat:                               @"INSERT INTO stud                             (name, address) VALUES (\"%@\", \"%@\")",                                                              name.text, address.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 = @"";
        }
        else
        {
            status.text = @"Failed to add contact";
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);

    }
}
- (IBAction)UpdateData:(id)sender
{
     [status setHidden:false];
    sqlite3_stmt *statement;
    const char *dbpath=[databasePath UTF8String];
    if(sqlite3_open(dbpath, &contactDB)==SQLITE_OK)
    {
        NSString *updateSQl=[NSString stringWithFormat:@"update stud set name=\"%@\",address=\"%@\" where id=\"%@\" ",name.text,address.text,no.text];
        const char *update_stmt=[updateSQl UTF8String];
        sqlite3_prepare_v2(contactDB, update_stmt, -1, &statement, NULL);

        if (sqlite3_step(statement)==SQLITE_DONE)
        {
            status.text=@"Record Updated";
            name.text=@"";
            address.text=@"";
            no.text=@"";
        }
        else
        {
            status.text=@"Record Not Updated";
        }
    }
}
- (IBAction)DeleteData:(id)sender
{
     [status setHidden:false];
    sqlite3_stmt *statement;
    const char *dbpath=[databasePath UTF8String];
    if (sqlite3_open(dbpath, &contactDB)==SQLITE_OK) {
        NSString *deleteSql=[NSString stringWithFormat:@"delete from stud where id=\"%@\"",no.text];
        const char *delete_stmt=[deleteSql UTF8String];
        sqlite3_prepare_v2(contactDB, delete_stmt, -1, &statement, NULL);
        if(sqlite3_step(statement)==SQLITE_DONE)
        {
            status.text=@"Record Deleted";
            name.text=@"";
            address.text=@"";
            no.text=@"";

        }
        else
        {
            status.text=@"Record Not Deleted ";
        }

    }
}

- (IBAction)ClearData:(id)sender
{
    name.text=@"";
    no.text=@"";
    address.text=@"";
}

- (IBAction)ShowData:(id)sender
{
    ShowRecord *showrecord=[[ShowRecord alloc]initWithNibName:@"ShowRecord" bundle:nil];
    [self. pushViewController:showrecord animated:YES];

   // ShowRecord *vc1 = [[ShowRecord alloc]                                  initWithNibName:@"ViewControllerNext" bundle:nil];
    //[self.navigationController pushViewController:vc1 animated:YES];


}

- (IBAction)FindData:(id)sender
{
    [status setHidden:false];
    const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt    *statement;
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat:
                              @"SELECT name,address FROM stud WHERE id=\"%@\"",
                              no.text];
        const char *query_stmt = [querySQL UTF8String];
        if (sqlite3_prepare_v2(contactDB,
                               query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                NSString  *namefield=[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 0)];

                NSString *addressField = [[NSString alloc]
                                          initWithUTF8String:
                                          (const char *) sqlite3_column_text(statement, 1)];
                name.text=namefield;
                address.text = addressField;
                status.text = @"Record Found";

            }
            else
            {
                status.text = @"Record Not Found";
                address.text = @"";
                name.text=@"";
            }
            sqlite3_finalize(statement);
        }
        sqlite3_close(contactDB);
    }
}

@end

Answer 2

import

In .h file

- (void)createEditableCopyOfDatabaseIfNeeded;  
+ (sqlite3 *) getNewDBConnection;  

Add the following code in appdelegate.m in didFinishLaunching method:

[self createEditableCopyOfDatabaseIfNeeded];

- (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:@"Scores.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:@"Scores.sqlite"];  

    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];  

    if (!success) {  
        NSAssert1(0, @"Failed to create writable database file with message ‘%@’.", [error localizedDescription]);  
    }  
}

+ (sqlite3 *) getNewDBConnection {  
    sqlite3 *newDBconnection;  
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  
    NSString *documentsDirectory = [paths objectAtIndex:0];  
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"Scores.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 your view controller call the method of insertion etc. here i called a method which brings me the data from table

sqlite3* db =[iMemory_SharpenerAppDelegate getNewDBConnection];  
sqlite3_stmt *statement = nil;  
const char *sql = "select * from HighScore";  

if(sqlite3_prepare_v2(db, sql, -1, &statement, NULL)!=SQLITE_OK) {  
    NSAssert1(0,@"error prepearing statement",sqlite3_errmsg(db));  
} else {  
    while (sqlite3_step(statement)==SQLITE_ROW) {  
        //dt= [[NSString alloc]initWithUTF8String:(char *)sqlite3_column_text(statement, 2)];  
        dt = [NSString stringWithFormat:@"%s",(char *)sqlite3_column_text(statement, 0)];  
        //dt = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];  
        NSLog(@"%@score==",dt);  
        [nmber addObject:dt];  
    }  

}  

sqlite3_finalize(statement);  
sqlite3_close(db);  

Answer 3

Here is a sample code to connect SQLite from iOS:(insertion of data) Source:http://sickprogrammersarea.blogspot.in/2014/03/sqlite-in-ios-using-objective-c.html

#import "sqlLiteDemoViewController.h"
@interface sqlLiteDemoViewController (){
UILabel *lblName;
UILabel *lblRoll;
UILabel *lblAge;
UITextField *txtName;
UITextField *txtroll;
UISlider *sldAge;
}

@end

@implementation sqlLiteDemoViewController

- (void)viewDidLoad
{

NSString *docsDir;
NSArray *dirPaths;

// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = dirPaths[0];
// Build the path to the database file
_databasePath = [[NSString alloc]initWithString: [docsDir stringByAppendingPathComponent:@"student.db"]];
NSLog(@"%@",_databasePath);
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 STUDENT (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ROLL TEXT, AGE TEXT)";

        if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {
            NSLog(@"Failed to create table");
        }
        sqlite3_close(_contactDB);
        NSLog(@"Connection Successful");
    } else {
        NSLog(@"Failed to open/create database");
    }
}


UITapGestureRecognizer *tapScroll = [[UITapGestureRecognizer alloc]initWithTarget:self action:@selector(tapped)];

[self.view addGestureRecognizer:tapScroll];

[super viewDidLoad];
lblName = [[UILabel alloc] initWithFrame:CGRectMake(10, 30, 100, 50)];
lblName.backgroundColor = [UIColor clearColor];
lblName.textColor=[UIColor blackColor];
lblName.text = @"Name";
[self.view addSubview:lblName];
lblRoll = [[UILabel alloc] initWithFrame:CGRectMake(10, 80, 100, 50)];
lblRoll.backgroundColor = [UIColor clearColor];
lblRoll.textColor=[UIColor blackColor];
lblRoll.text = @"Roll no";
[self.view addSubview:lblRoll];
lblAge = [[UILabel alloc] initWithFrame:CGRectMake(10, 130, 100, 50)];
lblAge.backgroundColor = [UIColor clearColor];
lblAge.textColor=[UIColor blackColor];
lblAge.text = @"Age";
[self.view addSubview:lblAge];



txtName = [[UITextField alloc] initWithFrame:CGRectMake(80, 40, 200, 40)];
txtName.borderStyle = UITextBorderStyleRoundedRect;
txtName.font = [UIFont systemFontOfSize:15];
txtName.placeholder = @"Specify Name";
txtName.autocorrectionType = UITextAutocorrectionTypeNo;
txtName.keyboardType = UIKeyboardTypeDefault;
txtName.returnKeyType = UIReturnKeyDone;
txtName.clearButtonMode = UITextFieldViewModeWhileEditing;
txtName.contentVerticalAlignment = UIControlContentVerticalAlignmentCenter;
txtName.delegate = self;
[self.view addSubview:txtName];

txtroll = [[UITextField alloc] initWithFrame:CGRectMake(80, 90, 200, 40)];
txtroll.borderStyle = UITextBorderStyleRoundedRect;
txtroll.font = [UIFont systemFontOfSize:15];
txtroll.placeholder = @"Specify Roll";
txtroll.autocorrectionType = UITextAutocorrectionTypeNo;
txtroll.keyboardType = UIKeyboardTypeNumberPad;
txtroll.returnKeyType = UIReturnKeyDone;
txtroll.clearButtonMode = UITextFieldViewModeWhileEditing;
txtroll.contentVerticalAlignment = UIControlContentVerticalAlignmentCenter;
txtroll.delegate = self;
[self.view addSubview:txtroll];


CGRect frame = CGRectMake(80.0, 140.0, 200.0, 40.0);
sldAge= [[UISlider alloc] initWithFrame:frame];
[sldAge setBackgroundColor:[UIColor clearColor]];
sldAge.minimumValue = 0;
sldAge.maximumValue = 30;
sldAge.continuous = YES;
sldAge.value = 15.0;
[self.view addSubview:sldAge];


UIButton *subButton = [UIButton buttonWithType:UIButtonTypeRoundedRect];
subButton.frame = CGRectMake(80.0, 200.0, 80.0, 30.0);
[subButton setTitle:@"Store" forState:UIControlStateNormal];
subButton.backgroundColor = [UIColor clearColor];
[subButton setTitleColor:[UIColor blackColor] forState:UIControlStateNormal ];
[subButton addTarget:self action:@selector(store:) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:subButton];


UIButton *srchButton = [UIButton buttonWithType:UIButtonTypeRoundedRect];
srchButton.frame = CGRectMake(200.0, 200.0, 80.0, 30.0);
[srchButton setTitle:@"Search" forState:UIControlStateNormal];
srchButton.backgroundColor = [UIColor clearColor];
[srchButton setTitleColor:[UIColor blackColor] forState:UIControlStateNormal ];
[srchButton addTarget:self action:@selector(find:) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:srchButton];   
}

- (void)tapped {
[self.view endEditing:YES];
}
-(void)store:(id)sender{
@try{
NSLog(@"store called");
    sqlite3_stmt    *statement;
    const char *dbpath = (const char *)[_databasePath UTF8String];
    NSString *age=[NSString stringWithFormat:@"%g", sldAge.value];
    NSLog(@"Age is %@",age);
    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO STUDENT (name, roll  , age) VALUES (\"%@\", \"%@\", \"%@\")",
                               txtName.text, txtroll.text, age];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(_contactDB, insert_stmt,
                           -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            NSLog(@"Insertion Successful");
        } else {
            NSLog(@"Insertion Failure");
        }
        sqlite3_finalize(statement);
        sqlite3_close(_contactDB);
    }
}@catch (NSException *e) {
    NSLog(@"Exception : %s",sqlite3_errmsg(_contactDB));
}
}

- (void) find:(id)sender
{
const char *dbpath = [_databasePath UTF8String];
sqlite3_stmt    *statement;

if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
{
    NSString *querySQL = [NSString stringWithFormat:@"SELECT roll, age FROM STUDENT WHERE name=\"%@\"",txtName.text];

    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(_contactDB,query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString *roll = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            txtroll.text = roll;
            NSString *age = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];
            sldAge.value = [age floatValue];
           NSLog(@"Match found");
        } else {
           NSLog(@"Match found");
            txtroll.text = @"";
            sldAge.value = 0.0;
        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(_contactDB);
}

}

Answer 4

Read the sqlite documentation on their website. They have API references and tutorials. All you need to get going in XCode is to add the libsqlite3.dylib to the referenced frameworks.