B4J Question SqLite rename column

Sergey_New

Well-Known Member
Licensed User
Longtime User
This code gives an error:
B4X:
Sub UpdateKey(tblName As String)
    db.ExecNonQuery("ALTER TABLE " & tblName & " RENAME COLUMN old TO new")
End Sub
Where is the mistake?
 
Solution
The SQLite Version shipped with B4J is 3.7.x.
But you need 3.25.x+


Download the newest Release and remember to use
B4X:
#AdditionalJar: sqlite-jdbc-3.36.0.2

DonManfred

Expert
Licensed User
Longtime User
Do you get any Error?
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
At least in 2018 it seems it was not possible.
Unfortunately, SQLite does not directly support the ALTER TABLE RENAME COLUMN statement that allows you to rename an existing column of a table.
The workaround for that is to create a new table with the list of column names that you want, copy data from the old table to the new table, then drop the old table.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
The SQLite Version shipped with B4J is 3.7.x.
But you need 3.25.x+


Download the newest Release and remember to use
B4X:
#AdditionalJar: sqlite-jdbc-3.36.0.2
 
Last edited:
Upvote 2
Solution

aeric

Expert
Licensed User
Longtime User
B4X:
'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
#End Region
#AdditionalJar: sqlite-jdbc-3.36.0.2

Sub Process_Globals
    Private DB As SQL
End Sub

Sub AppStart (Args() As String)
    RunTest
    StartMessageLoop
End Sub

Sub RunTest
    DB.InitializeSQLite(File.DirApp, "test.db", True)
    
    Dim qry As String
    
    qry = $"CREATE TABLE "table1" ("id"    INTEGER, "oldcolumn" TEXT, PRIMARY KEY("id" AUTOINCREMENT))"$
    DB.AddNonQueryToBatch(qry, Null)
    qry = $"INSERT INTO table1 (oldcolumn) SELECT ?"$
    DB.AddNonQueryToBatch(qry, Array As String("AAA"))
    DB.AddNonQueryToBatch(qry, Array As String("BBB"))
    Dim SenderFilter As Object = DB.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("Create: " & Success)
    
    qry = "SELECT id, oldcolumn FROM table1"
    Dim res As ResultSet = DB.ExecQuery(qry)
    PrintTable(res)
    
    qry = $"ALTER TABLE "table1" RENAME COLUMN "oldcolumn" TO "newcolumn""$
    DB.AddNonQueryToBatch(qry, Null)
    Dim SenderFilter As Object = DB.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("Alter: " & Success)
    
    qry = "SELECT * FROM table1"
    Dim res As ResultSet = DB.ExecQuery(qry)
    PrintTable(res)
End Sub

Sub PrintTable (Res As ResultSet)
    Dim sb As StringBuilder
    sb.Initialize
    For i = 0 To Res.ColumnCount - 1
        sb.Append(Res.GetColumnName(i)).Append(" | ")
    Next
    Log(sb.ToString)
    Do While Res.NextRow
        Dim sb As StringBuilder
        sb.Initialize
        For i = 0 To Res.ColumnCount - 1
            sb.Append(Res.GetString2(i)).Append(" | ")
        Next
        Log(sb.ToString)
    Loop
    Res.Close
End Sub

Waiting for debugger to connect...
Program started.
Create: true
id | oldcolumn |
1 | AAA |
2 | BBB |
Alter: true
id | newcolumn |
1 | AAA |
2 | BBB |
 
Upvote 0
Top