Android Question change column sql

[harinder]

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

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

 

[DonManfred]

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

 

[harinder]

That means column change is not possible in sqlite

 

[toby]

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]

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

 

[jimmyF]

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

 

[Mahares]

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.

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]

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

 

[Claudio Oliveira]

In this case, all this:

    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:

   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]

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

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

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

Cursor1=SQL1.ExecNonQuery(txt)

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

 

[Claudio Oliveira]

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

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]

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]

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]

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:

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

 

[RB Smissaert]

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:

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

What is the point of the Where clause here?

RBS

 

[Claudio Oliveira]

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:

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]

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]

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]

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]

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)