SQLite extension from frdfsnight


#1

Did anybody use the SQLite extension from frdfsnight.
I’m ok with executes, selects, inserts, updates and deletes.
It’s the only SQLIte extension to support transactions and that is problematic for me.
Whenever I start a transaction with begintransaction my app freezes.
I tried to begin a transaction do an insert and commit the transaction, but the app never gets to the insert.
On what way does one use this transaction mechanism?


#2

I would do two things.

  1. Build the smallest app possible to try this feature.
  2. Use logcat to figure out where it is crashing.

#3

Somewhere at the end I get
03-28 10:49:40.252 15448 16578 W System.err: Caused by: android.system.ErrnoException: open failed: EACCES (Permission denied)

The full logcat is in attachment.
What might be the cause of this?
I’ve got permissions for the camera and for the storage (external SD card).
Is there an permission for something else I need?


#4

Now I made a very small app.
Just openeing transaction and inserting into table.
I start logging when the app is open en the text to insert is typed in the textbox.
First thing I do is clicking the add button.
The add button does a begintransaction and than the insert.sqltransaction2_log.txt (383.7 KB)
The logfile is a lot smaller now, I do not find the error message from the previous log again.
For the rest I do not know what to look for in the logfile.


#5

I tried the small app on android 4.4, 7.0 and 8.0.
On all versions I have the same problem, the app hangs when I execute a begin transaction.


#6

@deboopi Consider adding an issue into developer’s github repo:


#7

Hi deboopi.
What are you trying to do in the transaction? any chance can show the block/steps you’re using?
Lildinti


#8

Hi,
first time I tried Begin transaction, insert and commit in one block.
I’ve split it all up (see the blocks in the picture) and now I see the begintransaction is ok.
It hangs when you try insert or insert async .


#9

I contributed on github but I saw only one other contribution and that one without an answer.


#10

Hi deboopi.
According to SQL definition and behaviour, single statements like insert, update and delete, are run within their own transaction (implicit). So if you are going to process one record at the time, there’s no point to use transaction unless you are operating in simultaneous tables and using joints, etc. Plain, simple statements have no benefit whatsoever in using transaction. If your update, insert or delete generates an error, its own (implicit) transaction will rollback and fire the onerror event when you can see what the error was (handle it).
Despite that, I had a look at the code of the extension and am not too sure is implemented correctly (in terms of transactions and handling). I’ll have a better look at it during some spare time in the next days and keep you posted. My suggestion in the meantime, is instead of using the Insert/update or delete block, just create the SQL statement string and use the executeSQL instead after stating the transaction like: BeginTransaction, executeSQL, commitTransaction in that order and see if it works.
Cheers
Lildinti


#11

I know you need more than insert and/or update to make use of transactions, I just made the example as simple as possible. I will give it a try with exevutesql.


#12

I tried now executesql but it does not make any difference.


#13

Hi deboopi
I had a look at the code and I don’t know for what strange reason, frdfsnight has implemented the transaction handling in the way he did. I would have implemented it in a different and simpler way, but is not my code. I can branch it from GitHub and give it a go but will take sometime for me to do the patching/new code in it.
As I know that usually doesn’t help when you are developing (like in your case) so I would suggest the following.
Build a sql string where the first line is the BeginTransaction command, followed by your insert/update/delete statement followed by the commitTransaction, followed by the EndTransaction to end the string. Something like this:
Var sqlString = BeginTransaction(); Insert into tableName value1, value2; setTransactionSuccessful(); EndTransaction()
If the insert fails, the commit statement shouldn’t execute and the extension should fire the onSqlError event where you can check what’s happening.
Hope this helps somehow till I (or frdfsnight) manage to get the extension working again.
Cheers
Lildinti
PS: please use a test database first just in case something goes wrong ok? Not being patronising, just safe!


#14

Hi Lildinti,
great if you want to look at the code.
Great idea to put all the sql in one statement and execute it as one statement, so I can keep parent and children synchronized.
Cheers


#15

Hi Deboopi.
I had a look at the code and unfortunately seems ok. Interestingly, it runs perfectly (without hanging) on Kodular but not on AppyBuilder. Maybe something withe AB companion? Hopefully Hossein may have a look at it?
You can try on modular (a simple sample will do it) and see that works. Haven’t tried thunkable due to time constrains but if you do, please let me know. Also I have contacted frsfsnight on his personal email (is a friend of mine) and asked about implementation of the library.
Hope this helps.
Cheers
Lildinti