Hi all,
Pretty sure that many of you have already worked on this.
An idea to frame things related to DB together.
Lets see how much this works!!!!
Ok Now lets proceed
1) Create Database
2) Create Tables
3) Fetch Data from Table
NSString *sqlStatement = [NSString stringWithFormat:@"SELECT * FROM Info
4) Execute Statements
Pretty sure that many of you have already worked on this.
An idea to frame things related to DB together.
Lets see how much this works!!!!
Ok Now lets proceed
1) Create Database
- (BOOL)createAppDatabase
{
NSString *docsDir;
NSArray *dirPaths;
BOOL isSuccess = NO;
dirPaths = NSSearchPathForDirectoriesInDomains
(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = [dirPaths objectAtIndex:0];
self.databasePath = [[NSString alloc] initWithString:
[docsDir stringByAppendingPathComponent: @"MyDatabase.db"]];
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: databasePath ] == NO)
{
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
isSuccess = YES;
sqlite3_close(database);
}
else
isSuccess = NO;
}
return isSuccess;
}
- (BOOL)createTableWithSqlStatement:(NSString *)sqlStatement
{
BOOL isSuccess = NO;
NSFileManager *fileManager = [NSFileManager defaultManager];
if ([fileManager fileExistsAtPath:self.databasePath ] == YES)
{
const char *dbpath = [self.databasePath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK)
{
char *errMsg;
const char *sql_stmt = [sqlStatement UTF8String];
if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
{
isSuccess = NO;
NSLog(@"Failed to create table");
}
sqlite3_close(database);
return isSuccess;
}
else
{
isSuccess = NO;
NSLog(@"Failed to open/create database");
}
}
return isSuccess;
}
NSString *sqlStatement = [NSString stringWithFormat:@"SELECT * FROM Info
where userId=='%d'",@"21"];
- (LoginModel*)fetchDataFromInfoTable:(NSString*)sqlStatement
{
sqlite3_stmt *sql_smt;
LoginModel *loginModel;
if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK)
{
if (sqlite3_prepare_v2(database, [sqlStatement UTF8String], -1, &sql_smt, nil)
== SQLITE_OK)
{
loginModel = [[LoginModel alloc] init];
while(sqlite3_step(sql_smt)==SQLITE_ROW)
{
const char *s;
s=(char *)sqlite3_column_text(sql_smt, 0); // Here 0 is the 1st column
if(s==NULL)
loginModel.userID=@"";
else
loginModel.userID =[NSString stringWithUTF8String:s];
s=(char *)sqlite3_column_text(sql_smt, 1); // Here 1 is the 2nd column
if(s==NULL)
loginModel.userName=@"";
else
loginModel.userName =[NSString stringWithUTF8String:s];
}
}
sqlite3_finalize(sql_smt);
sqlite3_close(database);
}
else
{
loginModel = nil;
NSLog(@"Failed to open/create database");
}
return loginModel;
}
- (BOOL)executeSqlStatement:(NSString*)sqlStatement
{
sqlite3_stmt *sql_smt;
BOOL isSuccess = NO;
if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK)
{
if (sqlite3_prepare_v2(database, [sqlStatement UTF8String], -1, &sql_smt, nil)
== SQLITE_OK)
{
if (SQLITE_DONE!=sqlite3_step(statement))
{
sqlite3_close(database);
isSuccess = NO;
}
else {
NSLog(@"Success");
isSuccess = YES;
NSLog(@"%@",sqlStatement);
}
}
sqlite3_finalize(sql_smt);
sqlite3_close(database);
}
else
{
isSuccess = NO;
NSLog(@"Failed to open/create database");
}
return isSuccess;
}
5) Call executeSqlStatement
NSString *sqlStatement = [NSString stringWithFormat:@"INSERT INTO Info
(userId , userName) VALUES (%d,'%@')",[userID intValue],userName];
[self executeSqlStatement:sqlStatement];
sqlStatement = [NSString stringWithFormat:@"UPDATE Info
Set userName = '%@' where userID = '%d' ",@"Swati",@"21"];
[self executeSqlStatement:sqlStatement];
sqlStatement = [NSString stringWithFormat: @"DELETE FROM Info
WHERE userID ='%d'",@"21"];
Hey where is my database ???
Here is the path >> Admin >> Library >> Application Support >> iPhone Simulator >> Your chosen Version >> Applications >> Here you will see a list of all apps on Simulator >> choose yours >> Documents >> MyDatabase.db
How to view the contents ???
You can install a plugin in /firefox named Sqlite Manager, click and provide the location and here is the DB view.....
