Xcode SQLLITE INSERT and SELECT
Posted on Tuesday, April 19, 2011
|
4 Comments
1. Create SQL Light DataBase By Following command in your MAC Terminal
o firstly we want to create a sqlite database file. Open up the Terminal and get into your project directory. Now type:
| sqlite3 names.db |
This will create the file ‘names.db’ at the path your at in the Terminal. Now to check the database contents and close, type:
1 | > .tables |
2 | > .quit |
| create table allusers(user_id DECIMAL,user_name varchar(50)); |
2. Create a new objective-c class file with named "dbController"
in header file add following code
--------------------------------------
#define DATABASE_NAME @"TweetUser.db"
#define DATABASE_TITLE @"Tweet User"
@interface dbController : NSObject {
NSMutableArray *_scoresArray ;
NSMutableArray *UserArray;
}
- (NSString *) getWritableDBPath;
-(void)createEditableCopyOfDatabaseIfNeeded;
-(void)saveUserInDatabase:(NSString *)user_id:(NSString *)user_name;
-(void)loadUserFromDatabase;
-(NSMutableString *)LoadData;
- (NSMutableArray *)FindUserFromDatabase:(NSString *)user_id;
@end
3. in implementaion file add following code
#import "dbController.h"
#import <sqlite3.h>
@implementation dbController
- (NSString *) getWritableDBPath {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:DATABASE_NAME];
}
static int loadTimesCallback(void *context, int count, char **values, char **columns)
{
NSMutableArray *times = (NSMutableArray *)context;
for (int i=0; i < count; i++) {
const char *nameCString = values[i];
[times addObject:[NSString stringWithUTF8String:nameCString]];
}
return SQLITE_OK;
}
- (void)loadUserFromDatabase
{
NSString *file = [self getWritableDBPath];
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL success = [fileManager fileExistsAtPath:file];
// If its not a local copy set it to the bundle copy
if(!success) {
//file = [[NSBundle mainBundle] pathForResource:DATABASE_TITLE ofType:@"db"];
[self createEditableCopyOfDatabaseIfNeeded];
}
_scoresArray = [[NSMutableArray alloc] init];
sqlite3 *database = NULL;
if (sqlite3_open([file UTF8String], &database) == SQLITE_OK) {
sqlite3_exec(database, "select * from allusers", loadTimesCallback, _scoresArray, NULL);
}
sqlite3_close(database);
}
- (NSMutableArray *)FindUserFromDatabase:(NSString *)user_id
{
NSString *file = [self getWritableDBPath];
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL success = [fileManager fileExistsAtPath:file];
// If its not a local copy set it to the bundle copy
if(!success) {
//file = [[NSBundle mainBundle] pathForResource:DATABASE_TITLE ofType:@"db"];
[self createEditableCopyOfDatabaseIfNeeded];
}
//[UserArray removeObjectsInArray:UserArray];
UserArray = [[NSMutableArray alloc] init];
sqlite3 *database = NULL;
if (sqlite3_open([file UTF8String], &database) == SQLITE_OK) {
NSString *SQLSTMNT = [NSString stringWithFormat:@"select * from allusers where user_id =%@",user_id];
const char *sql = [SQLSTMNT UTF8String];
sqlite3_exec(database, sql, loadTimesCallback, UserArray, NULL);
}
sqlite3_close(database);
[UserArray release];
return UserArray;
}
-(NSMutableString *)LoadData {
// Update text
NSMutableString *content = [[NSMutableString alloc] init];
for (int i = 0; i < [_scoresArray count]; i++) {
NSString *data = [_scoresArray objectAtIndex:i];
if(((i+2)%2)==0) {
[content appendString:[NSString stringWithFormat:@"ID: %@",data]];
} else if(((i+1)%2)==0) {
NSLog(@"%d",((i+2) %2));
[content appendString:[NSString stringWithFormat:@"Name: %@",data]];
[content appendString:@"\n"];
}
}
[content release];
return content;
}
-(void)saveUserInDatabase:(NSString *)user_id:(NSString *)user_name {
// Copy the database if needed
[self createEditableCopyOfDatabaseIfNeeded];
NSString *filePath = [self getWritableDBPath];
sqlite3 *database;
if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
//NSString *temp = [NSString stringWithFormat:@"insert into allusers (user_id,user_name) VALUES (%@,%@)",user_id,user_name];
const char *sqlStatement = "insert into allusers (user_id,user_name) VALUES (?,?)";
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
sqlite3_bind_text( compiledStatement, 1,[user_id UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text( compiledStatement, 2,[user_name UTF8String], -1, SQLITE_TRANSIENT);
}
if(sqlite3_step(compiledStatement) != SQLITE_DONE ) {
NSLog( @"Save Error: %s", sqlite3_errmsg(database) );
}
sqlite3_finalize(compiledStatement);
}
sqlite3_close(database);
}
-(void)createEditableCopyOfDatabaseIfNeeded
{
// Testing 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:DATABASE_NAME];
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:DATABASE_NAME];
success = [fileManager copyItemAtPath:defaultDBPath
toPath:writableDBPath
error:&error];
if(!success)
{
NSAssert1(0,@"Failed to create writable database file with Message : '%@'.",
[error localizedDescription]);
}
}
@end
================================
4. Now in your class which is load by a xib file use following code
-(IBAction)btnAdd:(id)sender {
if(textfield.text != @"" && textID.text != @"") {
NSMutableArray *dataArr = [userDB FindUserFromDatabase:textID.text];
if([dataArr count]==0) {
// Insert into the database
[userDB saveUserInDatabase:textID.text:textfield.text];
[userDB loadUserFromDatabase];
// Update the table view contents
textView.text =[userDB LoadData];
}
[textfield resignFirstResponder];
[textID resignFirstResponder];
}
}
- (void)viewDidLoad {
[super viewDidLoad];
userDB = [[dbController alloc]init];
[userDB loadUserFromDatabase];
// Update the table view
textView.text = [userDB LoadData];
}
5. Run XOCDE
6. Source File
I am seeing no errors, however, the table does not seem to be updated.
ReplyDeleteyou can send me your code for update
ReplyDeleteI think the problem is where I am creating the database in my "directory". Which folder should this go in?
ReplyDeleteIn Project Root directory
ReplyDelete