WM23 SQL Lite Data corruption on Android

Hello All,

I have a app that use sql lite on Android 6.0 and 7.0 for barcode scanning.

I have 6 tables.

3 of them I fill by getting data from a HFSQL server over WIFI connection inside our office with solid network connection.

The other 3 tables are used during the scanning process with a local connection on the device. From time to time I am getting corruption on the data file on the device. The data transfers from server to device with no problem. When I try to check the DB using the control center after a scan job is complete I get the file is malformed error. I have download a tool that repairs corruption and it is the tables that are used with the local connections that are the issue which leads me to believe that the corruption is happening during the scanning process.( I would presume something in my code)

The question in all of this has anyone had issues with corruption of the SQL Lite DB on Android with local connection?

DW

Hi Dennis

I'm not sure a lot of people are using SQL lite at all... I personally don't see any advantage over HF and you are loosing so much functionality. And no, up ill now, I've never have this problem with HF.

Best regards

Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Free Video Courses, free WXShowroom.com, open source WXReplication, open
source WXEDM.

More information on [www.fabriceharari.com]

Hi,

I agree with Fabrice, why SQLite?

In any case, whenever I see WM and data corruption mentioned together, "UNICODE" springs to mind - maybe that's worth some investigation?

Regards,
Darren.

Darren,

I use sqllite in one of my apps without any problem!

Why sqlite? Because HF was not supported a few years ago.
What: Since that time at least +500k records are saved without any issue....
Where: The database is stored in SysDirStorageCard()
How: HDescribeConnection(sConnectionName,"","",sDatabase,"",hAccessSQLiteAndroid,hOReadWrite)
followed by HChangeConnection and HOpenConnection. And this connection is open most of the time.

One thing that may be a difference: I create the sqllite database on a server every time the tablet is getting it's daily new data. So the sqlite file is rebuild from scratch almost every (few) day on every tablet. Then that file is zipped and downloaded and used on the tablet.

Arie

Hi Fabrice,

Quote
you are loosing so much functionality

Can you give some examples of that?

Arie

Thank you Fabrice,

I realized last night that I could use HF and I have switched over to it.

Now I have a couple of questions.

1) What happens to the connection to the DB as far as when it goes in the background and comes back to foreground? Is connection lost?

2) Once you connect to the local DB does that connection stay live until app is closed or will it time out?

DW



Edited 1 time(s). Last edit at 09/25/2018 02:06PM by cabinetman.

Sorry reply to cabinetman/DW/Dennis, not Darren

Arie

Arie,

No Problem,

I do store it also in SysDirStorageCard() but one thing I noticed is that I also have a XML file that holds user setting and when ever I access that folder from my computer to see what is going on with the data the next time I start the app that file is over written with a new one and all settings are lost. Leads me to think this has something to do with the database corruption.

DW

Hi,

@Dennis: I always have my DB in the program directory (NOT on the sdcard where the user can touch it, mess with it)

As for the local HF DB 'connection', we are talking about HF CLASSIC here, on android, so the "connection" is only a file that is open or not, and that doesn't change when the app goes into background; that I know of. Even if the file was closed, it will be open automatically at the next use (classic mode) so no problem.

@Arie: Automatic modification of structure when the analysis changes isn't enough for you? I don't remember the others from the top of my head, but it is not called LITE for nothing.

best regards

Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Free Video Courses, free WXShowroom.com, open source WXReplication, open
source WXEDM.

More information on [www.fabriceharari.com]

Hello Fabrice,

The only reason I am use the External Memory is so I can look at the data and audit it to make sure the code I am using is doing the correct thing. It is still on the internal disk it is just in a shared location.

DW

Hi Dennis

a location that users can read/write without any control...

To verify the content of the DB, I use a window/plane inside the app, with a table and a hbuildbrowsingtable...

best regards

Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Free Video Courses, free WXShowroom.com, open source WXReplication, open
source WXEDM.

More information on [www.fabriceharari.com]

Thanks Fabrice,

This will be the route I take. I did notice better performance out of the HF once I made the change.

DW

DW,

"better performance", interesting. Can you rate that like 10% or 25% or even more speed?

Arie

Fabrice,

automatic modification sure is a pro. DW mentioned performance too. I think I will give it a shot in my next project. Together with your WXreplication to move only the data which has been changed.

Arie

Hello Arie,

What I noticed with performance.

The data base has 6 tables

First 3 are data brought over from the server to the local Data file. Orders, LineItems & Barcodes.

I connect to a Hyperfile CS store all the records in arrays then connect back to local DB save all the data in arrays to local DB Process time 15 seconds in SQLLite. About 12 seconds in HF
1) 3 to 40 records
2) 30 to 700 records
3) 30 to 700 records

After changing to Hyperfile I ran the code in a secondary thread and it cut the time into a third. (4 or 5 seconds)

The other three tables are Scanned,ScannedCodes & ScanReport. As labels are scanned 1 scanned record is created for each LineItem and 1 ScannedCode for each Barcode and 1 ScanReport for each Order.

So I do the same in reverse, load all data from 3 Scan tables from local DB in arrays, connect to server and save array data to CS.

This seams to be the same. (Not as many fields in each table as the import)

The real difference is when it is time to loop thru all the records in all 6 tables and delete everything. In the SQLLite took 8 to 12 seconds in HF 3 to 4.

This would be going thru all 6 tables doing some checks to make sure the record should be deleted.

Also I load a Looper with about 6 fields and 700 lines HF is more responsive by 20%.

*Note did not set any time variables to accurately check each process i am just going off of fell.

DW

DW

Thanks, looks like it's worth to switch to HF

Arie