Check If column already exist and if not Alter Table in sqlite
Solution 1
Use following function for checking if column exists. Not checked practically so you may have to check syntax. Concept is like if you are able to create prepared statement than column exists otherwise not.
-(BOOL)checkColumnExists
{
BOOL columnExists = NO;
sqlite3_stmt *selectStmt;
const char *sqlStatement = "select yourcolumnname from yourtable";
if(sqlite3_prepare_v2(yourDbHandle, sqlStatement, -1, &selectStmt, NULL) == SQLITE_OK)
columnExists = YES;
return columnExists;
}
Swift 3.2:
private func tableHasColumn(db: OpaquePointer, tableName: String, columnName: String) -> Bool {
var retVal = false
var tableColumnsQueryStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, "PRAGMA table_info(\(tableName));",
-1,
&tableColumnsQueryStatement,
nil) == SQLITE_OK {
while (sqlite3_step(tableColumnsQueryStatement) == SQLITE_ROW) {
let queryResultCol1 = sqlite3_column_text(tableColumnsQueryStatement, 1)
let currentColumnName = String(cString: queryResultCol1!)
if currentColumnName == columnName {
retVal = true
break
}
}
}
return retVal
}
Solution 2
PRAGMA table_info(table-name);
This Pragma is used to get list of columns in your table.
- (BOOL)checkForField
{
NSString *desiredColumn = @"tblName";
const char *sql = "PRAGMA table_info(tblTest)";
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
{
return NO;
}
while(sqlite3_step(stmt) == SQLITE_ROW)
{
NSString *fieldName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
if([desiredColumn isEqualToString:fieldName])
return YES;
}
return NO;
}
PJR
Check my Sample codes on Github: https://github.com/paritsohraval100 Cocoa Controls https://www.cocoacontrols.com/controls/pjr-scrollview-slider https://www.cocoacontrols.com/controls/pjr-nsstring-category https://www.cocoacontrols.com/controls/pjrsignaturedemo https://www.cocoacontrols.com/controls/pulseanimation Please check My Blog: http://iosdevblogs.blogspot.in/
Updated on June 26, 2022Comments
-
PJR almost 2 years
I want to alter a table of sqlite in my app.
now , i want to check that if that column already exists in my table or not ?
hence if not exist i want to add that column with alter table syntex.
currently i am using .
-(void) alterDB{ sqlite3_stmt *statement; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:DATABASE_NAME]; if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { NSString *updateSQL = [NSString stringWithFormat: @"ALTER TABLE diagramInfo ADD COLUMN testColumn VARCHAR"]; const char *update_stmt = [updateSQL UTF8String]; sqlite3_prepare_v2(database, update_stmt, -1, &statement, NULL); if(sqlite3_step(statement)==SQLITE_DONE) { UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"DB altered" message:@"Success" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil]; [alert show]; [alert release]; alert=nil; } else { UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"DB Updation" message:@"DB not Altered" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil]; [alert show]; [alert release]; alert=nil; } // Release the compiled statement from memory sqlite3_finalize(statement); sqlite3_close(database); } }