Android Question [SOLVED] SQLite - Remove a column

[LucaMs]

I found this statement (http://www.sqlite.org/faq.html#q11):

_______________________________________________________________________________________

(11) How do I add or delete columns from an existing table in SQLite.

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
______________________________________________________________________________________


So I tried the example (see the attached projet) and it does NOT work.

I tried also those queries (single sql query) in SQLite Expert Personal 3 and it works perfectly.

Any idea why?


Thank you


[In a my real project I tried using distinct queries, and it ALMOST works: it removes a column but does not copy data]

 

[Roycefer]

This sub has the desired effect when I run it in B4J:

Sub btnRemoveC_Click
' ***   EXAMPLE FROM http://www.sqlite.org/faq.html#q11   ***
'BEGIN TRANSACTION;
'CREATE TEMPORARY TABLE t1_backup(a,b);
'INSERT INTO t1_backup SELECT a,b FROM t1;
'DROP TABLE t1;
'CREATE TABLE t1(a,b);
'INSERT INTO t1 SELECT a,b FROM t1_backup;
'DROP TABLE t1_backup;
'COMMIT;

    mDB.BeginTransaction
   
    Try
   
'        Dim sbQuery As StringBuilder
'        sbQuery.Initialize
'       
'        sbQuery.Append("BEGIN TRANSACTION;")
'        sbQuery.Append("CREATE TEMPORARY TABLE t1_backup(a,b);")
'        sbQuery.Append("INSERT INTO t1_backup SELECT a,b FROM t1;")
'        sbQuery.Append("DROP TABLE t1;")
'        sbQuery.Append("CREATE TABLE t1(a,b);")
'        sbQuery.Append("INSERT INTO t1 SELECT a,b FROM t1_backup;")
'        sbQuery.Append("DROP TABLE t1_backup;")
'        sbQuery.Append("COMMIT;")
'       
'        mDB.ExecNonQuery(sbQuery.ToString)
       
        mDB.ExecNonQuery("CREATE TABLE t1_backup(a,b)")
        mDB.ExecNonQuery("INSERT INTO t1_backup SELECT a,b FROM t1")
        mDB.ExecNonQuery("DROP TABLE t1")
        mDB.ExecNonQuery("CREATE TABLE t1(a,b)")
        mDB.ExecNonQuery("INSERT INTO t1 SELECT a,b FROM t1_backup")
        mDB.ExecNonQuery("DROP TABLE t1_backup")
        mDB.TransactionSuccessful
       
    Catch
   
        Log(LastException.Message)
       
    End Try
End Sub

As a side note, in your version of this sub, you were calling mDB.ExecQuery(sbQuery.ToString), which won't work because sbQuery.ToString isn't a query (it doesn't return a result). But that line is commented out in my version, anyhow.

 

[Mahares]

I also use this code in B4A that compares to Roy's and get a new table with 2 columns. Here is the full sub:

Sub btnRemoveC_Click
 
    mDB.Close
    If mDB.isinitialized =False Then mDB.Initialize(File.DirRootExternal, "ABC.db", False)

    lblData.Text = "" 
    Try
    mDB.BeginTransaction
    Dim MyQuery As String
    MyQuery ="CREATE TABLE  t1_backup  AS SELECT a,b FROM t1"
    mDB.ExecNonQuery(MyQuery)

    mDB.ExecNonQuery("DROP TABLE IF EXISTS t1")

    MyQuery="ALTER TABLE t1_backup RENAME TO t1"
    mDB.ExecNonQuery(MyQuery)
    mDB.TransactionSuccessful
    mDB.EndTransaction
    
        DisplayData    
        ToastMessageShow("Now the db should have only a and b columns", True)    
    Catch
        Msgbox(LastException.Message, "")    
    End Try
End Sub

Here is the full function project attached:

 

[Roycefer]

I just adapted your SQL commands to get them to work but Mahares' solution is more concise and will probably execute faster and use less memory.

 

[Mahares]

@Roycefer and @LucaMs :
I included the full functional project in my post if you want to check it out.

 

[LucaMs]

As a side note, in your version of this sub, you were calling mDB.ExecQuery(sbQuery.ToString), which won't work because sbQuery.ToString isn't a query (it doesn't return a result)

I tried both ExecNonQuery and ExecQuery and the project did not work. As I wrote, using SQLite Expert Personal 3 is works, writing the query as it were a single query.
Probably, for that "multi line query", ExecNonQuery should be better, but anyway it does not work.

Writing (executing) each query separately it removes the column but does not copies data (in my project, I have to test with these table and columns)

"CREATE TABLE t1_backup AS SELECT a,b FROM t1"

"nice" that "AS SELECT"; usually you use "AS" to rename (create an alias) table/fields.

I'm going to try your project, @Mahares, thank you, and, just for curiosity, the original project, changing to multi commands.


Thank you, @Roycefer & @Mahares
.

 

[LucaMs]

AS you know, your project works perfectly, @Mahares.

(Just one thing: probably you should put mDB.EndTransaction outside the try-catch-end try block).


Thank you again.

 

[LucaMs]

As (I like much more this word, now ) I wrote, just for curiosity, I "splitted" the command:

mDB.ExecNonQuery("BEGIN TRANSACTION")
mDB.ExecNonQuery("CREATE TEMPORARY TABLE t1_backup(a,b)")
mDB.ExecNonQuery("INSERT INTO t1_backup SELECT a,b FROM t1")
mDB.ExecNonQuery("DROP TABLE t1")
mDB.ExecNonQuery("CREATE TABLE t1(a,b)")
mDB.ExecNonQuery("INSERT INTO t1 SELECT a,b FROM t1_backup")
mDB.ExecNonQuery("DROP TABLE t1_backup")
mDB.ExecNonQuery("COMMIT")

More strange result:

This time the data was copied but the column was not eliminated!

 

[LucaMs]

Last important "question" (I'm going to check it):

when you use method
a) "CREATE TEMPORARY TABLE t1_backup(a,b)"; "INSERT INTO t1_backup SELECT a,b FROM t1");
or
b) CREATE TABLE t1_backup AS SELECT a,b FROM t1

the properties of the fields are preserved? For example, I use the "default value" property and I don't know if...!

Very probably not using a): I suppose it creates two new fields in a new table with default properties (TEXT and no more).
Method b)... I have to check

 

[LucaMs]

Last important "question" (I'm going to check it):

when you use method
a) "CREATE TEMPORARY TABLE t1_backup(a,b)"; "INSERT INTO t1_backup SELECT a,b FROM t1");
or
b) CREATE TABLE t1_backup AS SELECT a,b FROM t1

the properties of the fields are preserved? For example, I use the "default value" property and I don't know if...!

Very probably not using a): I suppose it creates two new fields in a new table with default properties (TEXT and no more).
Method b)... I have to check

Unfortunately, also the method b) loses the properties of the fields.
So, I must use the same query used to create the original table. This means, anyway, you could not use these methods if you work with a database copied from the assets folder (unless you do not build a function (library) which obtains all the necessary information, a huge job).

 

[LucaMs]

Well, now my project works.

Four steps:
1) create a new table using the same code used to create the "main table", excluding one field (it is necessary because of the loss of the properties using the other methods);
2) copy data from "main table" to "new table";
3) drop "main table"
4) rename "new table" to "main table".

BTW, my project did not copy the data just because... I was using in some cases ExecNonQuery and in others (copy) ExecQuery. Damn carelessness . I'M TIRED )


So, we "need" a library (or code module) to get all properties of a table to recreate it and use it when we want remove a field, when you use a "prefabricated" db.
If I will remember this (uhm... altmost impossible ) and I will have time...!

 

[Mahares]

This time the data was copied but the column was not eliminated!

@LucaMs:
If you want your code in post #8 to work properly, use this for your sub. It will work and the old column will be eliminated:

Sub btnRemoveC_Click
    mDB.Close
    If mDB.isinitialized =False Then mDB.Initialize(File.DirRootExternal, "ABC.db", False)

    lblData.Text = "" 
    'DoEvents
    Try
        mDB.ExecNonQuery("BEGIN TRANSACTION")
        mDB.ExecNonQuery("CREATE TEMPORARY TABLE t1_backup(a,b)")
        mDB.ExecNonQuery("INSERT INTO t1_backup SELECT a,b FROM t1")
        mDB.ExecNonQuery("DROP TABLE t1")
        mDB.ExecNonQuery("CREATE TABLE t1(a,b)")
        mDB.ExecNonQuery("INSERT INTO t1 SELECT a,b FROM t1_backup")
        mDB.ExecNonQuery("DROP TABLE t1_backup")
        mDB.ExecNonQuery("COMMIT")
               
        DisplayData
       
        ToastMessageShow("Now the db should have only a and b columns", True)
    Catch
        Msgbox(LastException.Message, "")
    End Try
End Sub

 

[LucaMs]

I should split the web page to compare code in #8 and yours, @Mahares .

Anyway, I'm trying to "do it" (copy&paste both)...

 

[LucaMs]

I should split the web page to compare code in #8 and yours, @Mahares .

Anyway, I'm trying to "do it" (copy&paste both)...

Probably you are tired like me, @Mahares, or I am much more tired than I realize; to me the codes seems the same (but my eyes are dancing an old twist )

 

[LucaMs]

Anyway, you need to preserve the properties of the fields, obviously; then you need other methods, necessarily.

 

[Mahares]

Probably you are tired like me, @Mahares, or I am much more tired than I realize; to me the codes seems the same (but my eyes are dancing an old twist

I only added these 2 lines to that sub. That is all. It works

mDB.Close
If mDB.isinitialized =FalseThen mDB.Initialize(File.DirRootExternal, "ABC.db", False)

Go to sleep now, it is probably midnight or after in Roma. Vive l'Italie

 

[LucaMs]

Go to sleep now, it is probably midnight or after in Roma

00:50

Vive l'Italie

Thank you