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 Extensions forum. 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

Description

This extension provides access to the local SQlite database.
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 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.

Please see here for the SQL syntax.

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.

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.


Export database.
Prefix the filename with / (i.e. relative path) to store the database on the SD card. For instance /myDatabase.sqlite will store the database at /mnt/sdcard/myDatabase.sqlite. If fileName starts with file:/// you can specify a complete path to the database to export.


Import database.
Prefix the filename with / (i.e. relative path) to look for the file on the SD card. For instance /myDatabase.sqlite will look for the file /mnt/sdcard/myDatabase.sqlite. To import a database from the assets packaged with an application (also works for the Companion) start the fileName with // (two slashes). If fileName starts with file:/// you can specify a complete path to the database to import.

Events


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

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)?

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!

Do you need help with your App Inventor project?

I can help you: for one hour I charge 25 USD.
Please contact me at info [at] puravidaapps [dot] com for details!
For questions about App Inventor, please use the App Inventor forum. Thank you.

Test the functionality of the SQlite extension

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

  1. Join the Taifun Test Google+ Group to be able to test the example app
  2. Then 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!
  3. 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

1. Paypal

Please transfer 10 USD via Paypal
to Pura Vida Apps

2. Bitcoin

After having received your payment I will be happy to send the extension (aix file) to you.
Thank you! Taifun


Please check your spam folder in case you did not receive the extension!
I usually will send the extension not later than 24 hours after having received your payment.

 

Terms and Conditions

Back to top of page ...


Home | Snippets | Tutorials | Extensions | News | Forums | Links | Search | Contact