Android Question [ANSWERED] SQLite records not updating

Discussion in 'Android Questions' started by Lee Gillie CCP, Jun 11, 2015.

  1. Lee Gillie CCP

    Lee Gillie CCP Active Member Licensed User

    My first DB project with B4A so probably something basic I am doing wrong. The idea is to store a presentation order column in a table, allowing the user to rearrange the order persistently. But I can't seem to get the initial load of this field to take. I did determine you can not submit update queries while a cursor is open, so the update is deferred....

    Code:
    Sub Activity_Create(FirstTime As Boolean)

        ...

        
    Dim DeliveryPath As String = DBUtils.CopyDBFromAssets("Delivery.db" )
        Delivery.Initialize(DeliveryPath,
    "Delivery.db"False )
        
    Dim curRow As Int

        ...

        
    Try
            
    ' Note: PresentationOrder initially zero for all records.
            Dim lst As List
            lst.Initialize
            crs = Delivery.ExecQuery(
    "SELECT * FROM OpenOrderHeader ORDER BY ShipToAddress1, OrderNumber")
            
    For curRow = 0 To crs.RowCount-1
                crs.Position = curRow
                
    Dim ordn As Int = crs.GetInt("OrderNumber")
                
    Dim mp As Map
                mp.Initialize
                mp.Put(
    "OrderNumber",ordn)
                lst.Add(mp)
            
    Next
            crs.Close
            
    For i = 0 To lst.Size-1
                DBUtils.UpdateRecord(Delivery,
    "OpenOrderHeader","PresentationOrder",i,lst.Get(i))
            
    Next
        
    Catch
            
    Log(LastException.Message)
        
    End Try
       

        crs = Delivery.ExecQuery(
    "SELECT * FROM OpenOrderHeader ORDER BY PresentationOrder")
        
    ' PresentationOrder is zero for all records in this cursor
        ' expecting sequentially assigned #s from the above update
     
  2. Lee Gillie CCP

    Lee Gillie CCP Active Member Licensed User

    Not really, but I suppose I could make that work.

    order #s start at around 710000 for example. Presentation order is like 0, 1, 2, 3, 4, 5... sequentially assigned. In this way when a user re-orders the items in the scroll view, that order can be persisted in the database. But I must make a default initial load of presentation order field.

    These are delivery orders. We begin by ordering by company name, and then order #. But the driver will manually rearrange by the order he wants to unload his truck. Probably only 3-10 orders at a time per tablet. But can't seem to make that initial load "stick".
     
  3. Lee Gillie CCP

    Lee Gillie CCP Active Member Licensed User

    I got to the bottom of this. Still don't know why it happened, but it is making sense.

    I exported this table from MS SQL Server to a TAB delimited file. I then imported to SQLite with Valentina Studio 5. And then dropped the resulting db file to the files folder for B4A to deploy.

    The OrderNumber field is text, and uniquely identifies each record. As it turns out apparently somebody added an unprintable character to the field. I don't know what it is, but when I step through code which does a crs.GetString the B4A debugger shows a square block after the last digit. Since above I used crs.GetInt to load the key value for later use, it NEVER matches the order number in the update query. As such an "=" query predicate never works.

    This was really tough to see in a 3rd party SQLite tool I was using. It trimmed the extra character out before display.

    I suspect the bug may be in Valentina Studio.

    Some code changes to avoid the nice auto-stripping of characters the implicit string-to-integer conversion in B4A does (which I suspected would do an exact match, preserving that invisible character) and now SQLite complains with...
    android.database.sqlite.SQLiteException: unrecognized token: "'295327" (code 1): , while compiling: SELECT * FROM [OpenOrderHeader] WHERE [OrderNumber]='295327��'

    Anyway, going to have to find a better way to migrate SQL data to SQLite on Android.
     
  4. mc73

    mc73 Well-Known Member Licensed User

    First, you should remove the 'try' block and see the type of error you get, if any.
    I see.
    Since the initial order is a combination of address and then order, then you could avoid setting the presentation order. In fact you could leave presentation order null. Then, whenever the driver rearranges data, you could give a negative presentation order number 'increasing' just to data the driver chose to be first. That is, you set the first order to -1, then second to -2 and so on. When you query by
    Code:
    select * from OpenOrderHeader order by presentationNumber desc,ShipToAddress1, OrderNumber
    will get the selected items by the driver first, then the remaining non-chosen data.
     
  5. Lee Gillie CCP

    Lee Gillie CCP Active Member Licensed User

    I see your strategy. Yes, the driver could select a sequential list starting from first.

    My thought was to put up-arrow and down-arrow on the selection list items to rearrange. The sequence numbers in this scheme would need to be pre-populated so you could simply swap the presentation order values for the two records. In this way he can begin his rearrangement from the bottom of the list, or the top of the list, or even the middle. We impose no constraint in this way. But probably starting sequentially from the first would likely be fewer steps for the driver in most cases.

    A more interesting realization through this all was the way the data imported, tagging unprintable characters at the end of each field. There were no nulls either. I found an awesome little tool which completely resolved the issue and made all my exact match predicates operate as expected: Code Project SQL Converter

    I appreciate your thoughts and input!
     
Loading...