Android Question SQL DROP COLUMN error

[Arf]

I've encountered a error when I try drop a table.

In a previous DDB schema upgrade, I did this:

Sub UpdateDB3_4(sq As SQL)
    'add personal Best
    SQL.ExecNonQuery("ALTER TABLE Patients ADD COLUMN pBestFVC Integer")

And now I am trying to remove it in another upgrade, as I ended up not needing it:

Sub UpdateDB4_5(sq As SQL)
    'and get rid of peronal bests again
    SQL.ExecNonQuery("ALTER TABLE Patients DROP COLUMN pBestFVC")

But I get an error:
android.database.sqlite.SQLiteException: near "DROP": syntax error (code 1): , while compiling: ALTER TABLE Patients DROP COLUMN pBestFVC

Tried to search but couldn't make any headway.

 

[Roycefer]

http://www.sqlite.org/faq.html#q11

 

[Lahksman]

Drop column is not supported in sqlite.
The only option is creating a new table and droping the old one.

check this article, it discribes the different steps.

@Roycefer beat me to it

 

[imbault]

You cannot drop a column in SQLite, limitation of SQLite.

workaround: recreate a new table, import data then drop old table

You both, beat me

 

[Mahares]

Hi @Arf
There is no statement to DROP a column. Here is what you can do:
1. create new table as the one you are trying to change with fewer columns,
2. copy all data,
3. drop old table,
4. rename the new one
Here is the process:

Dim TableOrig= "tblChemicals" , TableNew="tblChemicalsNew" As String
SQL1.BeginTransaction
  'DELETE TABLE IF IT EXISTS
  txt="DROP TABLE IF EXISTS " & TableNew
  SQL1.ExecNonQuery(txt)

  'CREATE NEW TABLE WITH FEWER COLUMNS FROM ORIGINAL TABLE
  txt="CREATE TABLE IF NOT EXISTS " & TableNew & " AS SELECT Code, Chemical, Price, Unit " _
  & "FROM " & TableOrig
  SQL1.ExecNonQuery(txt)

  'DELETE ORIGINAL TABLE IF IT EXISTS
  txt="DROP TABLE IF EXISTS " & TableOrig
  SQL1.ExecNonQuery(txt)

  'RENAME NEW TABLE TO ORIGINAL TABLE NAME
  txt= "ALTER TABLE " & TableNew & " RENAME TO " & TableOrig
  SQL1.ExecNonQuery(txt)

  SQL1.TransactionSuccessful
  SQL1.EndTransaction

 

[Arf]

Excellent, thanks all.