OK, this will be set in the first post shortly.
Is this extension open source? I don't find it on github.
Also does it work for Android11 and above?
Yes, just not got around to publishing the code
Yes, should work with all android versions
1 Like
Thanks! Great!
By curiosity, what did you have to change to make it work with recent Android11 versions (the extension "aix-SQLite" written by Tom Bennedum doesn't work after Android10)?
Please let us know when the source will be published on github.
If I remember correctly, I had to rework the file path mechanisms. Everything else was OK.
1 Like
Thanks for this information! Since you have already done the modification, it would be great if you could make a pull request/fork on "aix-SQLite" to update it, so people could choose between these two extensions according to their needs.
In any case, thanks a lot for sharing your work!
I have made quite a different beast, so didn't think such a fork would apply. Also, do not use github in anger 
I understand, thank you anyway!
Thank you for adding the source!
I tried your extension but I am sorry to say, I went back to the one from Tab Bennedum for several reasons.
● I couldn't find where the database was exported (and thus where to copy my pre-made db to import it). I am on Android 10. I tried several path: just a filename, a folder + a filename (I tried with / and // and without), an absolute path (I tried "/storage/emulated/0/", "mnt/sdcard/" which I use for the webviewer, and "data/user/" which is the start of the path returned by the databasePath block). I also looked into the companion folder (Android/data/edu.mit.appinventor.aicompanion3/...) but I could never find the exported db. (some of these paths returned false on export so it's normal there was not db, I am just adding them here to show what I tried). For the path with folder, I tried staring it with / or // and without.
I used the block databasePath to find out the path but it returns a path that doesn't exist: data/user/0/edu.mit.appinventor.aicompanion3/database/SimpleSqlite.db
● Unlike the extension from Tab Bennedum, there is not Async sql (as explained by Tab Bennedum, not doing the sql async could freeze the screen - my db will be quite large, so this is important for me)
● I was happy to see the ListFixer block, as I struggle with formatting my sql result as a list of lists (as the returned value are comma separated and I use comma in my db), but I realized that it isn't used for running the sql, but on the result, so the same problem with commas would probably happen. (I didn't try it as I gave up because of the problem with the path).
It really wanted to use your extension as it is compatible with Android 10+ phone and I like the barebone extension but sadly I can't because of these problems.
The default EXPORT/IMPORT path is your ASD (Application Specific Directory) e.g.
/storage/emulated/0/Android/data/<packageName>/files
You use a single / before the filename.
This is covered in the documentation.
I have not had an issue with queries on large databases, so did not believe it to be necessary. I could consider adding it though.
Sorry, I forgot to add in my previous comment that:
- I have tested my app with your extension only in AI inventor companion. So, I don't have a packageName folder like "/storage/emulated/0/Android/data//files".
- I read the documentation and followed the link for absolute path, and it is quite confusing because I don't have the path indicated by Anke like
/data/user/0/or/storage/emulated/0/Android/data/edu.mit.appinventor.aicompanion3/files/assets/.
If you have never had problems with large queries on large databases, then I will try again by installing the app.
/storage/emulated/0/Android/data/edu.mit.appinventor.aicompanion3/files
You should be able to find this with a File Manager on Android 10 (use Total Commander)
This path is built-in to the extension
To EXPORT to ASD you simply add your filename and a slash: /myfile.db
To IMPORT from assets: //myfile.db
To IMPORT from ASD: /myfile.db
Thanks so much for your help! I downloaded the File Manager you recommended and now I can see the database! That's really weird but well... it's working now!
When using your extension, I have noticed something that could be improved. If a Select sql returns an error (ex because of a typo in a table/column name), the returned value is ["completed"] which is misleading. I have created a block to catch the sql error in a notifier but the notifier isn't called by this kind of errors. It would be nice to be able to see the error message to simplify the debugging. (I am using DB browser for sqlite to test my sql request but it would be nice to be able to do everything in appinventor).
That would also be nice to have a clear message when a sql is run and the db is closed. For the moment the return value is [] which is not very informative.
Anyway, thanks so much for this extension. It adds a very useful feature to appinventor. I also appreciate that you take the time to help the new users like me. Thanks a lot!
True, whilst not inaccurate (the function has completed...), it does not indicate an error. You should get something back from sqlite though, which could be used as a conditional test?
Yes you are right, technically, the function as completed and I can also use this value to filter the result as I never expect to simply get "completed" from my sql. It's just that it's confusing at first.
What do you mean by You should get something back from sqlite though. Do you mean an error message or just the value ["completed"] (which is the value returned by the sql block which I can use a condition to test if the sql "succeeded")
I came across another situation where ['completed'] is misleading: I just tested an INSERT and I get ['completed'] whether there was an error or not, so I have no way of knowing if the query was successfully completed or if it completed with an error (ex: I didn't add single quotes before and after the text). In this case, it would be really nice to retrieve a more informative value.
Call back what you inserted with another query. Is it as expected ?
Yes, that is a possible solution but it's complicated in comparaison with receiving a returned value from the sql that is more informative. If it's too complicated/impossible to retrieve description of the error, just "completed with error" or "completed successfully" would be nice.
Try this:
uk.co.metricrat.simplesqliteV3j.aix (now available from first post as latest release)
You will need to use the SQLError event to return the error/code
The Run block now returns: ['SQL command completed'] instead of just completed if not returning a list of items.
Also, I believe my fork is correctly using the async method for all calls on the Run block.
1 Like