SQLite database with App Inventor, quick, easy, effective app development

[Peter Thomson]

I think that App Inventor plus an SQLite extension, and a user interface programmed using a WebViewer with HTML and JavaScript is one of the easiest, and quickest ways of developing apps that need to handle large amounts of data.

I think that these methods are very effective for commercial app development.

Quick, easy and effective also means that development costs are much less and the development takes a shorter time.

Once you have got the SQLite extension uploaded to your project you can use all the standard SQL commands for SQLite.

See three video tutorials that cover the basics at 

https://youtu.be/j8NgyBbFSdY

https://youtu.be/3udnYBF7e54

https://youtu.be/LQqGvNDOssk

The main source of SQLite information is https://www.sqlite.org/index.html

These tutorials also cover the basics of a user interface using WebViewer, HTML and JavaScript.

Let me know what other aspects you would like to see covered.

Peter

[TimAI2]

Thank you Peter

Shame there is a charge for the aia  :(  (especially as you have this on the play store under "Education")

[Peter Thomson]

All the information is available in the video. 

But since youtube stopped allowing any advertising on videos like this there is no way of getting any funding to produce more videos. 

Paying a small fee for the .aia file encourages authors to produce more. The question is - what is a reasonable fee for a user to pay save them the time needed to type in the code?

Is the price of a cup of coffee too much? Half a cup?

Peter

[TimAI2]

Fair point...

[TimAI2]

There is however a general (if not explicitly written rule) about "advertising" paid apps on this forum. Given that one has to go find the link on your youtube videos, and there one finds useful information and a free sql lite extension, I guess the mods will let it stay....

[Peter Thomson]

It isn't my intention to advertise here 😊

For those wanting to learn how to code, you will understand far more by creating all the code yourself and checking for errors, then making corrections.

But you will need the two database files for the first two tutorials. These are attached here.

The diary tutorial creates its own database.

The downloads have been updated today to include the four .aia files used in the three SQLite tutorials.

And it is free for the next week from 4th Jan. (50p is the minimum charge allowed by Google. )

[SteveJG]

Thank you for posting your extension Peter.  Developing it was a lot of work creating the extension and documentation.  It is appreciated.

I am working my way around your tool using the chinook.db.  So far the tool seems to be working OK.

I noticed what is a bug using the DBName Block which should contain a string but when queried displays as Boolean

Is it supposed to work that way?  https://github.com/frdfsnlght/aix-SQLite  indicates

  

This property defines the name of the database file. It's default value is "db.sqlite". Changing the name after opening a database has no effect on the database. Use this property to change the name before the database is opened.  No problem as it is not a critical Block.

I tried the App Inventor tutorials: Using SQLite app in Google Play.  Are you aware the files you posted in the app look like this  

App Inventor will not load aia files that include some characters.  The (  ) require the user changing the names.  You might keep that in mind for future versions as it is really not a problem, ja user ust deletes the ( ) and the aia's load fine.

The example aia's are useful.   I figured out most of how the extension works.  Your example blocks answered a few questions I had.

I think the  org.bennedum.SQLite.aix will become popular with many developers, especially since Google will sunset its FusionTable in December 2019.  You provide an extremely 

useful alternative that seems to work very well.  I still am testing and will finish soon.  I am not building anything that requires a database like this.

You might point users to  http://www.sqlitetutorial.net/sqlite-sample-database/  .   I don't have a need for an sql database but others that do might enjoy working 

with the chinook.db. It shows how useful your tool is.    I enjoyed experimenting with that dataset and your aix. I used the database with Taifun's Dynamic HTML table.It makes a very nice display of the tables.

Thanks again.

Regards,

Steve

[Peter Thomson]

Note I am not the author of the extension, but I agree with you that it is a very useful extension, and mostly everything works as expected.

App Inventor doesn't make use of the sqlite1.dbname blocks. It doesn't retain the name when you import a database.

I have another tutorial in preparation that covers the use of multiple databases, by saving an updated database and importing a different database.

When you import a database save its real file name in  tinyDB. Then when you export the database get the name from tinyDB and use that to save the database.

Usually you will save the database to the SDcard and then reload from the SDcard.

Peter

[ABG]

(added to new SQLite section of FAQ)

https://groups.google.com/forum/#!msg/mitappinventortest/2F51UhB4z0A/IcuCSPpOAgAJ

ABG

[Ondrej Ruz]

Is there another method in SQlite to import than this? In Attachment.....

Ondrej

[TimAI2]

Seems the most logical approach to me: "import database from file" ....

Taifun's sqlite extension uses a different block:

https://puravidaapps.com/sqlite.php

for the same function

[Ondrej Ruz]

Don't have an aix file to try? So I don't buy a cat in a bag ...

Ondrej

[TimAI2]

Well I believe Taifun opens the bag well enough on that page so that you can check that there is a pig in there or not  (value of pig > cat !!)

[Taifun]

to test my sqlite extension, please see chapter "Test the functionality of the SQlite extension" here https://puravidaapps.com/sqlite.php

thank you

Taifun

---

Trying to push the limits of App Inventor! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.