how to connect with sqlite in iphone? [closed]

问题:

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

回答1:

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);  

回答2:

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.

回答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);
}

}

回答4:

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