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
It's awesome! Thanks so much!
I installed it this morning but I didn't get the time to test it, and when I came back this afternoon, I forgot I had installed it and also that I had changed the columns names in my db without updating them in the sql block. Of course, it failed but instead of being in the dark, I got a helpful message! That was simply fantastic! Many thanks! You even did it on a Sunday! You are amazing!
I tested it on an insert and a select query and it's just perfect!
Thanks also for implementing the async sql. I wonder how it works. In the extension from Tab Bennedum, there was an "after sql" block, that looked like a procedure block (with a tag to differentiate the sql) so that we can perform some actions after the sql is done, but I don't see this block in your extension. Let's say, I am running a large sql and then feed the result to the webview. If my blocks are like below, is there a risk that the webviewer won't get the returned sql, if it is really large and it takes some time to run in async?
I have just re-read the comments in the original code. The Run block is not running asynchronously. However, all db calls are serialised onto one thread, and there is a wait until done function.
1 Like
Thanks for looking into it!
Testing SimpleSqlite v3
pSqlite_Simple.aia (50.7 KB)
- Screen1
- Sqlite in ASD.
- Screen2
- Using the KIO4_Base64 extension we Store and Get the sqlite in CloudDB.
1 Like
INSERT IF name NOT EXISTS
INSERT INTO contacts(name, location)
SELECT 'Bob', 'London'
WHERE NOT EXISTS (
SELECT 1 FROM contacts WHERE name = 'Bob'
);
1 Like
