App Inventor Extensions

SQlite Extension

See the App Inventor Extensions document about how to use an App Inventor Extension.

For questions about this extension or bug reports please start a new thread in the App Inventor community. Thank you.

For feature requests please contact me by email. To be a sponsor of a new method already is possible starting from only 10 USD! With your contribution you will help the complete App Inventor community. Thank you.

Aug 11th, 2016: Version 1d: avoid DX execution failed error: build each extension separately

Jul 6th, 2017: Version 1e: bugfix: remove last Carriage Return for SELECT statement results
Note: using the extension version 1e together with another Pura Vida Apps extension built between July 1st and July 20th might result in error message Unable to find TaifunTools component while opening a project. Use extension version 1f instead.

Jul 21th, 2017: Version 1f: new build using extension-specific directory in aix file, "More Information" link updated in extension description

Sep 3rd, 2017: Version 1g: minor bugfix to get correct length of the result after doing a SELECT query

Jan 26th, 2018: Version 1h: bugfix: double values in SELECT statements will not be truncated anymore

Aug 21th, 2018: Version 1i: Android Pie fix

Oct 30th, 2020: Version 1j: API29 update because of assets path for companion app

Oct 9th, 2021: Version 2: ExecuteAsync method and corresponding Executed event added; SDK30 update to store database for the companion app in the ASD, subdirectory databases; change Kodular companion app path to /Kodular/assets for devices < Android 10

Nov 15th, 2021: Version 2a: Kodular companion app still uses /storage/emulated/0/Kodular/assets to store the assets for devices < Android 10

Description

This extension provides access to the local SQlite database.
Minimum API level is 16 (Android 4.1, Jelly Bean).
Required permission: android.permission.READ_EXTERNAL_STORAGE, android.permission.WRITE_EXTERNAL_STORAGE

Probably you want to read this first: What is SQLite and what is a relational database?

There also is a nice Windows tool called SQLite Studio to create, edit and browse SQLite databases. Thank you Frederic for this tip, thank you Pawel for providing this tool.

There are several Firefox Add-Ons available to manage a SQlite database, for example the SQLite Manager, SQLite Reader or SQLite Manager. See also sqliteexpert.com for a tool to design your database.

Please see here for the SQL syntax.
Please see here for some SQLite tutorials.

Each app has its own SQlite database. There is only one SQlite database per app. Even if you have multiple SQlite components, they will use the same database.

When you are developing apps using the AI Companion, all the apps using that Companion will share the same SQlite database. That sharing will disappear once the apps are packaged and installed on the phone. During development you should be careful to clear the Companion app's data each time you start working on a new app.

SQlite Addons Extension

Andres Daniel offers an extension as add on to the sqlite extension, which helps to simplify creating SQL statements like CREATE, INSERT, SELECT, UPDATE and DELETE statements. You can download his extension here.Thank you Andres!

Properties

Returns whether the header row should be ignored in the result of a Select statement.

Specifies whether the header row should be ignored in the result of a Select statement.

Methods

Execute query in SQlite database.
Use this method if you expect to get back a result immediately. For longer running SQL statements use the ExecuteAsync method together with the corresponding Executed event.

For SELECT statements, the result will be returned and you can assign it to a label or listpicker or ... In case of an error, an error message will be returned. For other statements (for example CREATE TABLE, INSERT, UPDATE, DELETE, ...) in case the query was successful, "done" will be returned, else an error message.

It is posssible to have commas inside table values. In this case, for SELECT queries which return more than one column, the result will be quotified by double quotes.

Execute query in SQlite database.
Use this method for longer running SQL statements to not block the main thread. The result will be provided in the Executed event. If you expect to get back a result immediately use the Execute method.

Export database.
Note: Starting with SDK30 Android restricts accessing the file system. Therefore starting from Version 2 of the extension the database will be stored in the ASD - application specific directory /storage/emulated/0/Android/data/<packageName>/files. As fileName just use the filename without slashes.

Import database.
Note: Starting with SDK30 Android restricts accessing the file system. Therefore starting from Version 2 of the extension you can import the database only from the assets or from ASD - application specific directory. As fileName just use the filename without slashes.

Prefix the filename with / (i.e. relative path) to look for the file on the SD card in the ASD - application specific directory. For instance /myDatabase.sqlite will look for the file /storage/emulated/0/Android/data/<packageName>/files/myDatabase.sqlite. To import a database from the assets packaged with an application (also works for the Companion) start the fileName with // (two slashes).

Events

Return error message (only for Import and Export methods).

Event indicating that asynchronous execution of SQL statement has finished.

Example Use

Example App: SQlite Demo using the Chinook example database

The example app imports the Chinook example database on first run of the app. Thank you Chinook database team!
Alternatively you also can import your own SQlite database, export a database and execute some SQL queries to test the functionality of the SQlite extension.




The Chinook database model looks like this:


What about solving some queries (questions taken from here (pdf)?

• Which artists did not make any albums at all? (See my solution above in the blocks screenshot)
• Which artists did not record any tracks of the Latin genre?
• Which video track has the longest length?
• Find the names of customers who live in the same city as the top employee (The one managed by anyone).
• More tasks see here (pdf)

Screenshots:

Questions and Answers

Q1: Is there a limit of stored data in SQLite?
A: For limits, see here http://www.sqlite.org/limits.html.

Q2: I am very happy with the extension. It worked great with the wildcards % and _. It did not work with [charlist] like: SELECT * FROM Artist WHERE Name LIKE '[!a-c]%'; I can rewrite the expression with the command LIKE and NOT LIKE but I did wonder if the extension did accept [charlist] or if I was doing something wrong?
A: See this Stackoverflow answer and use GLOB instead of LIKE like this


Q3: I have a general question about a SQlite query. Where can I ask that?
A: For general questions about SQLite please ask at stackoverflow.com. Also please prepare some example records and an example SQL statement and the result you would expect. Also before asking there, please first take the tour and read how do I ask a good question.

Q4: After executing a SELECT statement, I get the following error: unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.
A: Remove leading spaces from your SELECT statement.

Q5: I can't execute command like .dbinfo or .backup etc. I receive a "syntax error". What could be the matter?
A: Commands like this are not available, you can send only SELECT, INSERT, UPDATE and DELETE statements. Also there is the Import and Export functionality.

Q6: I'm not sure how you create the database in the 1st place. I know (think) it's a built in facility but the import seem to imply that you have a database built in the 1st place to your requirement?
A: There is no method to create a database, the database always is there (same like TinyDB). You have 2 possibilities:
1) on first run of your app just create your tables (CREATE TABLE statements) and use some INSERT TABLE statements to add some data
2) alternatively prepare your database (including data), upload it into the assets of your app and import the database on first run of the app, see also the example project.
Let me suggest to follow the second option.
There is a nice SQLite Manager extension available for the Firefox Browser, which makes it easy to manage the database. Thank you Sonny for the info. See also sqliteexpert.com for a tool to design your database.

Q7: If I create an .apk for others to use, would that include the database or does every user have to build their own?
A: After installing, both TinyDB and the SQlite database are empty. See Q6 and follow one of the 2 options.

Q8: How can I get a list of all tables in the database?
A: Use the following query: SELECT name FROM sqlite_master WHERE type='table', see also this stackoverflow answer.

Q9: I have application with many thousands of rows (now ower 100 000) in TinyDB and TinyDB is really slow. Need to upgrade. Will be your SQLite fastest to TinyDB?
A: I don't know, but my guess is yes. You might want to look at the SQlite database speed comparison, also you might want to do a test yourself... You could use my example project and import any SQLite database of your choice and do some tests... To test the example project, see chapter "Test the functionality of the SQLite extension" below. To create the SQLite database, you might want to use the SQLite Manager extension available for the Firefox Browser, which makes it easy to manage the database. Let me know, what you find out... You also might want to read about SQlite database optimization... To set the correct primary keys, foreign keys and also probably indexes is the key to success. Also define your query correctly, for an example see here.
I now received the following feedback: SQLite is many many times faster. For example fillup 100,000 lines: TinyDB over one hour, SQLite 6 minutes. And fully fledged work with SQL commands - priceless. Thanks for your Extension.
Thank you very much Mirek for your feedback!

Q10: I am trying to get this SQL command to work in the Execute SQL block. INSERT userlist FROM /storage/emulated/0/etfiles/upload.txt. What I am trying to do is upload a file into the userlist table of the sqlite database.The upload.txt file would look something like this:
100
200
300
400
Etc.
A: This will not work like this. Fot correct INSERT statements see here. The following would be a valid INSERT statement (assuming, there is a table userlist which has a column userid): INSERT INTO userlist (userid) VALUES (100), (200), (300), (400). For questions about sqlite you also always can check Stackoverflow, for example this question and answers.

Q11: How can I import a database in Kodular while using the companion app?
A: Starting with version 2 of the sqlite extension you can import the database from the assets as in App Inventor like this //yourDatabase.sqlite.

Q12: I'm using a LineageOS device and get the following error message while using the extension:
A: It seems to be, the extension is not compatibe with LineageOS devices. Please use an Android device.

Q13: What version of sqlite is your extension using?
A: The SQlite version depends on the Android device, see this stackoverflow answer.

Q14: How do I handle string escaping? What I mean is this: A user can input something into a textbox which I want to insert into the database. But if I were to do this:

then people could destroy the database because the textbox text is not being escaped. For example if they write: '); DROP table mytable; Then the whole table get's dropped.
A: I checked the extension and as you can see from the screenshots below, it is not possible to add several sql commands separated by semicolon, only the first one will be executed in this case.
query1: list all tables in the test database
query2: list relevant records in table Playlist
query3: try to concatenate 2 sql statements, i.e. try to drop table Playlist
query4: list all tables in the test database and as you can see, table Playlist is still there
query5: list relevant records in table Playlist and as you can see, the Insert statement has been executed successfully
query6: now drop the table Playlist
query7: list all tables in the test database and as you can see, now the table got successfully dropped


Q15: I need to get the table columns from a sqlite table, even if there is no data in the table yet. The standard way to do this is to run PRAGMA table_info(table_name); But it seems pragma is not supported in the sqlite extension. Could you add support for this or let me know of another way to get the columns please?
A: Just set the property IgnoreHeader to false and send a SELECT query to the table to get the data including header, i.e. column names. And if you like to get only the header without data, then just add a WHERE clause, which will not provide any data...

Q16: I use the sqlite extention to call select id,sqrt(id) from myTable. The id is in the table. The sqrt function is not available, but in the SQLite documentation the the function is listed. What can I do?
A: Answer by TimAI2: From the link you posted, it just explains that sqlite must be compiled with maths functions enabled for maths functions to be available in the sqlite application. It is quite possible that the compile on your device does not have these functions. Thank you TimAI2!

Test the functionality of the SQlite extension

The test app is available in Google Play. You can test the example following these steps

1. Follow the opt-in URL to get access to the app and to be able to download it to your device. It might take a few hours for Google to enable you as beta tester!
2. Start testing.

Buy the SQlite extension for App Inventor

You can buy this extension (aix file).
With your payment you accept the terms and conditions below.

Payment options

After having received your payment I will be happy to send the download link to you. Please let me know your Google account! I usually will send the download link not later than 24 hours after having received your payment.
Thank you! Taifun

Terms and Conditions

• You are buying a digital product and agree to the No Return, No Refund Policy.
• You have tested the example app successfully on your device before buying the extension.
• You can use the extension in as many projects as you like and pass the generated apk files to as much end users as you like, also of course you can publish your apk files to Google Play and other services
• This is a single user license for you as App Inventor developer, which means, the extension (aix file) is only for you to use. You are not allowed to pass a paid extension (aix file) or a project (aia file) which contains a paid extension to someone else, to post it in one of the communities or to distribute it to others. In case you are interested in a multiple user license, please contact me by email.
• There are a lot of different Android devices available in the market. I'm not able to test the example on all these devices and therefore also can not guarantee, that the example is running on your device.
• THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Back to top of page ...