I am trying to import via email a Sqlite DB & then update the local Sqlite DB, the import is successful & I can copy data to NSLog successfully, but when I try to update the local DB it always fails, I have tried copying it from NSBundle to document directory, I have tried creating a new table in the ViewDidLoad & still fails, can anybody please help
-(void) checkAndCreateDatabase2{
databaseName2 = @"KpiPapav2.sqlite";
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
success = [fileManager fileExistsAtPath:databasePath2];
if(success) return; {NSLog(@"KpiPapav2.sqlite exists");}
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName2];
[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath2 error:nil];
}
Table creation in ViewDidLoad
NSString *createSQL = @"CREATE TABLE IF NOT EXISTS kpiRegionCPW (CPW_Region TEXT,Region_Number TEXT,Region_Name TEXT,Regional_General_Manager TEXT,Period TEXT,EBIT_YTD TEXT,EBIT_12Month TEXT,Sales_Conversion TEXT,Sales_Conversion_VsTarget TEXT,PPCR TEXT,PPCR_VsTarget TEXT,GSHT TEXT,Upgrade_Mix TEXT,Upgrade_Mix_VsTarget TEXT,WCSS TEXT,WCSS_VsTarget TEXT,Right_Sell_Usage TEXT,Right_Sell_Usage_VsTarget TEXT,Mystery_Shopper TEXT,Mystery_Shopper_VsTarget TEXT,SOH_Spend TEXT,SOH_Spend_VsTarget TEXT,CATI TEXT,CATI_VsTarget TEXT,Shrinkage TEXT,Shrinkage_VsTarget TEXT)";
char *errorMsg;
if (sqlite3_exec(localDB, [createSQL UTF8String], NULL, NULL, &errorMsg)!= SQLITE_OK) {
sqlite3_close(localDB);
NSAssert(0, @"Error creating table: %s", errorMsg);
}
Open DB & save to NSDefaults & Insert into new DB
-(void) updateLocalSqlite {
const char *dbpath = [databasePath3 UTF8String];
sqlite3_stmt *statement;
if (sqlite3_open(dbpath, &papav2Import) == SQLITE_OK) {
NSString *querySQL = [NSString stringWithFormat:
@"SELECT * FROM kpiRegionCPW"];
const char *query_stmt = [querySQL UTF8String];
if (sqlite3_prepare_v2(papav2Import,
query_stmt, -1, &statement, NULL) == SQLITE_OK)
while (sqlite3_step(statement) == SQLITE_ROW) {
NSString *ykpiRegionCPW1 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
NSString *ykpiRegionCPW2 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
NSString *ykpiRegionCPW3 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
NSString *ykpiRegionCPW4 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)];
NSString *ykpiRegionCPW5 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];
NSString *ykpiRegionCPW6 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];
NSString *ykpiRegionCPW7 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 6)];
NSString *ykpiRegionCPW8 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 7)];
NSString *ykpiRegionCPW9 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 8)];
NSString *ykpiRegionCPW10 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 9)];
NSString *ykpiRegionCPW11 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 10)];
NSString *ykpiRegionCPW12 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 11)];
NSString *ykpiRegionCPW13 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 12)];
NSString *ykpiRegionCPW14 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 13)];
NSString *ykpiRegionCPW15 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 14)];
NSString *ykpiRegionCPW16 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 15)];
NSString *ykpiRegionCPW17 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 16)];
NSString *ykpiRegionCPW18 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 17)];
NSString *ykpiRegionCPW19 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 18)];
NSString *ykpiRegionCPW20 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 19)];
NSString *ykpiRegionCPW21 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 20)];
NSString *ykpiRegionCPW22 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 21)];
NSString *ykpiRegionCPW23 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 22)];
NSString *ykpiRegionCPW24 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 23)];
NSString *ykpiRegionCPW25 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 24)];
NSString *ykpiRegionCPW26 = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 25)];
kpiRegionCPW1 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW1];
kpiRegionCPW2 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW2];
kpiRegionCPW3 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW3];
kpiRegionCPW4 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW4];
kpiRegionCPW5 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW5];
kpiRegionCPW6 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW6];
kpiRegionCPW7 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW7];
kpiRegionCPW8 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW8];
kpiRegionCPW9 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW9];
kpiRegionCPW10 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW10];
kpiRegionCPW11 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW11];
kpiRegionCPW12 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW12];
kpiRegionCPW13 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW13];
kpiRegionCPW14 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW14];
kpiRegionCPW15 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW15];
kpiRegionCPW16 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW16];
kpiRegionCPW17 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW17];
kpiRegionCPW18 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW18];
kpiRegionCPW19 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW19];
kpiRegionCPW20 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW20];
kpiRegionCPW21 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW21];
kpiRegionCPW22 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW22];
kpiRegionCPW23 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW23];
kpiRegionCPW24 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW24];
kpiRegionCPW25 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW25];
kpiRegionCPW26 = [[NSString alloc]initWithFormat:@"%@", ykpiRegionCPW26];
}
sqlite3_finalize(statement);
char *err;
if (sqlite3_exec(papav2Import, [querySQL UTF8String], NULL, NULL, &err) !=SQLITE_OK) {
sqlite3_close(papav2Import);
NSLog(@"Export Failed!!!!");
}else{
NSLog(@"Export Worked");
}
NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
[defaults setObject:kpiRegionCPW1 forKey:@"kpiRegionCPW1"];
[defaults setObject:kpiRegionCPW2 forKey:@"kpiRegionCPW2"];
[defaults setObject:kpiRegionCPW3 forKey:@"kpiRegionCPW3"];
[defaults setObject:kpiRegionCPW4 forKey:@"kpiRegionCPW4"];
[defaults setObject:kpiRegionCPW5 forKey:@"kpiRegionCPW5"];
[defaults setObject:kpiRegionCPW6 forKey:@"kpiRegionCPW6"];
[defaults setObject:kpiRegionCPW7 forKey:@"kpiRegionCPW7"];
[defaults setObject:kpiRegionCPW8 forKey:@"kpiRegionCPW8"];
[defaults setObject:kpiRegionCPW9 forKey:@"kpiRegionCPW9"];
[defaults setObject:kpiRegionCPW10 forKey:@"kpiRegionCPW10"];
[defaults setObject:kpiRegionCPW11 forKey:@"kpiRegionCPW11"];
[defaults setObject:kpiRegionCPW12 forKey:@"kpiRegionCPW12"];
[defaults setObject:kpiRegionCPW13 forKey:@"kpiRegionCPW13"];
[defaults setObject:kpiRegionCPW14 forKey:@"kpiRegionCPW14"];
[defaults setObject:kpiRegionCPW15 forKey:@"kpiRegionCPW15"];
[defaults setObject:kpiRegionCPW16 forKey:@"kpiRegionCPW16"];
[defaults setObject:kpiRegionCPW17 forKey:@"kpiRegionCPW17"];
[defaults setObject:kpiRegionCPW18 forKey:@"kpiRegionCPW18"];
[defaults setObject:kpiRegionCPW19 forKey:@"kpiRegionCPW19"];
[defaults setObject:kpiRegionCPW20 forKey:@"kpiRegionCPW20"];
[defaults setObject:kpiRegionCPW21 forKey:@"kpiRegionCPW21"];
[defaults setObject:kpiRegionCPW22 forKey:@"kpiRegionCPW22"];
[defaults setObject:kpiRegionCPW23 forKey:@"kpiRegionCPW23"];
[defaults setObject:kpiRegionCPW24 forKey:@"kpiRegionCPW24"];
[defaults setObject:kpiRegionCPW25 forKey:@"kpiRegionCPW25"];
[defaults setObject:kpiRegionCPW26 forKey:@"kpiRegionCPW26"];
[defaults synchronize];
}
sqlite3_close(papav2Import);
NSLog(@"Test Export: %@", kpiRegionCPW2);
}
-(void) updatelocalSqlite2 {
NSString *xkpiRegionCPW1 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW1"];
NSString *xkpiRegionCPW2 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW2"];
NSString *xkpiRegionCPW3 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW3"];
NSString *xkpiRegionCPW4 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW4"];
NSString *xkpiRegionCPW5 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW5"];
NSString *xkpiRegionCPW6 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW6"];
NSString *xkpiRegionCPW7 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW7"];
NSString *xkpiRegionCPW8 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW8"];
NSString *xkpiRegionCPW9 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW9"];
NSString *xkpiRegionCPW10 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW10"];
NSString *xkpiRegionCPW11 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW11"];
NSString *xkpiRegionCPW12 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW12"];
NSString *xkpiRegionCPW13 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW13"];
NSString *xkpiRegionCPW14 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW14"];
NSString *xkpiRegionCPW15 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW15"];
NSString *xkpiRegionCPW16 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW16"];
NSString *xkpiRegionCPW17 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW17"];
NSString *xkpiRegionCPW18 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW18"];
NSString *xkpiRegionCPW19 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW19"];
NSString *xkpiRegionCPW20 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW20"];
NSString *xkpiRegionCPW21 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW21"];
NSString *xkpiRegionCPW22 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW22"];
NSString *xkpiRegionCPW23 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW23"];
NSString *xkpiRegionCPW24 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW24"];
NSString *xkpiRegionCPW25 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW25"];
NSString *xkpiRegionCPW26 = [[NSUserDefaults standardUserDefaults]stringForKey:@"kpiRegionCPW26"];
NSLog(@"Test import: %@", xkpiRegionCPW8);
[self openDBLocal];
{
char *update = "INSERT INTO kpiRegionCPW (CPW_Region,Region_Number,Region_Name,Regional_General_Manager,Period,EBIT_YTD,EBIT_12Month,Sales_Conversion,Sales_Conversion_VsTarget,PPCR,PPCR_VsTarget,GSHT,Upgrade_Mix,Upgrade_Mix_VsTarget,WCSS,WCSS_VsTarget,Right_Sell_Usage,Right_Sell_Usage_VsTarget,Mystery_Shopper,Mystery_Shopper_VsTarget,SOH_Spend,SOH_Spend_VsTarget,CATI,CATI_VsTarget,Shrinkage,Shrinkage_VsTarget)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
char *errorMsg = NULL;
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(localDB, update, -1, &stmt, nil)== SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [xkpiRegionCPW1 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [xkpiRegionCPW2 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [xkpiRegionCPW3 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, [xkpiRegionCPW4 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, [xkpiRegionCPW5 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 6, [xkpiRegionCPW6 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 7, [xkpiRegionCPW7 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 8, [xkpiRegionCPW8 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 9, [xkpiRegionCPW9 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 10, [xkpiRegionCPW10 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 11, [xkpiRegionCPW11 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 12, [xkpiRegionCPW12 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 13, [xkpiRegionCPW13 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 14, [xkpiRegionCPW14 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 15, [xkpiRegionCPW15 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 16, [xkpiRegionCPW16 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 17, [xkpiRegionCPW17 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 18, [xkpiRegionCPW18 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 19, [xkpiRegionCPW19 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 20, [xkpiRegionCPW20 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 21, [xkpiRegionCPW21 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 22, [xkpiRegionCPW22 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 23, [xkpiRegionCPW23 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 24, [xkpiRegionCPW24 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 25, [xkpiRegionCPW25 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 26, [xkpiRegionCPW26 UTF8String], -1, SQLITE_TRANSIENT);
}
if (sqlite3_step(stmt) != SQLITE_DONE) {
NSAssert(0,@"Error updating local table:%s ",errorMsg);
sqlite3_finalize(stmt);
}
}
sqlite3_close(localDB);
}
Aucun commentaire:
Enregistrer un commentaire