Checking for a COLUMN before ALTER TABLE

SQLite is one of the many ways to store data on BB10. I find it is easier for me to use sql calls to get the information I want then doing the old BB OS way of using Vectors.  Also, instead of using DataModels to populate a form in BB10, I am calling from the database and then creating a instance of the item to fill in, once I have retrieved the data (I’ll write up another post about that).  The issue with SQLite is that it is “Lite”, which means not all functionality is available in order to reduce the size.  For example, it would be nice to check to see if a COLUMN existed before doing an ALTER TABLE so that an error won’t happen.

For example

IF DOES NOT EXIST ALTER TABLE myTable ADD COLUMN myNewColumn VARCHAR

While you could just let this error out, that’s not a good way to check.  And for a mobile application, you don’t want this error happening every time the application starts.  The work around is to use a PRAGMA statement to check the table for the column before doing the ALTER.   It seems like a bunch more code, but it is probably the best way to prevent just handling an error.  Also, if you have multiple changes to the table based on if a particular column does not exist, then you could add other actions in the ALTER section.

// Check to see if row exists
const QString colCheck = "PRAGMA table_info('myTable')";
bool alterTable = true;
if(query.exec(colCheck)) {
  while(query.next()) {
    // the second value ( 1 ) is the column name
    if(query.value(1).toString() == "myNewColumn") {
      alterTable = false;
    }
  }
} else {
  const QSqlError error = query.lastError();
  qDebug() << tr("ALTER  TABLE ERROR:  %1").arg(error.text());
}
// Alter table if column does not exist
const QString colAdd = "ALTER TABLE ADD COLUMN myNewColumn VARCHAR";
if(alterTable) {
  if(query.exec(colStateAdd)) {
    qDebug() << "COLUMN myNewColumn ADDED";
  } else {
    const QSqlError error = query.lastError();
    qDebug() << tr("ADD COLUMN myNewColumn TABLE ERROR:  %1").arg(error.text());
  }
}