How to retrieve data from sqlite database? Getting return value zero.
-(id)init{
if(self==[super init]){
database=nil;
}
return self;
}
-(void)addDetails:(NSString *)_name withAddress:(NSString *)_address withAge:(int)_age withMobile:(double)_mobile{
// NSLog(@"in db class --->%@ %@ %d %f",_name,_address,_age,_mobile);
// NSString *name=@"...has done it";
if([self openDBConnection] == TRUE) {
const char *sql= "insert into Personal (Name,Address,Age,Mobiel) Values(?, ?, ?, ?)";
sqlite3_stmt *updateUser_stmt = nil;
NSInteger retVal = sqlite3_prepare_v2(database, sql, -1, &updateUser_stmt, NULL);
NSLog(@"the return value is %d",retVal);
if(retVal == SQLITE_OK)
{
sqlite3_bind_text (updateUser_stmt , 1, [_name UTF8String],-1,SQLITE_STATIC);
sqlite3_bind_text (updateUser_stmt , 2, [_address UTF8String], -1, SQLITE_STATIC);
sqlite3_bind_int(updateUser_stmt, 3, _age);
sqlite3_bind_double(updateUser_stmt, 4, _mobile);
// sqlite3_bind_text (updateUser_stmt , 4, [HomeUserDetails.strName UTF8String], -1, SQLITE_STATIC);
NSInteger resultInt = sqlite3_step(updateUser_stmt );
if(SQLITE_DONE != resultInt)
NSLog(@"Error while inserting data. '%s'", sqlite3_errmsg(database));
else
{
NSLog(@"MyA user details added to database");
}
}
sqlite3_reset(updateUser_stmt );
sqlite3_finalize(updateUser_stmt );
updateUser_stmt = nil;
}
}
-(void) modifyUserDetails :(Person *)_person
{
NSLog(@"in update");
if([self openDBConnection] == TRUE)
{
NSString *str = [NSString stringWithFormat:@"UPDATE Personal SET Address='%@', Age='%d' , Mobiel='%d' WHERE Name='%@'",
_person.address, [_person getAge], [_person getMobile], _person.name];
NSLog(@" String :%@",str);
//NSString *str = [NSString stringWithFormat:@"UPDATE Room SET Access=1 WHERE RoomName='%@'",room.m_roomName];
const char *sql = [str UTF8String];
// [NSString stringWithFormat:@"UPDATE Room SET Access=%d WHERE RoomName=%@", access,room.m_roomName];
sqlite3_stmt *updateUser_stmt = nil;
if(sqlite3_prepare_v2(database, sql, -1, &updateUser_stmt, NULL) == SQLITE_OK)
{
sqlite3_bind_text(updateUser_stmt, 1, [_person.name UTF8String], -1, SQLITE_STATIC);
sqlite3_bind_text(updateUser_stmt, 2, [_person.address UTF8String], -1, SQLITE_STATIC);
sqlite3_bind_int(updateUser_stmt, 3, [_person getAge]);
sqlite3_bind_double(updateUser_stmt, 4, [_person getMobile]);
if(SQLITE_DONE != sqlite3_step(updateUser_stmt))
NSLog(@"Error while updating data data. '%s'", sqlite3_errmsg(database));
}
sqlite3_reset(updateUser_stmt );
sqlite3_finalize(updateUser_stmt );
updateUser_stmt = nil;
}
}
-(void)getUserDetails
{
UIApplication *app=[UIApplication sharedApplication];
appdlegate=app.delegate;
if(appdlegate.arrayNames)
[appdlegate.arrayNames removeAllObjects];
NSLog(@"in get users");
if([self openDBConnection] == TRUE)
{
const char *sql = "select Name,Address,Age,Mobiel from Personal";//AccessLevel,HintA HintQ,Name,pwd
sqlite3_stmt *getAccess_stmt = nil;
NSInteger retVal = sqlite3_prepare_v2(database, sql, -1, &getAccess_stmt, NULL);
if(retVal == SQLITE_OK)
{
while(sqlite3_step(getAccess_stmt) == SQLITE_ROW)
{
char* name = (char*) sqlite3_column_text(getAccess_stmt, 0);
NSString *tmp;
if (name != NULL){
tmp = [NSString stringWithUTF8String:name];
NSLog(@"value form db :%@",tmp);
}
[appdlegate.arrayNames addObject:tmp];
char *addrs = (char*) sqlite3_column_text(getAccess_stmt, 1);
if (addrs != NULL){
NSString *tmp = [NSString stringWithUTF8String:addrs];
NSLog(@"value from db :%@",tmp);
}
int age =sqlite3_column_int(getAccess_stmt,2);
if(age){
NSLog(@"age from db: %d",age);
}
int mobile=sqlite3_column_double(getAccess_stmt, 3);
;
if(mobile){
NSLog(@"mobile from db: %d",mobile);
}
}
}
sqlite3_reset(getAccess_stmt );
sqlite3_finalize(getAccess_stmt );
getAccess_stmt = nil;
}
}
-(Person *)getPerticular:(NSString *)_name
{
Person *person;
UIApplication *app=[UIApplication sharedApplication];
appdlegate=app.delegate;
NSLog(@"in get users");
if([self openDBConnection] == TRUE)
{
NSString *query = [NSString stringWithFormat:@"select *from Personal where name ='%@'",_name];
const char *sql =[query cStringUsingEncoding:NSASCIIStringEncoding];
//const char *sql = "select *from Personal where name=''";//AccessLevel,HintA HintQ,Name,pwd
sqlite3_stmt *getAccess_stmt = nil;
NSInteger retVal = sqlite3_prepare_v2(database, sql, -1, &getAccess_stmt, NULL);
if(retVal == SQLITE_OK)
{
person=[[Person alloc]init];
while(sqlite3_step(getAccess_stmt) == SQLITE_ROW)
{
char* name = (char*) sqlite3_column_text(getAccess_stmt, 0);
NSString *tmp;
if (name != NULL){
tmp = [NSString stringWithUTF8String:name];
NSLog(@"value perticular form db :%@",tmp);
person.name=tmp;
}
char *addrs = (char*) sqlite3_column_text(getAccess_stmt, 1);
if (addrs != NULL){
NSString *tmp = [NSString stringWithUTF8String:addrs];
NSLog(@"value perticular from db :%@",tmp);
person.address=tmp;
}
int age =sqlite3_column_int(getAccess_stmt,2);
if(age){
NSLog(@"perticular age from db: %d",age);
[person setAge:age];
}
int mobile=sqlite3_column_double(getAccess_stmt, 3);
;
if(mobile){
NSLog(@"mobile from db: %d",mobile);
[person setMobile:mobile];
}
}
}
sqlite3_reset(getAccess_stmt );
sqlite3_finalize(getAccess_stmt );
getAccess_stmt = nil;
}
return person;
}
-(void)createConnection{
NSError *error;
NSArray *strdest=[NSArray arrayWithObjects:NSHomeDirectory(),@"Documents",DB_NAME,nil];
dest=[NSString pathWithComponents:strdest];
NSFileManager *manager=[NSFileManager defaultManager];
NSArray *strSrc=[NSArray arrayWithObjects:NSHomeDirectory(),@"SqlliteDemo.app",DB_NAME, nil];
NSString *source=[NSString pathWithComponents:strSrc];
BOOL sucess=[manager fileExistsAtPath:dest];
if(sucess){
NSLog(@"alredy db copied to documents");
}
else {
[manager copyItemAtPath:source toPath:dest error:&error];
if(error){
NSLog(@"NO Error");
}
else{
NSLog(@"error is %@",error);
}
}
}
-(BOOL)openDBConnection{
[self createConnection];
if(!database){
if(sqlite3_open([dest UTF8String], &database)==SQLITE_OK){
return TRUE;
}else {
return FALSE;
}
}
return TRUE;
}
-(void)finalizeStatements{
if(database)sqlite3_close(database);
}
