B4J Question Problem Drop Column

[Guenter Becker]

Hello,
I have a problem in managing SQLite 3 database.
Adding a new column to table works fine.
deleting the column wether it has values or not raises an error I do'nt understand why.

Dim cmd As StringBuilder:cmd.initialize      
cmd.Append("alter table ").Append(Tablename).append(" drop column ").Append(CellClicked)
Log(cmd.ToString) ' > alter table gbe drop column GB
sql.ExecNonQuery(cmd.tostring) '<----- Error

Waiting for debugger to connect...
Program started.
GB
alter table gbe drop column GB
(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "drop": syntax error)

#AdditionalJar: sqlite-jdbc-3.46.0.0
#AdditionalJar: slf4j-api-2.0.13
'The reference to slf4j-nop is not mandatory. However SLF4J will issue a warning, that can be ignored.
'So, for a cleaner log add this
#AdditionalJar: slf4j-nop-2.0.13.jar

''' Database with DBBrowser 3.13.1
** Database is there and SQL is initialized.
** Column GB is added to the table gbe and it's checked that it is there
** single user action no database locks

Thank you for help!

 

[aeric]

Which SQLite version are you using?
Try version 3.36+

 

[Guenter Becker]

I did it but it does not work again. Thank you for your help.

After some hours investigating the internet and trial&error I found the solution! The error is within SQLite!

This is the solution that works (B4A, B4J:

1st step:
create a list columnlist and fill it with the table gbe columnnames (PRAGMA table_info(gbe)).
2nd step:
remove the columname to be delete from the list (columnlist .removeat(columnlist .indexof(columnname)).
3d step:
create string col (Dim col as string)
fill it with the remaining columnnames from the list (Example. id,source)
4th step
remove the column from the table gbe

sql.BeginTransaction
Dim cmd As StringBuilder:cmd.initialize
'# create temporary table without column to be deleted
cmd.Append("CREATE TEMPORARY TABLE t1_backup").Append("(").append(col).append(")")
sql.ExecNonQuery (cmd.tostring)
'# insert data in backup table from origin
cmd.initialize
cmd.Append("INSERT INTO t1_backup Select ").Append(col).append(" FROM " & Tablename)
sql.ExecNonQuery(cmd.ToString)
'# deleze table origin
sql.ExecNonQuery("DROP TABLE " & Tablename)
'# create new table origin
cmd.initialize
cmd.Append("CREATE TABLE ").Append(Tablename).Append("(").append(col).append(")")
sql.ExecNonQuery(cmd.ToString)
'# fill saved data into new oeigin
cmd.initialize
cmd.Append("INSERT INTO ").append(Tablename).Append(" Select ").append(col).append(" FROM t1_backup")
sql.ExecNonQuery(cmd.ToString)
'# delete backup table
sql.ExecNonQuery("DROP TABLE t1_backup")
sql.TransactionSuccessful
'# create/reload B4XTable
loadData

 

[aeric]

I made a quick test with no problem.

#AdditionalJar: sqlite-jdbc-3.39.3.0

Sub Class_Globals
    Private Root As B4XView
    Private DB As SQL
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    RunTest
End Sub

Sub RunTest
    DB.InitializeSQLite(File.DirApp, "test.db", True)
    Dim qry As String
    qry = $"CREATE TABLE "gbe" ("id"    INTEGER, "QA" TEXT, "QB" TEXT, "QC" TEXT, PRIMARY KEY("id" AUTOINCREMENT))"$
    DB.AddNonQueryToBatch(qry, Null)
    qry = $"INSERT INTO gbe (QA, QB, QC) SELECT ?, ?, ?"$
    DB.AddNonQueryToBatch(qry, Array As String("A1", "BB1", "CCC1"))
    DB.AddNonQueryToBatch(qry, Array As String("A2", "BB2", "CCC2"))
    Dim SenderFilter As Object = DB.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("Create: " & Success)
   
    qry = "SELECT * FROM gbe"
    Dim res As ResultSet = DB.ExecQuery(qry)
    PrintTable(res)
   
    qry = $"ALTER TABLE "gbe" DROP COLUMN "QB""$
    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 gbe"
    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.
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
Create: true
id | QA | QB | QC |
1 | A1 | BB1 | CCC1 |
2 | A2 | BB2 | CCC2 |
Alter: true
id | QA | QC |
1 | A1 | CCC1 |
2 | A2 | CCC2 |

Edit: It may not work in Android because it is using an outdated version of SQLite.

 

[klaus]

Maybe the SQLite documentation could help you, there are some restrictions.