Add
libsqlite3.0.dylib Framework to you Xcode project.
Create an SQlite Table
SqliteTable using SQLite manager.
Add a new File of NSObject class DBFieldsDict for your columns.
//
// DBFieldsDict.h
// SQlite
//
// Created by Prasad on 7/9/13.
// Copyright (c) 2013 Prasad. All rights reserved.
//
#import <Foundation/Foundation.h>
@interface DBFieldsDict : NSObject
// TABLE Meeting
@property (nonatomic, assign) int Meeting_Id;
@property (nonatomic, copy) NSString *Meeting_Title;
@property (nonatomic, copy) NSString *Meeting_Information;
@property (nonatomic, assign) int Meeting_day;
@property (nonatomic, assign) int Meeting_time_start;
@property (nonatomic, assign) int Meeting_time_end;
// TABLE Directors
@property (nonatomic, assign) int Director_Id;
@property (nonatomic, copy) NSString *Director_name;
@property (nonatomic, copy) NSString *Director_Organization;
@property (nonatomic, copy) NSString *Director_Organization_Role;
@property (nonatomic, copy) NSString *Director_Bio_Info;
@property (nonatomic, copy) UIImage *Director_image;
@synthesize all columns in DBFieldsDict.m
Create Another NSObject class "DataBase" for Database Queries
in DataBase.h File
#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "DBFieldsDict.h"
@interface DataBase : NSObject
+ (NSString*) copyDBFile;
+ (NSMutableArray*) select Meeting InformationFromDB;
+ (NSMutableArray*) selectDirectorsFromDB;
@end
in DataBase.m File
#import "DataBase.h"
@implementation DataBase
//Commen method for DAtaBase Copy
+ (NSString*) copyDBFile
{
NSString *docsDirectoryPath;
NSArray *dirPaths;
NSString *databasePath;
NSFileManager *fileManager = [NSFileManager defaultManager];
// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDirectoryPath = [dirPaths objectAtIndex:0];
// Build the path to the database file
databasePath = [[NSString alloc] initWithString: [docsDirectoryPath stringByAppendingPathComponent: @"SqliteTable.db"]];
BOOL isSuccess = [fileManager fileExistsAtPath: databasePath ];
if (!isSuccess)
{
NSError *error;
NSString *defaultDBPath=[[NSBundle mainBundle]pathForResource:@"SqliteTable" ofType:@"db"];
// NSLog(@"path :%@", defaultDBPath);
isSuccess = [fileManager copyItemAtPath:defaultDBPath toPath:databasePath error:&error];
if (! isSuccess)
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
NSLog(@"%@",databasePath);
return databasePath;
}
//To Return Notnull String to avoid crashes
+(NSString *)notNullsqlite3_column_textOfColumn :(int)columnNo squliteStatement:(sqlite3_stmt*)statement
{
NSString *coumnText = ((char *)sqlite3_column_text(statement, columnNo)) ? [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, columnNo)] : nil;
if(coumnText == nil)
{
return @"";
}
else
{
return [NSString stringWithUTF8String:(const char*) sqlite3_column_text(statement, columnNo)];
}
}
Select Query
//select Directors FromDB
+ (NSMutableArray*) selectDirectorsFromDB
{
NSString *DBPath = [self copyDBFile];
sqlite3 *sqliteDB = nil;
sqlite3_stmt *statement = nil;
NSMutableArray *allDataArray = [[NSMutableArray alloc] init];
if (sqlite3_open([DBPath UTF8String], &sqliteDB) == SQLITE_OK)
{
//Prepare Query to retrieve data
NSString *query = @"SELECT Director_Id, Director_name , Director_Organization , Director_Organization_Role , Director_Bio_Info , Director_image FROM Directors";
if(sqlite3_prepare_v2(sqliteDB, [query UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
while (sqlite3_step(statement) == SQLITE_ROW)
{
DBFieldsDict *dbFieldsDict = [[DBFieldsDict alloc] init];
//To get data in to dbFieldsDict
//For Integer Data
dbFieldsDict.Director_Id = sqlite3_column_int(statement, 0);
//For VArChar String Data
dbFieldsDict.Director_name = [self notNullsqlite3_column_textOfColumn:1
squliteStatement:statement];
dbFieldsDict.Director_Organization = [self notNullsqlite3_column_textOfColumn:2
squliteStatement:statement];
dbFieldsDict.Director_Organization_Role = [self notNullsqlite3_column_textOfColumn:3
squliteStatement:statement];
dbFieldsDict.Director_Bio_Info = [self notNullsqlite3_column_textOfColumn:4
squliteStatement:statement];
//For Image Data
NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(statement, 2)
length: sqlite3_column_bytes(statement, 2)];
dbFieldsDict.Director_image = [UIImage imageWithData:imageData];
//To add the all data from dicts to the Array
[allDataArray addObject:dbFieldsDict];
}
}
else
{
NSLog(@"Statement not prepared");
}
}
return allDataArray;
}
+ (BOOL) insertCoordinatesIntoDB:(DBColumns*)dbColumns
{
NSString *DBPath = [self copyDBFile];
sqlite3 *contactsDB = nil;
sqlite3_stmt *insertStatement = nil;
// NSLog(@"DICT : %@", contact);
if (sqlite3_open([DBPath UTF8String], &contactsDB) == SQLITE_OK)
{
if(insertStatement == nil)
{
// CREATE TABLE "LatLangTable" ("CompanyName" VARCHAR, "Lattitude" FLOAT, "Langitude" FLOAT)
NSString *insertQuery = [NSString stringWithFormat:@"INSERT INTO LatLangTable ( CompanyName, Lattitude, Langitude) VALUES(?, ?, ? )"];
const char *insert = [insertQuery UTF8String];
NSLog(@"-------- %@",insertQuery);
if(sqlite3_prepare_v2(contactsDB, insert, -1, &insertStatement, NULL) != SQLITE_OK)
{
NSLog(@"Error while creating insert Statement");
}
else
{
sqlite3_bind_text(insertStatement, 1, [dbColumns.companyName2 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_double(insertStatement, 2, dbColumns.latitude);
sqlite3_bind_double(insertStatement,
3, dbColumns.
longitude);
//To insert Date
sqlite3_bind_double(insertStatement, 2, [dbColumns.ResultentTime timeIntervalSince1970]);
}
}
if(SQLITE_DONE != sqlite3_step(insertStatement)) {
NSLog(@"Error while Executing insert Statement");
}
}
return YES;
}
//Calling insert Query from our view
-(IBAction)insertLatLang:(id)sender
{
//Add latitudes, longitudes to dictionary to coordinatesArray
for (int i= 0; i<addreessArray.count; i++)
{
CLLocationCoordinate2D center = [self geoCodeUsingAddress: [addreessArray objectAtIndex:i]];
dbColumns = [dataArray objectAtIndex:i];
DBColumns *dbCol = [[DBColumns alloc] init];
dbCol.companyName2 = dbColumns.companyName1;
dbCol.latitude = center.latitude;
dbCol.longitude = center.longitude;
[DataBase insertCoordinatesIntoDB:dbCol];
}
}
//Update statement for update recent contacts by time
//====================================
+ (BOOL) updateRecentRecord:(Recent*)recent
{
NSString *DBPath = [self copyDBFile];
sqlite3 *contactsDB = nil;
sqlite3_stmt *updateStatement = nil;
if (sqlite3_open([DBPath UTF8String], &contactsDB) == SQLITE_OK)
{
if(updateStatement == nil)
{
NSString * updateQuery = [NSString stringWithFormat:@" UPDATE RecentsTable SET NAME = ? , recentRowId = ?, time = ? where recentRowId = ?"];
const char *update = [updateQuery UTF8String];
if(sqlite3_prepare_v2(contactsDB, update, -1, &updateStatement, NULL) != SQLITE_OK) {
NSLog(@"Error while creating update Statement");
}
else
{
sqlite3_bind_text(updateStatement, 1, [recent.Name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(updateStatement, 2, recent.recentRowId);
sqlite3_bind_double(updateStatement, 3, [[NSDate date] timeIntervalSince1970]);
sqlite3_bind_int(updateStatement, 4, recent.recentRowId);
}
// NSLog(@" Nane- %@ recentID-%d ",recent.Name, recent.recentRowId );
if(SQLITE_DONE != sqlite3_step(updateStatement)) {
NSLog(@"Error while Executing update Statement");
}
}
}
[DBPath release];
return YES;
}
//To Delete the selecte contacts from db
//==================================
+(BOOL)deleteContactFromDB:(Contact*)contact
{
NSString *projectPath=[self copyDBFile];
BOOL returnValue = YES;
sqlite3_stmt *deleteStmt = nil;
sqlite3 *cuisineDB = nil;
if (sqlite3_config(SQLITE_CONFIG_SERIALIZED) == SQLITE_OK) {
NSLog(@"Can now use sqlite on multiple threads, using the same connection");
}
int ret = sqlite3_enable_shared_cache(1);
if(ret != SQLITE_OK)
{
// NSLog(@"Not Shared");
}
// Open the database. The database was prepared outside the application.
if (sqlite3_open([projectPath UTF8String], &cuisineDB) == SQLITE_OK)
{
if(deleteStmt == nil)
{
// int selectedId = [[dict objectForKey:@"rowid"] intValue];
int selectedId = contact.rowid;
NSString *strValue = [NSString stringWithFormat:@"DELETE FROM ContactInfo WHERE (rowid = '%d') ", selectedId];
const char *sql = [strValue UTF8String];
if(sqlite3_prepare_v2(cuisineDB, sql, -1, &deleteStmt, NULL) != SQLITE_OK) {
NSLog(@"Error while creating addStmt in addRecipeToDatabase %@", [NSString stringWithUTF8String:(char *)sqlite3_errmsg(cuisineDB)]);
returnValue = NO;
}
}
if(SQLITE_DONE != sqlite3_step(deleteStmt)) {
NSLog(@"Error while Executing addStmt in addRecipeToDatabase %@", [NSString stringWithUTF8String:(char *)sqlite3_errmsg(cuisineDB)]);
returnValue = NO;
}
sqlite3_reset(deleteStmt);
if (deleteStmt)
{
sqlite3_finalize(deleteStmt);
deleteStmt = nil;
}
}
sqlite3_close(cuisineDB);
[projectPath release];
return returnValue;
}
