I need to check to see if a column exists and if it doesn't exist add it. From my research it looks like sqlite doesn't support IF statements and case statement should be used instead.
Here is what I have so far:
SELECT CASE WHEN exists(select * from qaqc.columns where Name = "arg" and Object_ID = Object_ID("QAQC_Tasks")) = 0 THEN ALTER TABLE QAQC_Tasks ADD arg INT DEFAULT(0);
But I get the error: Near "ALTER": Syntax error.
Any ideas?
You cannot use ALTER TABLE withcase.
You are looking for getting the column names for a table::-
PRAGMA table_info(table-name);
Check this tutorial on PRAGMA
This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.
Althought this is an old question, I found at PRAGMA functions a simpler solution:
SELECT COUNT(*) AS CNTREC FROM pragma_table_info('tablename') WHERE name='column_name'
If the result is more than zero then the column exists. Simple and one line query
The trick is to use
pragma_table_info('tablename')
instead of
PRAGMA table_info(tablename)
Edit: Please note that, as reported in PRAGMA functions:
The table-valued functions for PRAGMA feature was added in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite cannot use this feature
// This method will check if column exists in your table
public boolean isFieldExist(String tableName, String fieldName)
{
boolean isExist = false;
SQLiteDatabase db = this.getWritableDatabase();
Cursor res = db.rawQuery("PRAGMA table_info("+tableName+")",null);
res.moveToFirst();
do {
String currentColumn = res.getString(1);
if (currentColumn.equals(fieldName)) {
isExist = true;
}
} while (res.moveToNext());
return isExist;
}
You did not specify a language, so assuming it's not pure sql, you can check for errors on column querying:
SELECT col FROM table;
if you get an error so you know the column is not there (assuming you know the table exists, anyway you have the "IF NOT EXISTS" for this), otherwise the column exists and then you can alter the table accordingly.
I have applied this solution:
public boolean isFieldExist(SQLiteDatabase db, String tableName, String fieldName)
{
boolean isExist = false;
Cursor res = null;
try {
res = db.rawQuery("Select * from "+ tableName +" limit 1", null);
int colIndex = res.getColumnIndex(fieldName);
if (colIndex!=-1){
isExist = true;
}
} catch (Exception e) {
} finally {
try { if (res !=null){ res.close(); } } catch (Exception e1) {}
}
return isExist;
}
It is a variant of code by Pankaj Jangid.
Use with a try, catch and finally for any rawQuery() executions for better practices. And the following code will give you the result.
public boolean isColumnExist(String tableName, String columnName)
{
boolean isExist = false;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
if (cursor.moveToFirst()) {
do {
String currentColumn = cursor.getString(cursor.getColumnIndex("name"));
if (currentColumn.equals(columnName)) {
isExist = true;
}
} while (cursor.moveToNext());
}
}catch (Exception ex)
{
Log.e(TAG, "isColumnExist: "+ex.getMessage(),ex );
}
finally {
if (cursor != null)
cursor.close();
db.close();
}
return isExist;
}
SELECT EXISTS (SELECT * FROM sqlite_master WHERE tbl_name = 'TableName' AND sql LIKE '%ColumnName%');
..be aware of the LIKE condition which is imperfect, but it works for me as all my columns have very unique names..
A weird way to check for existing column
public static bool SqliteColumnExists(this SQLiteCommand cmd, string table, string column)
{
lock (cmd.Connection)
{
// make sure table exists
cmd.CommandText = string.Format("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = '{0}'", table);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
//does column exists?
bool hascol = reader.GetString(0).Contains(String.Format("\"{0}\"", column));
reader.Close();
return hascol;
}
reader.Close();
return false;
}
}
To get column names for a table:
PRAGMA table_info (tableName);
To get indexed columns:
PRAGMA index_info (indexName);
I've used following SELECT statement with SQLite 3.13.0
SELECT INSTR(sql, '<column_name>') FROM sqlite_master WHERE type='table' AND name='<table_name>';
Returns 0 (zero) if column <column_name> does not exists in the table <table_name>.
I updated the function of a friend... tested and working now
public boolean isFieldExist(String tableName, String fieldName)
{
boolean isExist = false;
SQLiteDatabase db = this.getWritableDatabase();
Cursor res = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
if (res.moveToFirst()) {
do {
int value = res.getColumnIndex("name");
if(value != -1 && res.getString(value).equals(fieldName))
{
isExist = true;
}
// Add book to books
} while (res.moveToNext());
}
return isExist;
}
Update the DATABASE_VERSION so onUpgrade function is called then if Column is already exists then nothing happen if not then it will add new column.
private static class OpenHelper extends SQLiteOpenHelper {
OpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (!isColumnExists(db, "YourTableName", "YourColumnName")) {
try {
String sql = "ALTER TABLE " + "YourTableName" + " ADD COLUMN " + "YourColumnName" + "TEXT";
db.execSQL(sql);
} catch (Exception localException) {
db.close();
}
}
}
}
public static boolean isColumnExists(SQLiteDatabase sqliteDatabase,
String tableName,
String columnToFind) {
Cursor cursor = null;
try {
cursor = sqliteDatabase.rawQuery(
"PRAGMA table_info(" + tableName + ")",
null
);
int nameColumnIndex = cursor.getColumnIndexOrThrow("name");
while (cursor.moveToNext()) {
String name = cursor.getString(nameColumnIndex);
if (name.equals(columnToFind)) {
return true;
}
}
return false;
} finally {
if (cursor != null) {
cursor.close();
}
}
}
Similar to IF in SQLite, CASE in SQLite is an expression. You can't use ALTER TABLE with it. See: http://www.sqlite.org/lang_expr.html
I am really sorry for posting it late. Posting in the intention of may be helpful in someone's case.
I tried fetching the column from the database. If it returns a row, it contains that column otherwise not...
-(BOOL)columnExists {
BOOL columnExists = NO;
//Retrieve the values of database
const char *dbpath = [[self DatabasePath] UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK){
NSString *querySQL = [NSString stringWithFormat:@"SELECT lol_10 FROM EmployeeInfo"];
const char *query_stmt = [querySQL UTF8String];
int rc = sqlite3_prepare_v2(database ,query_stmt , -1, &statement, NULL);
if (rc == SQLITE_OK){
while (sqlite3_step(statement) == SQLITE_ROW){
//Column exists
columnExists = YES;
break;
}
sqlite3_finalize(statement);
}else{
//Something went wrong.
}
sqlite3_close(database);
}
return columnExists;
}
public static bool columExsist(string table, string column)
{
string dbPath = Path.Combine(Util.ApplicationDirectory, "LocalStorage.db");
connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
DataTable ColsTable = connection.GetSchema("Columns");
connection.Close();
var data = ColsTable.Select(string.Format("COLUMN_NAME='{1}' AND TABLE_NAME='{0}1'", table, column));
return data.Length == 1;
}
Some of these examples didn't worked for me. I'm trying to check whether my table already contains a column or not.
I'm using this snippet:
public boolean tableHasColumn(SQLiteDatabase db, String tableName, String columnName) {
boolean isExist = false;
Cursor cursor = db.rawQuery("PRAGMA table_info("+tableName+")",null);
int cursorCount = cursor.getCount();
for (int i = 1; i < cursorCount; i++ ) {
cursor.moveToPosition(i);
String storedSqlColumnName = cursor.getString(cursor.getColumnIndex("name"));
if (columnName.equals(storedSqlColumnName)) {
isExist = true;
}
}
return isExist;
}
The examples above are querying the pragma table which is a metadata table and not the actual data, each column indicates the names, type and some other stuff about the table's columns. So the actual column names are within the rows.
Hope that this helps someone else.
SELECT INSTR(Lower(sql), " exceptionclass ") FROM sqlite_master WHERE type="table" AND Lower(name)="bugsgroup";
