- (IBAction)saveWithSqlite:(id)sender
{
[self initDBWithDataTable];
[self storingDataIntoSqlite];
[self getPersons];
}
-(void)initDBWithDataTable
{
NSArray* arrPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString* strDBPath = [arrPath objectAtIndex:0];
strDBPath = [strDBPath stringByAppendingPathComponent:@"EmpDB.sqlite3"];
BOOL isExist = [[NSFileManager defaultManager]fileExistsAtPath:strDBPath];
if(sqlite3_open([strDBPath UTF8String], &sqlite)== SQLITE_OK)
{
if(!isExist)
{
char *error;
const char *sqlStatement = "CREATE TABLE IF NOT EXISTS PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT, FIRSTNAME TEXT, LASTNAME TEXT, BIRTHDAY DATE)";
if(sqlite3_exec(sqlite, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
{
// Create the ADDRESS table with foreign key to the PERSON table
sqlStatement = "CREATE TABLE IF NOT EXISTS ADDRESS (ID INTEGER PRIMARY KEY AUTOINCREMENT, STREETNAME TEXT, STREETNUMBER INT, PERSONID INT, FOREIGN KEY(PERSONID) REFERENCES PERSON(ID))";
if(sqlite3_exec(sqlite, sqlStatement, NULL, NULL, &error)== SQLITE_OK)
{
NSLog(@"DataBase Created");
}
else
{
NSLog(@"Error: %s", error);
}
}
else
{
NSLog(@"Error: %s", error);
}
}
}
}
-(void)storingDataIntoSqlite
{
NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO PERSON (FIRSTNAME, LASTNAME, BIRTHDAY) VALUES (\"%@\", \"%@\", \"%@\")", @"Kiran",@"yadav", @"1987-02-24"];
char *error;
if ( sqlite3_exec(sqlite, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
{
int64_t personID = sqlite3_last_insert_rowid(sqlite);
// Create insert statement for the address
insertStatement = [NSString stringWithFormat:@"INSERT INTO ADDRESS (STREETNAME, STREETNUMBER, PERSONID) VALUES (\"%@\", \"%@\", \"%lld\")", @"Ghazipur", @"30", personID];
if ( sqlite3_exec(sqlite, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
{
NSLog(@"Person inserted.");
}
else
{
NSLog(@"Error: %s", error);
}
}
else
{
NSLog(@"Error: %s", error);
}
}
-(NSArray*)getPersons
{
// Allocate a persons array
NSMutableArray *persons = [[NSMutableArray alloc]init];
// Create the query statement to get all persons
NSString *queryStatement = [NSString stringWithFormat:@"SELECT ID, FIRSTNAME, LASTNAME, BIRTHDAY FROM PERSON"];
// Prepare the query for execution
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(sqlite, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
// Iterate over all returned rows
while (sqlite3_step(statement) == SQLITE_ROW)
{
// Get associated address of the current person row
// int personID = sqlite3_column_int(statement, 0);
// Convert the birthday column to an NSDate
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc]init];
dateFormatter.dateFormat = @"yyyy-MM-dd";
NSString *birthdayAsString = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 3)];
NSDate *birthday = [dateFormatter dateFromString: birthdayAsString];
NSLog(@"%@",birthday);
// Create a new person and add it to the array
NSString* str1 = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
NSString* str2 = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 2)];
[persons addObject:str1];
[persons addObject:str2];
}
sqlite3_finalize(statement);
}
return persons;
}
Posts
