Android Question [SOLVED] SQLite - Remove a column

LucaMs · Sep 13, 2015

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 · Sep 13, 2015

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

B4X:

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 · Sep 13, 2015

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:

B4X:

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 · Sep 13, 2015

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 · Sep 13, 2015

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

LucaMs · Sep 13, 2015

Roycefer said:
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)

Mahares said:
"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 · Sep 13, 2015

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 · Sep 13, 2015

As (I like much more this word, now

) I wrote, just for curiosity, I "splitted" the command:

B4X:

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 · Sep 13, 2015

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 · Sep 13, 2015

LucaMs said:
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 · Sep 13, 2015

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 · Sep 13, 2015

LucaMs said:
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:

B4X:

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 · Sep 13, 2015

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 · Sep 13, 2015

LucaMs said:
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 · Sep 13, 2015

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

Mahares · Sep 13, 2015

LucaMs said:
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

B4X:

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 · Sep 13, 2015

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

00:50

Mahares said:
Vive l'Italie

Thank you

Android Question [SOLVED] SQLite - Remove a column

LucaMs

Expert

Attachments

Roycefer

Well-Known Member

Mahares

Expert

Attachments

Roycefer

Well-Known Member

Mahares

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

Mahares

Expert

LucaMs

Expert

LucaMs

Expert

LucaMs

Expert

Mahares

Expert

LucaMs

Expert