Flex 4.6 actionscript 3 executing a SQL alter statment

Question

Hello I have a program that is working, I had a customer ask if I could add a notes section to my program, great idea. I added the following code :

private var insertStmt:SQLStatement;
private function updateTable():void
        {
            insertStmt = new SQLStatement();
            insertStmt.sqlConnection = conn;
            var sql:String = "";
            sql += "ALTER TABLE Customersold ADD Note TEXT;";
            insertStmt.text = sql;
            insertStmt.execute();

that runs fine if the customer is using this version for the first time, but after running it a 2nd time i get an error that the column already exists. How can i fix this?

conn btw is my sql connection.

Edit: This is a program not a mobil app.

Answer 1

Don't run the statement twice. If you ask SQLLite to add a column that is already there, then throwing an error is the expected behaviour.

If you end up with update scripts in your app like this (which is not uncommon) then the usual way to deal with it is to store a version number in your DB and apply upgrades incrementally as needed. As you haven't done that yet, you can also assume that if no version number was present then it's version 1. Just check this somewhere when your app starts up.

Pseudo code:

var version:int = getAppVersionFromDB();
if(version < 2) 
{
    applyV2();
}
if(version < 3) 
{
    applyV3();
}
...  etc ....

Then just update functions similar to as you have now.

private function applyV2():void
{
    ... some schema changes here ...
   updateSavedVersion(2);
}

You get to flesh out the functions yourself.

Answer 2

Your check query change about underline DBMS.

Before your ADD COLUMN statement you must launch this query (different by DBMS):

SQL SERVER

SELECT COUNT(*) FROM syscolumns c
JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'Customersold'
AND c.name = 'Note'

ORACLE

SELECT COUNT(*) FROM ALL_TAB_COLUMNS c
WHERE c.table_name = 'Customersold'
AND c.column_name = 'Note'

MySql

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.table_name = 'Customersold'
AND c.column_name = 'Note'

If the result returns a number > 0 so you don't add your column (because exists)

Your method becames:

private function updateTable():void
{
    var checkStmt:SQLStatement = new SQLStatement();
    checkStmt.sqlConnection = conn;
    var sql:String = "ABOVE QUERY";
    checkStmt.text = sql;
    checkStmt.execute();
    checkStmt.getResult(); <-- Here you get the result of your select

    if (CONDITION ABOUT NOT EXISTING FIELD) {
        insertStmt = new SQLStatement();
        insertStmt.sqlConnection = conn;
        sql = "ALTER TABLE Customersold ADD Note TEXT;";
        insertStmt.text = sql;
        insertStmt.execute();
    }
}