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());
  }
}

About DeanLogic
Dean has been playing around with programming ever since his family got an IBM PC back in the early 80's. Things have changed since BASICA and Dean has dabbled in HTML, JavaScript, Action Script, Flex, Flash, PHP, C#, C++, J2ME and SQL. On this site Dean likes to share his adventures in coding. And since programming isn't enough of a time killer, Dean has also picked up the hobby of short film creation.

About DeanLogic

Dean has been playing around with programming ever since his family got an IBM PC back in the early 80's. Things have changed since BASICA and Dean has dabbled in HTML, JavaScript, Action Script, Flex, Flash, PHP, C#, C++, J2ME and SQL. On this site Dean likes to share his adventures in coding. And since programming isn't enough of a time killer, Dean has also picked up the hobby of short film creation.

3 Replies to “Checking for a COLUMN before ALTER TABLE”

    • I’m using SQLite in the above example, for regular SQL you should be able to use “IF EXISTS”, but for MySQL, you still have to do a select to see if it exists first. T-SQL should work the same as regular SQL, but you should check the documentation just in-case.

Leave a Reply

Your email address will not be published. Required fields are marked *

*