I need to format a column value in my return but that column may not exist in which case I need to grab the rest of the columns data

Question

I have an app that uses a local sqlite database which has a particular table called 'Theme' for each project. Sometimes that table has a column 'startDate' and sometimes it does not. I need to have that 'startDate' returned in a particular format if it does exist. My problem is, when I query this table, specifying the neccessary format, if the column does not exist, the query returns an error "NO SUCH COLUMN".

HOW DO I CHECK FOR COLUMN EXISTENCE, IF IT DOES EXIST, RETURN THE 'startDate' PROPERLY FORMATTED ALONG WITH THE REST OF THE DATA, IF IT DOES NOT EXIST, RETURN THE REST OF THE DATA WITHOUT THE 'startDate'???

This must be done in 1 query!

Something like this...

SELECT *  (if exists STRFTIME('%Y/%m/%d %H:%M:%S', startDate) AS sDate FROM Theme

possible duplicate of [How to get a list of column names](http://stackoverflow.com/questions/685206/how-to-get-a-list-of-column-names) — Klas Lindbäck, Aug 14 '15 at 15:09
You could use a `select *` and check the columns in the result, see http://stackoverflow.com/a/14004784/1015327 — JimmyB, Aug 14 '15 at 15:16
Or try [`PRAGMA table_info(table_name)`](https://www.sqlite.org/pragma.html#pragma_table_info). — JimmyB, Aug 14 '15 at 15:18
@Hanno Because, as the DB is now, some projects have date info in their 'Theme' table while some do NOT have date info in their 'Theme' table — Peter Baldwin, Aug 14 '15 at 15:28
@Klas 'How to get a list of column names' suggests querying the DB for a list of column names and then using that data. I can only make one query — Peter Baldwin, Aug 14 '15 at 15:30
In that case I'd also be interested in the answer. Does a failed query count as a query? If that's ok, then assume the column exists, and if that fails run the query without the date field. — Klas Lindbäck, Aug 17 '15 at 06:30

score 0 · Answer 1 · answered Aug 18 '15 at 05:08

Only one query:

Cursor cursor = database.query(TABLE_NAME, null, null, null, null, null, null);
if(cursor.moveToFirst()) {
  do {
    for(String columnName : cursor.getColumnNames()) {
      // do something
    }
  } while(cursor.moveToNext());
} else {
  // your actions for empty table
}
cursor.close();

I need to format a column value in my return but that column may not exist in which case I need to grab the rest of the columns data

1 Answers1