B4J Question SqLite rename column

[Sergey_New]

This code gives an error:

Sub UpdateKey(tblName As String)
    db.ExecNonQuery("ALTER TABLE " & tblName & " RENAME COLUMN old TO new")
End Sub

Where is the mistake?

 

[DonManfred]

Do you get any Error?

 

[DonManfred]

At least in 2018 it seems it was not possible.

SQLite - How to rename a column?

How to rename a column in SQLite

tableplus.com

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.

 

[Sergey_New]

This is possible in newer versions SqLite.

 

[DonManfred]

The SQLite Version shipped with B4J is 3.7.x.
But you need 3.25.x+

SQLite JDBC - Library version updates

Hi everybody! This thread is supposed to get all SQLite JDBC library version links together so as to find and download them easier. As a heavy B4J+SQLite user, I'm always looking forward to have new JDBC versions. All information contained herein has been taken from Mr. Taro L. Saito (xerial)...

www.b4x.com

Download the newest Release and remember to use

#AdditionalJar: sqlite-jdbc-3.36.0.2

 

[Sergey_New]

Download the newest Release and remember to use

Thank you for help!

 

[aeric]

'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 |