Android Question change column sql

harinder · Aug 14, 2018

I want the correct syntax for changing column name of a database. Following not working where I want to change names of 2 columns, one from old to new, other from good to bad..thnx

B4X:

Dim chngQuery As String= "ALTER TABLE " &Main.DBTable & " CHANGE COLUMN  old new CHANGE COLUMN good bad"
        Main.SQL1.ExecNonQuery(chngQuery)

DonManfred · Aug 14, 2018

https://www.sqlite.org/lang_altertable.html

harinder · Aug 14, 2018

That means column change is not possible in sqlite

toby · Aug 14, 2018

It's possible. As the link provided by @DonManfred indicates, you can create a new table with correct column names, copy data over and drop the old one.

harinder · Aug 14, 2018

can you tell me how to transfer data from Table1 having columns A,B,C to Table2 having columns D,E,F?

jimmyF · Aug 14, 2018

https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table

Mahares · Aug 14, 2018

harinder said:
can you tell me how to transfer data from Table1 having columns A,B,C to Table2 having columns D,E,F?

I will put in code the steps that was enumerated by @DonManfred, @toby and @jimmyF:
The sequence is as follows:
1. Create a 2nd table with the same structure As the 1st except different col names.
2. Insert all the records from the 1st table into the 2nd table.
3. Delete the 1st table.
4. Rename the 2nd table To the 1st table, unless you do not want to delete the 1st.

B4X:

Dim Cursor1 As Cursor
Dim DBTableName As String ="Table1"
Dim DBTableName2 As String ="Table2"
Dim txt As String

Sub CreateTable
    txt="CREATE TABLE IF NOT EXISTS " & DBTableName2 & " (D TEXT, E TEXT , F TEXT )"
    SQL1.ExecNonQuery(txt)
End Sub

Sub PopulateSecondTable
    txt="SELECT * FROM " & DBTableName
    Cursor1=SQL1.ExecQuery(txt)
    SQL1.BeginTransaction
    For i=0 To Cursor1.RowCount -1
        Cursor1.Position=i
        txt="INSERT INTO " & DBTableName2 & " VALUES (?,?,?)"
        SQL1.ExecNonQuery2(txt, Array As String(Cursor1.GetString("A"), Cursor1.GetString("B"), , Cursor1.GetString("C") ))
    Next

    SQL1.ExecNonQuery("DROP TABLE " & DBTableName)  'Delete the first table
    SQL1.ExecNonQuery("ALTER TABLE " & DBTableName2 & " RENAME TO " & DBTableName)  'rename the 2nd table to the 1st
    SQL1.TransactionSuccessful
    SQL1.EndTransaction
End Sub

harinder · Aug 14, 2018

THANK YOU VERY MUCH Mahares....WORKS LIKE A CHARM

Claudio Oliveira · Aug 14, 2018

In this case, all this:

B4X:

    txt="SELECT * FROM " & DBTableName
    Cursor1=SQL1.ExecQuery(txt)
    SQL1.BeginTransaction
    For i=0 To Cursor1.RowCount -1
        Cursor1.Position=i
        txt="INSERT INTO " & DBTableName2 & " VALUES (?,?,?)"
        SQL1.ExecNonQuery2(txt, Array As String(Cursor1.GetString("A"), Cursor1.GetString("B"), , Cursor1.GetString("C") ))
    Next

Can be replaced by this:

B4X:

   txt=$"INSERT INTO ${DBTableName2} SELECT * FROM ${DBTableName}"$
    SQL1.BeginTransaction
    SQL1.ExecNonQuery(txt)
    SQL1.TransactionSuccessful
    SQL1.EndTransaction

This way, all the copying process is left to SQLite engine to carry out.

Mahares · Aug 14, 2018

Claudio Oliveira said:
txt=$"INSERT INTO ${DBTableName2} SELECT * FROM ${DBTableName} WHERE TRUE"$

That is not a correct syntax with TRUE in there. It needs to be:

B4X:

txt=$"INSERT INTO ${DBTableName2} SELECT * FROM ${DBTableName}"$

Claudio Oliveira said:
Cursor1=SQL1.ExecNonQuery(txt)

ExecNonQuery does not return a cursor. It should be: SQL1.ExecNonQuery(txt)

Claudio Oliveira · Aug 14, 2018

Mahares said:
That is not a correct syntax with TRUE in there. It needs to be:

B4X:

txt=$"INSERT INTO ${DBTableName2} SELECT * FROM ${DBTableName}"$

Unnecesary, yes. But definitely not incorrect. Deleted.

ExecNonQuery does not return a cursor. It should be: SQL1.ExecNonQuery(txt)

That's right. Copy/paste stuff. Corrected.

Mahares · Aug 15, 2018

Claudio Oliveira said:
Unnecesary, yes. But definitely not incorrect. Deleted.

I tested your syntax before I rushed to judgement and indeed it returned the error. Please check it for yourself:
(no such column: TRUE (code 1): , while compiling: INSERT INTO tblVehicles2 SELECT * FROM tblVehicles WHERE TRUE)

Claudio Oliveira · Aug 15, 2018

Mahares said:
I tested your syntax before I rushed to judgement and indeed it returned the error. Please check it for yourself:
(no such column: TRUE (code 1): , while compiling: INSERT INTO tblVehicles2 SELECT * FROM tblVehicles WHERE TRUE)

I tested before with SQLite Expert on Windows and it worked pretty fine.
This syntax is documented in SQLite homepage, so this is weird...

I tested now on B4A and you're absolutely right. Indeed SQLite returns this "no such column" error.
So i've come to the conclusion that it's not a good idea testing SQL for use with B4A in other environments except B4A itself.
One more lesson learned...

Mahares · Aug 15, 2018

Claudio Oliveira said:
So i've come to the conclusion that it's not a good idea testing SQL for use with B4A in other environments except B4A itself.
One more lesson learned...

You can use the number 1 instead of TRUE if you want, although unnecessary because SQLite does not have a Boolean data type class, so 1 is considered TRUE. The below statement will work:

B4X:

strQuery=$"INSERT INTO tblVehicles2 SELECT * FROM tblVehicles WHERE 1"$

RB Smissaert · Aug 15, 2018

Mahares said:
You can use the number 1 instead of TRUE if you want, although unnecessary because SQLite does not have a Boolean data type class, so 1 is considered TRUE. The below statement will work:

B4X:

strQuery=$"INSERT INTO tblVehicles2 SELECT * FROM tblVehicles WHERE 1"$

What is the point of the Where clause here?

RBS

Claudio Oliveira · Aug 15, 2018

Mahares said:
You can use the number 1 instead of TRUE if you want, although unnecessary because SQLite does not have a Boolean data type class, so 1 is considered TRUE. The below statement will work:

B4X:

strQuery=$"INSERT INTO tblVehicles2 SELECT * FROM tblVehicles WHERE 1"$

That's right.
As a matter of fact, this where clause would be necessary only in the case described in SQLite documentation (excerpt attached on post #13). I have not paid attention to this when I posted my code...

Claudio Oliveira · Aug 15, 2018

RB Smissaert said:
What is the point of the Where clause here?

RBS

Absolutely none. It's needed only when using the upsert clause, which is not the present case.

Claudio Oliveira · Aug 15, 2018

By the way, I wouldn't do this by code, because every DB object related to that table would have to be recreated and some of them, like triggers and views, would have to be manually updated and recreated to reflect column name changes.
Generally speaking, good database programming practice is that all DB maintenance should always be carried out by the developer/development team, not by the aplication itself. And when it comes to SQLite, this is more advisable yet, as it isn't natively capable of dealing with all this stuff.
In Oracle, for example, if there are triggers or views referencing a certain column, the DBMS raises an error if you try to change that column's name.
Not by chance SQLite implements only RENAME and ADD COLUMN to its ALTER TABLE command.

harinder · Aug 16, 2018

Thanks Claudio..this necessity arose as I have an initial table with general info in which user also chooses one out of 3 certain categories. So I need to then pass all the general info as per chosen category(using WHERE and GROUP BY and UNION ALL) to a master table having info of all 3 categories in different columns and each category info goes to respective column..
Easier approach of course is to have only one table with info from all sources mapped in it and then use same table for different UI...I will do that tidying later, but I consider the present route as a great learning curve..
The issue I am facing presently is that the following code to pass info from new table created by Mahares and your help (new table with correct columns and correct general info created successfully) is not working as Master table shows a null !! (Only column heads visible)
Tableprior is Master table and Tablepee1 is the new created table with general info and the correct columns..

harinder · Aug 16, 2018

B4X:

Dim Query2 As String=$"
    SELECT ACType,sum(DualDay), sum(DualNite),sum(P1Day), sum(P1Nite),
    sum( P2Day),sum( P2Nite) from (select * from  ${main.DBTableprior}
    UNION ALL SELECT ACType,0,0,0,0,0,0,0,0,0,0,0,0,P1Dayhr,P1Daymin,P1Nitehr,
    P1Nitemin,0,0,0,0,InstrFlghr,InstrFlgmin,0,0 from ${main.DBTablepee1})
    GROUP BY ACType"$

    Main.SQL1.ExecQuery(Query2)

Android Question change column sql

Active Member

Expert

Active Member

Well-Known Member

Active Member

Active Member

Expert

Active Member

Active Member

Expert

Active Member

Expert

Active Member

Expert

Well-Known Member

Active Member

Active Member

Active Member

Active Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency