SQLite Bulk Data Load

In this post, I’ll show you how your Android app could include ability to perform bulk data load into device SQLite database.

Platform: Ai2LiveComplete

Image below shows how the app will look like.

The components in the Design Editor are shown in the below diagram:

 

The blocks are shown in the diagram below.

• Screen.Initialize — used to check if first time installation. This is done by checking if any database table exists. If not, it will then invoke initDatabase procedure
• btnDropTable.Click – used to drop mytable. It also checks to see if it exist or not. If so, it drops table (NOTE: All data will be deleted)
• btnInitDb – once clicked, it will invoke initDatabase to re-create table and re-populate data
• initDtabase procedure – First it creates mytable Table and then it will bulk load data in one-shot. Please note that in one call, 4 rows are inserted into database table
• lpShowData.BeforePicking – Once this ListPicker is clicked, a query is executed to retrieve all data. Once retrieved, it will show data in a separate screen
• lpShowTables – Once this ListPicker is clicked, it will retrieve result from SQLite.DisplayTables blocks and will then show result in the ListPicker

Image below shows data once lpShowData is clicked. The first row is the column names and subsequent rows are the data in csv format

NOTES:

---

•  Bulk insert into SQLite database has a limit of 500 rows. If you intend to insert over 500 rows, you’ll have to break your insert statements.
• You can easy convert a csv file into SQL CRUD (Create, Read, Update, Delete) statements using THIS online conversion tool

Variations:

If your SQL data is large, you can create variance of this app that pull data from your server.

Download apk

• Click HERE to download .apk

 

 

Using SQLite component with ListPicker

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

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.

 

Ai2LiveComplete

On January 10th, 2014, the basic version of Ai2LiveComplete was updated to be aligned with the latest version of MIT AI2. It is completely browser based and still includes SQLite and custom fonts for buttons and labels.

You can download the this basic offline version from sourceforge site HERE.  It also includes its own companion app for connecting your device via WiFi. After unzipping the download file, the companion app can be found in the root folder.

The online version of Ai2LiveCompete includes additional new components and enhancements. Please see below for these features. Interested? please see HERE for more information.

New components:

• * AdMob – under 30 second integration with Google AdMob
• * AdAmazon – under 30 second integration with Amazon Ads
• SQL Lite
• Near Field Communication (NFC)
• File Manager
• * Gallery View
• * Spinner Picker (drop-down)
• Toggle Button
• Light Sensor
• Pressure Sensor
• Temperature Sensor
• * Proximity Sensor
• Pedometer
• Date Picker
• Time Picker
• Chronometer
• *SoundSensor
• PhoneStatus
• * Kitchen Sink – adhoc utility blocks
  • KeepScreenOn,
  • Hide/Show notification bar,
  • Can check to see if device has network signal or is a GPS enabled device
  • Can pop-up the GPS setting window
• Pedometer
• * @ XY Chart – Live XY chart. You can bind such data as accelerometer data to the chart
• * Dynamic Custom Menus

* = See it in action here: http://YouTube.com/kkashi01
@ = Work-in-progress – feature will be added in future

Enhancements:

• ListPicker – Includes ability to sort in ascending or descending
• Custom Fonts – You can now import custom .ttf font with a button or TextBox. Free fonts can be used from HERE
• Progress Bar – Ability to show or dismiss a progress bar — useful for long running processes; e.g. Show progress bar when downloading large files and dismiss (close) when download completed
• TextToSpeech — Ability to change speech pitch and rate (works very nicely with slider)

@ = Work-in-progress – feature will be added in future

 Interested? please see HERE for details and more information.