Android Question [ANSWERED] SQLite records not updating

[Lee Gillie CCP]

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

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

 

[Lee Gillie CCP]

If I get it right, what you're trying to do is probably this:
delivery.execNonQuery("update OpenOrderHeader set PresentationOrder=OrderNumber")

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

 

[Lee Gillie CCP]

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.

 

[mc73]

First, you should remove the 'try' block and see the type of error you get, if any.

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

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

select * from OpenOrderHeader order by presentationNumber desc,ShipToAddress1, OrderNumber

will get the selected items by the driver first, then the remaining non-chosen data.

 

[Lee Gillie CCP]

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!