DataBaseConnection.h
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DBCon : NSObject {
sqlite3 *database;
}
@property (nonatomic) sqlite3 *database;
-(void)fnCopyDatabase;
-(void) fnInitializeDB;
- (NSString *) fnGetDBPath;
-(void)fnCloseDB;
@end
DataBaseConnection.m
#import "DBCon.h"
@implementation DBCon
@synthesize database;
- (void)fnCopyDatabase {
NSLog(@"Copy");
@try {
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"dblovequots.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:@"dblovequots.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}
@catch (NSException * e) {
NSLog(@"Exception %@",e);
}
}
- (NSString *) fnGetDBPath {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:@"dblovequots.sqlite"];
}
-(void) fnInitializeDB {
@try {
NSLog(@"fnInitializeDB");
if (sqlite3_open([[self fnGetDBPath] UTF8String], &database) == SQLITE_OK) {
} else {
sqlite3_close(database);
NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
}
}
@catch (NSException * e) {
NSLog(@"Exception %@",e);
}
}
-(void)fnCloseDB {
@try {
sqlite3_close(database);
}
@catch (NSException * e) {
NSLog(@"Exception %@",e);
}
}
@end
DataBase Files Retrive using Query:
DBFiles.h
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DBFiles : NSObject {
NSMutableArray *pArrCategory;
NSString *pStrDescription;
int iFavourite;
sqlite3 *database;
NSInteger primaryKey;
DBFiles *oDBFiles;
}
@property(nonatomic)NSInteger primaryKey;
@property(nonatomic) int iFavourite;
@property(nonatomic,retain) NSString *pStrDescription;
- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db;
+ (void)finalizeStatements;
//- (NSMutableArray*)fnGetDetails:(NSString *)psDayMonth;
//- (NSMutableArray*)fnGetResults:(NSString *)psSearchText;
- (NSMutableArray*)fnGetDescription;
- (NSMutableArray*)fnGetFavourites;
- (NSMutableArray*)fnGetFavFromId:(int) iId;
-(void) fnSetFavorite:(int)iBookMark:(int)iId;
@end
DBFiles.m
#import "DBFiles.h"
#import "DBCon.h"
static sqlite3_stmt *insert_statement = nil;
static sqlite3_stmt *fav_statement = nil;
static sqlite3_stmt *fav_update_statement = nil;
@implementation DBFiles
@synthesize pStrDescription;
@synthesize primaryKey, iFavourite;
- (NSMutableArray*)fnGetDescription {
DBCon *oDBCon = [[DBCon alloc] init];
[oDBCon fnInitializeDB];
oDBFiles = [[DBFiles alloc]init];
NSMutableArray *arrDict = [[NSMutableArray alloc] init];
NSString *sQryAuthor = [NSString stringWithFormat:@"SELECT * FROM tblWords"];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(oDBCon.database, [sQryAuthor UTF8String], -1, &selectstmt, NULL) == SQLITE_OK) {
while(sqlite3_step(selectstmt) == SQLITE_ROW) {
primaryKey = sqlite3_column_int(selectstmt, 0);
oDBFiles = [[DBFiles alloc]initWithPrimaryKey:primaryKey database:oDBCon.database];
oDBFiles.pStrDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)];
oDBFiles.iFavourite = sqlite3_column_int(selectstmt, 5);
[arrDict addObject:oDBFiles];
}
} else {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(oDBCon.database));
}
sqlite3_finalize(selectstmt);
[oDBCon fnCloseDB];
return arrDict;
}
- (NSMutableArray*)fnGetFavourites {
DBCon *oDBCon = [[DBCon alloc] init];
[oDBCon fnInitializeDB];
oDBFiles = [[DBFiles alloc]init];
NSMutableArray *arrDict = [[NSMutableArray alloc] init];
NSString *sQryAuthor = [NSString stringWithFormat:@"SELECT * FROM tblWords where bookmark = 1"];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(oDBCon.database, [sQryAuthor UTF8String], -1, &selectstmt, NULL) == SQLITE_OK) {
while(sqlite3_step(selectstmt) == SQLITE_ROW) {
primaryKey = sqlite3_column_int(selectstmt, 0);
oDBFiles = [[DBFiles alloc]initWithPrimaryKey:primaryKey database:oDBCon.database];
oDBFiles.pStrDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)];
oDBFiles.iFavourite = sqlite3_column_int(selectstmt, 5);
[arrDict addObject:oDBFiles];
}
} else {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(oDBCon.database));
}
sqlite3_finalize(selectstmt);
[oDBCon fnCloseDB];
return arrDict;
}
- (NSMutableArray*)fnGetFavFromId:(int) iId {
DBCon *oDBCon = [[DBCon alloc] init];
[oDBCon fnInitializeDB];
oDBFiles = [[DBFiles alloc]init];
NSMutableArray *arrDict = [[NSMutableArray alloc] init];
NSString *sQryAuthor = [NSString stringWithFormat:@"SELECT bookmark FROM tblWords where id = %d", iId];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(oDBCon.database, [sQryAuthor UTF8String], -1, &selectstmt, NULL) == SQLITE_OK) {
while(sqlite3_step(selectstmt) == SQLITE_ROW) {
//primaryKey = sqlite3_column_int(selectstmt, 0);
//oDBFiles = [[DBFiles alloc]initWithPrimaryKey:primaryKey database:oDBCon.database];
//oDBFiles.pStrDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)];
oDBFiles.iFavourite = sqlite3_column_int(selectstmt, 0);
[arrDict addObject:oDBFiles];
}
} else {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(oDBCon.database));
}
sqlite3_finalize(selectstmt);
[oDBCon fnCloseDB];
return arrDict;
}
-(void) fnSetFavorite:(int)iBookMark:(int)iId {
DBCon *oDBCon = [[DBCon alloc] init];
NSLog(@"iBookMark %d, iId %d", iBookMark, iId);
if (sqlite3_open([[oDBCon fnGetDBPath] UTF8String], &database) == SQLITE_OK) {
if (fav_update_statement == nil) {
const char *sql = "UPDATE tblWords SET bookmark=? WHERE id=?";
if (sqlite3_prepare_v2(database, sql, -1, &fav_update_statement, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
}
//int iFlag=0;
//if(flag==YES) iFlag=1;
sqlite3_bind_int(fav_update_statement, 1, iBookMark);
sqlite3_bind_int(fav_update_statement, 2, iId);
int success = sqlite3_step(fav_update_statement);
if (success != SQLITE_DONE) {
NSAssert1(0, @"Error: failed to save priority with message '%s'.", sqlite3_errmsg(database));
}
sqlite3_reset(fav_update_statement);
sqlite3_close(database);
}
}
/*- (NSMutableArray*)fnGetResults:(NSString *)psSearchText {
NSString *pStrTemp = @"%";
DBCon *oDBCon = [[DBCon alloc] init];
[oDBCon fnInitializeDB];
oDBFiles = [[DBFiles alloc]init];
NSMutableArray *arrDict = [[NSMutableArray alloc] init];
NSString *sQryAuthor = [NSString stringWithFormat:@"SELECT * FROM tbl_anniversaries WHERE anniv_About LIKE '%@%@%@' OR anniv_Events LIKE '%@%@%@' OR anniv_Births LIKE '%@%@%@' OR anniv_Deaths LIKE '%@%@%@'",pStrTemp,psSearchText,pStrTemp,pStrTemp,psSearchText,pStrTemp,pStrTemp,psSearchText,pStrTemp,pStrTemp,psSearchText,pStrTemp];
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(oDBCon.database, [sQryAuthor UTF8String], -1, &selectstmt, NULL) == SQLITE_OK) {
while(sqlite3_step(selectstmt) == SQLITE_ROW) {
primaryKey = sqlite3_column_int(selectstmt, 0);
oDBFiles = [[DBFiles alloc]initWithPrimaryKey:primaryKey database:oDBCon.database];
oDBFiles.pStrDayMonth = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];
oDBFiles.pStrAbout = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 2)];
oDBFiles.pStrEvents = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)];
oDBFiles.pStrBirths = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)];
oDBFiles.pStrDeaths = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 5)];
[arrDict addObject:oDBFiles];
}
} else {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(oDBCon.database));
}
sqlite3_finalize(selectstmt);
NSLog(@"arrdict %@", arrDict);
[oDBCon fnCloseDB];
return arrDict;
}*/
- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db {
database=db;
primaryKey = pk;
return self;
}
+ (void)finalizeStatements {
if (fav_statement) {
sqlite3_finalize(fav_statement);
fav_statement = nil;
}
if (insert_statement) {
sqlite3_finalize(insert_statement);
insert_statement = nil;
}
if (fav_update_statement) {
sqlite3_finalize(fav_update_statement);
fav_update_statement = nil;
}
}
@end
AppDelegate Function:
AppDelegate.h
didFinishLaunchwithApplication {
DBConnect *oDBConnect = [[DBConnect alloc] init];
[oDBConnect fnCopyDataBase];
}
Add FrameWork : lbsqlite3.0.dylib
Add Header file:#import <sqlite3.h>
