Sqlite select long string cause database hanging

[billt tran]

Hi DS team,
I found more problem about sqlite in DS v1.5x that:
If i insert long string like html table to DB then it's stuck when i execute sql "SELECT HEX(long_html_string) FROM tab". Since that, every sql can't be executed any more.
This code run very well in DS v1.34 but always stuck in v1.5x.
I don't know why? Did you update newer sqlite version in DS v1.5x? How to copy or replace sqlite in DS v1.5x with older sqlite of DS v1.34? I have done with DB in DS very much so i relize that Sqlite DB library in DS v1.5x is very unstable!!!
Please help to fix. Thanks!

[Dave Smart]

The SQLite functionality has not changed since v1.34... are you trying to use multiple threads again?

[billt tran]

Thank Dave,
I have changed my app to excute sql in App only, altenate for executing in Webview. Nested multi sql excution seem to be ok. But, when i get html from a website (html length about 350000 chars), store it to DB, then get it by "SELECT HEX(html) FROM tab", then convert hex to string. But, it's stuck, every other sql execution after that stuck too.
I must using HEX in SELECT because some NUL char break execution.
I have tried to detect this problem but i can't find the cause? It's stranger that my code run very well in DS v1.34 (never stuck or fail) but stop working in DS v1.5x.
I don't know your changed between versions, i don't know where to read your libraries code to detect problem. I'm sure that the problem is caused by some changes in your built-in code.
Since i upgraded DS to v1.54-1.56, my app is very unstable, some problem occur sometimes, others stuck without message. Most of them relate to Sqlite Database. I spend very much of time to detect, fix but it's difficult to control them. It's like a ghost, especially in nested callbacks.
My app run very well in DS v1.34 for a long time but i still want to upgrade to v1.56 for some new functions. I need them.
Please show me where to read your built-in libraries code and how to replace it with older? So, i can detect the cause.
Thanks.

[Steve Garman]

billt, DroidScript is not open-source, so you will not find library source.

You have obviously made changes to your code to use the one thread.
Are you sure the amended code still works in v 1.34?

I get the feeling DroidScript 1.5x uses a bit more memory than 1.34.

What is the device on which you are running the code?

[billt tran]

Thank Steve,
My device is Xioami mi max octa-core 64GB ROM, RAM 3GB. I think it is enough strong.
My app have run well on v1.34 and below for 5 years (since 2012). My app is about Invest Management (stock, forex, ...). My app get data online from many source on internet and analyze, report, watching to support investors. I reinstall back to v1.34 for many times to test and for my work too. So, i'm sure it still run very well on DS v1.34 but bugs (unstable) on v1.5x.
I think bugs caused by changes in mechanic of asynchronous callbacks, queue or cluster, or memory distribution, or sqlite pre-set parameters(ex: quota, permisson, queue, time wait) which cause the conflict or stuck at somewhere in runtime. Because the bugs occur when i use nested some callbacks like using Web&App Execute, sql execute callbacks with abit bigger data (ex: select text 350000 chars). My app require proccessing data, so it need that. I usually use nested sql execution for many sqls to get data for analyzing.
Now, i can't detect the original cause of problem, i can only try to find the way to avoid them which make my code very complexible unsignificantly. But i still can't find out the solution for SELECT long string problem.
Can you give me how to fix active link download in webview in DS v1.34 (like in DS v1.56 fixed)? I need that, i want to continue using v1.34 until v1.56 stable.
Please help!
Thank you very much!

[billt tran]

I think the problem is caused by overflow memory for sqlite in DS v1.56. When i modify the sql like this 'SELECT HEX(substr(html,0,4000)) FROM tab', the sql executed ok. But if i select substr(html,0,10000 or greater) then stuck again. Nommally, i think the TEXT field's length about 10k-100k is not too big.
Can you check the pre-set parameters related to memory or cache for sqlite sql results in built-in code of DS v1.56 vs DS v1.34? I think something at somewhere in your code make overflow in v1.5x. It's bug beacause the text length is not too big and it's still well in v1.34.
I think this is serious bug for Database Application. It's only relized if working much with database.
Hope you fix this problem soon for perfect DS.
I think you should focus to make a DS version stable (fix most of bug) before add new functions too fast.
I love DS, thanks.

[JustAnotherDude]

100kb data in the database? and this in one single entry? what do you want to save in this 100kb data? any why you cant put your data in more than one row?

i recomend to save anything that you can in a own row because thats a dirty way to save data on a sql database (you can use txt files for this type of data)

[billt tran]

Hi JustAnotherDude,
No, sorry, 100k = 100000 chars. That is the length of html i store in the TEXT field.
That is raw of data of text format, it's not bigger than image data (BLOB). I work with DB very much, the text field with 100000 chars is normal (like memo). I don't want to save as text file because i have many text (at least 1/per day), and for query. 100000 chars is much less than text field limit in sqlite db. That is my max for example. In the fact that i have zipped my text before store to db, so the length is about 30000 chars but when select i must use Hex() function to avoid NUL chars, so the hex text length about 300000 chars.
The problem is in DS v1.56, i only select the text with length above 10000 chars (about 100000chars as Hex) causing stuck silently (sql stuck but app is not hanging). But it's run well in DS v1.34. Why?

[billt tran]

Hi DS team,
I have tried this:
var sql = "SELECT market, HEX(substr(quote,0,4000)) as quote, HEX(substr(quote,4001,4000)) as quote1, HEX(substr(quote,8001,4000)) as quote2, HEX(substr(quote,12001,4000)) as quote3, HEX(substr(quote,16001,4000)) as quote4 FROM htmquotetoday WHERE market LIKE ? AND qdate LIKE ?";
It's stuck.
But, this run ok:
var sql = "SELECT market, HEX(substr(quote,0,4000)) as quote, HEX(substr(quote,4001,4000)) as quote1 FROM htmquotetoday WHERE market LIKE ? AND qdate LIKE ?";
So now, i'm sure about pre-set memory limit of sqlite sql executiton (only select sql, insert still ok). That is limit of total memory per 1 sql (not per 1 field).
Something about sqlite or pre-set for sqlite in new DS v1.5x has changed vs DS v1.34?
Now, i only have 1 tempolary solution is split my select sql to many select sqls. That is dirty and make select query more slow unsignificantly.
I hope you fix this from built-in DS. Thank you!

[billt tran]

Hi DS team,
I have tried split to multi select sqls to select long string but it's still stuck when nested callbacks of sqls executions. That is so serious. I waste alot of time for this problem. So bored to come back to DS v1.34 now.
I came back to DS v1.34 then the nested callbacks of multi sqls execution run well. I think the DS v1.5x have the very serious bug in backgroud which make app unstable, especially for complex or big application with nested callbacks and data processing.
I think the most stable DS version is v1.34. It does just like what we think. But lost some functions, for example download link in webview not working.

[JustAnotherDude]

you know that 1 char = 1 byte so 1000 chars = 1kb? :3

how many Ram do you have ?

[billt tran]

Ok, 1 char=1byte, 1000chars=1kb. But do you know you can store image to DB as BLOB? 1 small image about hundreds kb is normal. With database proccessing, it's normal.
The fact that, in DS v1.34, i have run my app well with DB storing long text like that for 5 years. My DB size after 5 years just is about 100mb, it's so small for online data udating daily? If zipped it's only about 10-20mb size.
But when i upgrade DS to v1.5x, it's stuck. Additionally, when split select long text to multi select by using substr, it's still stuck when nesting sql results callbacks.
In DS v1.5x, nesting some select sqls results callbacks is unstable too, some case stuck, but still well in DS v1.34. I don't know why?
My Device Ram is 3GB.
I think this is bug in DS v1.5x. I wrote very much about this and i spent much of time to detect problem. But i can't because i am not DS dev.
I have worked about 15 years in IT with Sql server, Oracle DB. So i know How a big data is.