Dboperations

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface DBOperation : NSObject
{
}

+(void)OpenDatabase:(NSString*)path;  //Open the Database
+(void)finalizeStatements;//Closing and do the final statement at application exits
+(void)checkCreateDB;
+(int) getLastInsertId;
+(BOOL) executeSQL:(NSString *)sqlTmp;
+(NSMutableArray*) selectData:(NSString *)sql;

@end


#import "DBOperation.h"
static sqlite3 *database = nil;
static int conn;
@implementation DBOperation

+(void)getDatabaseName:(NSString *)DataBaseName{
	NSString *dbPath;
	NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);
	NSString *docDir = [docPaths objectAtIndex:0];
	dbPath = [[docDir stringByAppendingPathComponent:DataBaseName] retain];
	BOOL success;
	NSFileManager *fm = [NSFileManager defaultManager];
	success = [fm fileExistsAtPath:dbPath];
	//NSLog(@"%@",[[NSBundle mainBundle] resourcePath]);
	if(success){
		//NSLog(@"Database copy successfully created !!");
		[self OpenDatabase:dbPath];
		return;
	}
	NSString *dbPathFromApp=[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:DataBaseName];
	[fm copyItemAtPath:dbPathFromApp toPath:dbPath error:nil];
	[self OpenDatabase:dbPath];
}

//Open database
+ (void) OpenDatabase:(NSString*)path
{
	@try
	{
		conn = sqlite3_open([path UTF8String], &database);
		if (conn == SQLITE_OK) {
			NSLog(@"Database Opened");
		}
		else
			sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
	}
	@catch (NSException *e) {
		NSLog(@"%@",e);
	}
}

+(NSMutableArray*) selectData:(NSString *)sql{
	if ( conn == SQLITE_OK) {
   // NSLog(@"DBManagerObj::selectData \n");
    sqlite3_stmt *stmt = nil;
    if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
        [NSException raise:@"DatabaseException" format:@"Error while creating statement. '%s'", sqlite3_errmsg(database)];
    }
	NSMutableArray *obj = [[NSMutableArray alloc]init];
	int numResultColumns = 0;
    while (sqlite3_step(stmt) == SQLITE_ROW)
	{

		numResultColumns = sqlite3_column_count(stmt);

		NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
        NSMutableDictionary *tmpObj = [[NSMutableDictionary alloc]init];

		for(int i = 0; i < numResultColumns; i++)
		{

			if(sqlite3_column_type(stmt, i) == SQLITE_INTEGER){

                const char *name = sqlite3_column_name(stmt, i);
                NSString *columnName = [[NSString alloc]initWithCString:name encoding:NSUTF8StringEncoding];
                [tmpObj setObject:[NSString stringWithFormat:@"%i",sqlite3_column_int(stmt, i)] forKey:columnName];

            } else if (sqlite3_column_type(stmt, i) == SQLITE_FLOAT) {

				const char *name = sqlite3_column_name(stmt, i);
				NSString *columnName = [[NSString alloc]initWithCString:name encoding:NSUTF8StringEncoding];
				[tmpObj setObject:[NSString stringWithFormat:@"%f",sqlite3_column_double(stmt, i)] forKey:columnName];
            } else if (sqlite3_column_type(stmt, i) == SQLITE_TEXT) {
                const char *name = sqlite3_column_name(stmt, i);
				NSString *tmpStr = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, i)];
				if ( tmpStr == nil) {
					tmpStr = @"";
				}
                NSString *columnName = [[NSString alloc]initWithCString:name encoding:NSUTF8StringEncoding];
                 [tmpObj setObject:tmpStr forKey:columnName];

            } else if (sqlite3_column_type(stmt, i) == SQLITE_BLOB) {

            }

        }
        [obj addObject:tmpObj];

        [pool release];

    }
    return obj;
	} else {
		return nil;
	}
}

+(BOOL) executeSQL:(NSString *)sqlTmp {
    if(conn == SQLITE_OK) {
        const char *sqlStmt = [sqlTmp cStringUsingEncoding:NSUTF8StringEncoding];
        sqlite3_stmt *cmp_sqlStmt1;
        int returnValue = sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt1, NULL);
        returnValue == SQLITE_OK ? NSLog(@"\n Inserted \n") :NSLog(@"\n Not Inserted \n");
        sqlite3_step(cmp_sqlStmt1);
        sqlite3_finalize(cmp_sqlStmt1);
        if (returnValue == SQLITE_OK) {
            return TRUE;
        }
    }
    return FALSE;
}

+(int) getLastInsertId{
	return sqlite3_last_insert_rowid(database);
}

//Save data at application closing time
+ (void) finalizeStatements {
	if(database) sqlite3_close(database);
}


@end