Android Question B4XTable SQLite update advice

DroidLyon · Aug 12, 2019

Hello Everyone

I'm trying to understand how best to update SQLite from a B4xtable and would like some advice.

I'm struggling slightly because the help docs b4xtable help is showing nothing for me for some reason, plus the closest examples I've found
B4xTable load data - loads from sqllite but doesn't update SQL
B4xTable editable form -loads and writes from csv, but it seems to me at B4xtable close, that it writes the entire contents of B4xtable to csv which may of course not be suitable for SQLite ?

So I've written a new version of the load SQL example above, to contain three separate B4xtables to see what happens. Each B4xTable is built on button click and loads from the same SQLite table

If B4xTable 1 is updated a Sql.ExecNonQuery2 takes place immediately after the B4XTable1.sql1.ExecNonQuery2 in the CellLongClicked to update SQLite after you have changed a value using an inputtemplate on an individual cell

If B4xTable2 is modified then the update takes place in the DataUpdated event (whenever any data is updated on the visible screen?), - again after you have changed a value using an inputtemplate on an individual cell

If B4xtable3 is modified it holds the values as you move from B4xtable3 screens (next, previous etc) but loses values if move to another B4xtable (as expected as the data updates only exists in the b4xtable3 memory)

I expect it may depend on the what the B4xtables are used for, but my questions are:

Are either or both of the first two valid options, or is one better than the other?
Is there anything else I should consider (as it's a multiple update, should SQL1.BeginTransaction be used on the 2nd table for example?)
Would a delete and recreate table (on activity pause If UserClosed be valid like the CSV example) be valid in some circumstances?

Thanks in advance

KMatle · Aug 12, 2019

To me it's just a design decision how to edit the data

a) inside the table
b) let a user click on a row and Display the editable values in extra views or so

Question: Why do you want to drop and recreate the db/table? Why are there so many changes? Usually a user does not Change so many rows.

Additional: You usually don't drop a table or db, it will just be updated or some rows deleted, but not dropped.

DroidLyon · Aug 12, 2019

Thanks KMatle for reply

Firstly, yes I agree that dropping a table is the least favoured approach and I only even considered it as an option because the CSV example above did this for a CSV file, (and as a amateur) I simply linked the two. I would always have gone for one of the first two methods that I use, which appear to give real time updates to the tables with the benefits that that brings.

I also accept your point about the design having an impact on what you do, and recognise that with B4xtable there are multiple ways of updating it (and consequently SQLite)

So if I was to shorten my first post to say that with the learning example I attach, a user wants to simply update a single field for one customer record(ie first name), and using the inputtemplate method as suggested in other B4xtable examples, would it be better writing the Sql update in the CellLongClicked or DataUpdated event?

TQ

Erel · Aug 12, 2019

The example does everything that you need. It updates the memory db whenever the data is modified:

B4X:

Private Sub ShowDialog(Item As Map, RowId As Long)
   Wait For (PrefDialog.ShowDialog(Item, "OK", "CANCEL")) Complete (Result As Int)
   If Result = xui.DialogResponse_Positive Then
       Dim params As List
       params.Initialize
       params.AddAll(Array(Item.Get("Type"), Item.Get("Name"), Item.Get("Birth Date"), Item.Get("Sex"))) 'keys based on the template json file
       If RowId = 0 Then 'new row
           B4XTable1.sql1.ExecNonQuery2($"INSERT INTO data VALUES("", ?, ?, ?, ?)"$, params)
           B4XTable1.ClearDataView
       Else
           params.Add(RowId)
           'first column is c0. We skip it as this is the "edit" column
           B4XTable1.sql1.ExecNonQuery2("UPDATE data SET c1 = ?, c2 = ?, c3 = ?, c4 = ? WHERE rowid = ?", params)
           B4XTable1.Refresh
       End If
   End If
End Sub

All you need to do is to create similar SQL commands and update or insert into your database. The only difference is that the column names will be different.

DroidLyon · Aug 16, 2019

Thanks again. Can I just check I'm doing this correctly please?

1) You intended that I use the Prefdialog lib with a json form built by form builder etc
2) And that the showdialog sub should be called in the Celllongclicked event (and depending can update both B4xtable memory and SQL).

B4X:

'In Activty_create
PrefDialog.Initialize(Activity, "Editable Row", 300dip, 300dip)
PrefDialog.LoadFromJson(File.ReadString(File.DirAssets, "Customers.json"))


Sub B4XTable5_CellLongClicked (ColumnId As String, RowId As Long)
        
    Dim RowData As Map = B4XTable5.GetRow(RowId)
    ShowDialog5(RowData,RowId)

End Sub

Private Sub ShowDialog5(Item As Map, RowId As Long)
    
    Wait For (PrefDialog.ShowDialog(Item, "OK", "CANCEL")) Complete (Result As Int)
    If Result = xui.DialogResponse_Positive Then
        Dim params As List
        params.Initialize
        params.AddAll(Array( Item.Get("Name"), Item.Get("Company"), Item.Get("Address") )) 'keys based on the template json file
                    If RowId = 0 Then 'new row
                            ' insert not undertaken in this call
'                                B4XTable5.sql1.ExecNonQuery2($"INSERT INTO data VALUES("", ?, ?, ?, ?)"$, params)
'                                B4XTable5.ClearDataView
                    Else
                        params.Add(RowId)
                        'first column is c0. We skip it as this is the "edit" column
                            B4XTable5.sql1.ExecNonQuery2("UPDATE data SET c1 = ?, c2 = ?, c3 = ? WHERE rowid = ?", params)
                            B4XTable5.Refresh
                    sql.ExecNonQuery2("UPDATE customers SET LastName = ? , Company = ?, Address  = ?  WHERE rowid =  ?" , Array As Object(Item.GetValueAt(1), Item.GetValueAt(2),Item.GetValueAt(3), RowId))               
        End If
    End If
    
End Sub

Thanks

For anyone interested in this kind of thing here's an high level overview and code is attached.

Table 1 - uses an XUI View B4xInputtemplate called on CellLongClicked on a single field and just updates table memory
Table 2 - uses an Inputtemplate as 1 butupdates both table memory and SQL
Table 3 - uses an Inputtemplate as 1 and updates table memory on CellLongClicked but SQL in Dataupdated event
Table 4 - PrefDialog json form on CellLongClicked and uses a sub to update table Memory
Table 5 - PrefDialog json form on CellLongClicked and uses a sub to update table Memory & SQL
Table 6 - Has "+" button which calls a similar sub as above but sends a row 0 parameter and then uses a prefdialog to insert new record

Erel · Aug 18, 2019

Looks correct. Tip: no need to write Array As Object. It is enough to write Array.

Android Question B4XTable SQLite update advice

DroidLyon

Member

Attachments

KMatle

Expert

DroidLyon

Member

Erel

B4X founder

DroidLyon

Member

Attachments

Erel

B4X founder

Android Question B4XTable SQLite update advice

DroidLyon

Member

Attachments

KMatle

Expert

DroidLyon

Member

Erel

B4X founder

DroidLyon

Member

Attachments

Erel

B4X founder

Privacy & Transparency

Privacy & Transparency