B4J Question [SOLVED] B4XTable, how to add a column to an active table

[swChef]

I could use some direction on how to accomplish something with a B4XTable. I would guess it would work by always reloading the entire table, but I would expect to be able to do it within B4XTable.

I have a B4XTable with something in it (1 column, some rows of values).
I want to add expand it by adding another column to B4XTable1, and then add a row with values in (both) columns.

If I use the following Sub, it fails to add the field ((SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "770756": syntax error)).

Sub AddColumn(sID As String) ' say sID="770756"
    Dim query As String
    query = "ALTER TABLE DATA ADD COLUMN "&sID&" text"
    B4XTable1.SQL1.BeginTransaction
    Try
        B4XTable1.SQL1.ExecNonQuery(query)
        B4XTable1.SQL1.TransactionSuccessful   '*****************************************************
    Catch
        Log("failed to Add "&sID&" Field")
  
    End Try
    'B4XTable1.SQL1.EndTransaction
End Sub

If I use AddColumn, when I try to add a row it complains trying to insert 2 columns into a table with 1 column.

B4XTable1.AddColumn(PN,  B4XTable1.COLUMN_TYPE_TEXT)

Try
    Dim lCells As List
    lCells.Add("a")
    lCells.Add("b")
    B4XTable1.sql1.ExecNonQuery2($"INSERT INTO data VALUES(?,?")"$, lCells)
    B4XTable1.Refresh
Catch
    Log(LastException) ' java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (table data has 1 columns but 2 values were supplied)
End Try

If I try the .AddColumn approach, and add a .RefreshNow (to hopefully force the table to realize the added column), RefreshNow gives exception
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "FROM": syntax error)

Thank you.

 

[LucaMs]

in first sub use :

 query = $" ALTER TABLE DATA ADD COLUMN ${sID} text "$

or

query = "ALTER TABLE DATA ADD COLUMN '"&sID&"' text"

That is anyway wrong, because he passes a field value (770756), not the name of the new table field!

query = "ALTER TABLE DATA ADD COLUMN PN text"

 

[swChef]

That is anyway wrong, because he passes a field value (770756), not the name of the new table field!

query = "ALTER TABLE DATA ADD COLUMN PN text"

That number is the column name. The table cells have other entries in them. It is also a sparse table.

 

[swChef]

After some experimentation
B4XTable1.AddColumn apparently doesn't manage the rows (setting existing rows' new column values to null nor empty string.
If the SQL ALTER TABLE statement is used, B4XTable1.Columns doesn't see the change (added column).
So far I did not find a way to build a sparse table within B4XTable1, and I have to assemble it in a list of arrays outside and then set the data.
Then to add subsequent columns and more sparse data, I have to first retrieve the existing data from B4XTable1, clear B4XTable1's data, alter the data list, and reapply it.
Am I missing something?

 

[LucaMs]

That number is the column name.

Then you must to write it between double quotes, it's a rule.
Try:

Dim query As String
query = "ALTER TABLE DATA ADD COLUMN ? text"
B4XTable1.SQL1.ExecNonQuery2(query, Array As String(sID))

 

[swChef]

Then you must to write it between double quotes, it's a rule.
Try:

Dim query As String
query = "ALTER TABLE DATA ADD COLUMN ? text"
B4XTable1.SQL1.ExecNonQuery2(query, Array As String(sID))

behnam_tr did suggest that earlier, added, but not to post#1. Oddly the examples in the forum for Alter Table do not use this syntax (which could be root cause for one poster's problem).
Still left with the summary in my last post.

 

[swChef]

If SetData is called with an empty list, B4XTable1 apparently never sets up the internal db, as .SQL1 operations don't work.
So in order to start with an empty table (but with several (1+) defined columns), and do INSERT INTO data statements, a dummy row has to be added.
If I add the dummy row, then follow up with SetData( emptylist ) then (at some point, didn't chase it down, but before any Inserts or Updates) it gives an exception, java-sqlexception-with-jdbc-sqlite-misuse.
Otherwise I've managed to achieve the sparse (non-key) data in B4XTable.

 

[swChef]

With more work I found I was able to eliminate issues and workarounds.
I used B4XTable1.AddColumn instead of sql ALTER TABLE.