B4J Question B4XTable - AddColumn after data has already been loaded

[Alexander Stolte]

if i have already filled the B4XTable with data, i would like to add a new column. if i use the following code:

B4XTable1.AddColumn(input.Text, B4XTable1.COLUMN_TYPE_TEXT)
B4XTable1.Refresh

comes this error:

Error occurred on line: 427 (B4XTable)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "FROM": syntax error)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.throwex(DB.java:374)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:123)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
    at org.sqlite.Conn.prepareStatement(Conn.java:404)
    at org.sqlite.Conn.prepareStatement(Conn.java:399)
    at org.sqlite.Conn.prepareStatement(Conn.java:383)
    at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:365)
    at b4j.example.b4xtable$ResumableSub_ImplUpdateDataFromQuery.resume(b4xtable.java:2780)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resumeAsUserSub(DebugResumableSub.java:47)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resume(DebugResumableSub.java:42)
    at anywheresoftware.b4a.keywords.Common$2$1.run(Common.java:1050)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
    at java.base/java.lang.Thread.run(Thread.java:832)

 

[LucaMs]

B4XTable uses an in-memory SQLite DB.
I'm afraid you have to use the Clear method and SetData again with new data, as if you didn't do anything to begin with.

However, I want to investigate further, because B4XTable provides the DB and the table name it uses is "data".

 

[LucaMs]

However, I want to investigate further, because B4XTable provides the DB and the table name it uses is "data".

No, I tried adding a new field to that table ("data") but it wasn't enough to avoid the crash.
You have to create everything from scratch, as I wrote before.

 

[Alexander Stolte]

Makes no sense to me why that shouldn't work. With SQLite you can also add columns afterwards...

 

[LucaMs]

Makes no sense to me why that shouldn't work. With SQLite you can also add columns afterwards...

Add B4XTable.bas to your project, extracting it from the library. Try running the project step-by-step, since you add the new column to the B4XTable.
I didn't have much patience doing it; you can probably get to what you need, perhaps by modifying B4XTable and perhaps without this being essential.

 

[Mahares]

Makes no sense to me why that shouldn't work. With SQLite you can also add columns afterwards...

I didn't have much patience doing it

How about something like this. Seems to work

Dim Data As List
    Data.Initialize        
    For i=1 To l.Size-1   '
        Dim row() As String= l.Get(i)
        Data.Add(row)
    Next
    B4XTable1.SetData(Data)
    
    Dim cx As B4XTableColumn 
    cx.Id = "Country"
    cx.SQLID = "c" & (B4XTable1.Columns.Size)
'    cx.SQLID = "c" & (B4XTable1.Columns.Size -1)  'use this line to test if you know the col already exists
    
    If TableColumnExists("data", cx.SQLID ) =  False Then
        B4XTable1.sql1.ExecNonQuery($"ALTER TABLE data ADD ${cx.SQLID} TEXT"$)
        Log("new column added")    
        B4XTable1.AddColumn(cx.id, B4XTable1.COLUMN_TYPE_TEXT).Width =100dip
        B4XTable1.SetData(Data)
        B4XTable1.sql1.ExecNonQuery2($"UPDATE data SET ${cx.SQLID} = ? "$, Array As String("Italia"))
        B4XTable1.Refresh
    Else
        Log("Column already exists")    
    End If
End Sub

Sub TableColumnExists(TableName As String, ColumnName As String) As Boolean
    Dim res As ResultSet
    res = B4XTable1.sql1.ExecQuery("PRAGMA table_info(" & TableName & ")")
    Do While res.NextRow
        Dim MyColumn As String = res.GetString("name")
'        Log("col name " & cur.GetString("name"))
        If MyColumn.Trim.ToUpperCase = ColumnName.ToUpperCase Then Return True
    Loop
    Return False
End Sub

 

[LucaMs]

For i=1 To l.Size-1

But what l is?

B4XTable1.SetData(Data)

I assume that "data" already contains the data of the added column, so I might as well use a Clear and SetData again.

Dim cx As B4XTableColumn cx.Id = "Country"

Uninitialized cx doesn't raise an exception?

 

[Mahares]

assume that "data" already contains the data of the added column, so I might as well use a Clear and SetData again

No. The first SetData does not contain the new column. You need to test it before you react. I tested it on one of my project and it works

Uninitialized cx doesn't raise an exception?

No. it does not raise an exception. Why don't you test it. If not I can post a project to demonstrate it

 

[LucaMs]

You need to test it before you react

Don't beat me up , it was a question.

Why don't you test it.

I didn't have much patience doing it

... and doing something else.

If not I can post a project to demonstrate it

I don't need proof, I trust what you post.
Maybe it will be useful to @Alexander Stolte.

 

[Mahares]

don't need proof, I trust what you post.
Maybe it will be useful to @Alexander Stolte.

I will be glad to post a project Here it is: before and after. For LucaMS, I will do anything