App Inventor Extensions


SQlite Extension

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

For questions about this extension, bug reports and feature requests, please start a new thread in the App Inventor Extensions forum. Thank you.

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

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.

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.

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.

Download the example app to test the functionality of the SQlite extension

Download SQlite test app (apk file)

Note: The Test app (apk file) uses a special version of the extension, which means, in one day in the future the extension will display an error message and will stop working. This was necessary to prevent dishonest people from extracting the extension from the apk file. Extracting a paid extension from an apk file violates the intellectual property rights of Pura Vida Apps. Don't do that! Thank you.

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.

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