Android Question B4XTable SQLite update advice

Discussion in 'Android Questions' started by DroidLyon, Aug 12, 2019.

  1. DroidLyon

    DroidLyon Member Licensed User

    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:

    1. Are either or both of the first two valid options, or is one better than the other?
    2. Is there anything else I should consider (as it's a multiple update, should SQL1.BeginTransaction be used on the 2nd table for example?)
    3. 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
     

    Attached Files:

    Last edited: Aug 12, 2019
  2. KMatle

    KMatle Expert Licensed User

    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.
     
    mangojack and Peter Simpson like this.
  3. DroidLyon

    DroidLyon Member Licensed User

    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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    The example does everything that you need. It updates the memory db whenever the data is modified:
    Code:
    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.
     
  5. DroidLyon

    DroidLyon Member Licensed User

    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).

    Code:
    'In Activty_create
    PrefDialog.Initialize(Activity"Editable Row"300dip300dip)
    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
     

    Attached Files:

    AnandGupta likes this.
  6. Erel

    Erel Administrator Staff Member Licensed User

    Looks correct. Tip: no need to write Array As Object. It is enough to write Array.
     
    DroidLyon likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice