Using SQLite component with ListPicker

29 Jan

Developing Android app with database functionality has been simplified in Ai2LiveComplete. SQLite component is an alternative to TinyDb with much powerful functionality.

This post shows you how to use SQLite component in Ai2LiveComplete and how to perform CRUD (Create, Read, Update, Delete) operations on database tables.

The SQLite is an alternative to TinyDB with full database functionality.

This example shows you how you can quickly create a  table, insert data, read (query) and then populate the result into a ListPicker.

  • On application start, the procedure initDb procedure is invoked, which creates a simple table with 2 columns.
  • Next, we insert 3 rows into the database table using SQL command

Create table and insert into table

Next, we use a ListPicker to display result data. In the ListPicker.BeforePicking, the table is queried. The query returns a list which can directly be populated into our ListPicker. Our table has a key and value columns. Let’s say that we use key for specifying a product (e.g. Lemonade) and value is the quantity of product on hand (e.g. 3). To keep data generic, both columns are defined as Varchar (String).

The query we want to perform is to determine how many of an item has been sold for each day.  Also, we want to multiply the quantity by 2 (assuming each item cost $2) and name the resulting column as ‘sold’. Also, we only want to select rows where quantity (value) is < 30.    This logic can be converted into sql statement like below:

select cast(value as decimal) * 2 sold, value from eav_table where value  between 0 and 30 

Above query means, select column value, convert (cast) to decimal and multiple by 2, name the result as ‘sold’.


When the ListPicker opens, it will display data as requested:


You can see the column headings and result data in the ListPicker.

Interested in Ai2LiveComplete? please see HERE for details and more information.


Posted by on January 29, 2014 in Uncategorized


6 responses to “Using SQLite component with ListPicker

  1. Dade Irfan Nak'forcik

    May 5, 2014 at 12:38 pm

    thenk for M. Hossein Amerkasih. ini yang saya cari.

  2. Don59

    April 22, 2014 at 3:01 pm

    Hi! I have a question about the component SQLite. In my project I have a database (myfile.db) already exists that I uploaded as “media,” which will be in asset folder. I simply want to connect to it and do visualizations using the query “SELECT”. In essence, what is the procedure to be done with the INITIALIZE screen ‘. I have tried in various ways without getting the connection..

  3. John

    February 17, 2014 at 9:57 pm

    Hossein, thank you for the nice example. I still have a lot of questions however about SQLite and how to use it in AppInventor/AiLiveComplete…Could you do some more examples/tutorials like these? For exampe how to go about when you want to design the database beforehand instead of programmatically? Is that possible? if not, how to prevent having to create the tables again every time? When created, it is not necessary anymore. But how to check that and arrange that with the blocks? What I would like to do is for example create a SQLite database beforehand with a tool like SQLite Studio. Then import it in AppInventor(AILC) and then be able to work with that database. I don’t think that’s possible now, or?

    Anyway, some more tutorials on this subject SQLite in combination with AiLveComplete would be really cool..!! (and much appreciated !!)

    Best regards,
    John (

    • M. Hossein Amerkashi

      February 17, 2014 at 10:13 pm

      Please note that once table is created, it will not be re-created again.
      You can select data from a table and if no data is returned, then you’ll now that table has not been created.
      You can also use a TinyDB and on first run, create table and store a value like ‘created’ in a TinyDB tag. Next on start-up, check the TinyDB tag of ‘created’ to see if data exists for it or not.

      Again, once table is created, it will not be recreated again until you drop it. You can test this by creating a table and then inserting data into it. After that, try to re-create it again and the result of that block will be ‘false’ instead of ‘Done’ — need to verify to make sure these are the values that are returned.

      Hope this helps

      • Paul

        January 19, 2016 at 12:18 pm

        Hi, i have one table of products in sqlite (id,product_name), and i populate listpicker with produtc_name fields. So now how can I get the equivalent ‘id’ from listpicker to perform and select query with selected product? Thanks for any help!!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: