Android Example SQLiteLight four simple SQLite projects

Some users asked here for a simple SQLite project as an example to make their first steps in SQLite.
The already existing examples seem beeing somewhat complicated for beginners.

These examples are explained in the B4XSQLiteDatabase Booklet.

Attached three small projects:
- SQLiteLight1
A very simple project with a very simple user interface with following functions:
At the first run the database is empty and the user must add entries.
- Add an entry
- Edit an entry
- Update an entry
- Delete an entry
- Display next and previous entry

- SQLiteLight2
A simple project with some more functions:
A default database is supplied to allow the use of the filter without the need to add many entries.
- Display the database in a table with following functions:
-- Add display the Edit activity to add a new entry
-- Edit display the Edit activity to edit an entry
-- Filter filter the database
-- Set Filter display the Filter activity to set the filter
- Edit
-- Add / Update / Delete
-- First / Previous / Next / Last
- Filter

The database is very simple with four columns :
- ID
- FirstName
- LastName
- City

- SQLiteLight3
Same as SQLiteLight2, all functions are the same.
The differences are the database path, database name, table name, columb number, column names, column alias names and column data types are variables instead beeing hard coded.
It allows also to generate a new database by:
- changing in Globals the values of the variables listed above
- in Activity_Create
-- comment this line : 'File.Copy(File.DirAssets, SQLDateBaseName, SQLDataBasePath, SQLDateBaseName)
-- uncomment this line : CreateDataBase

- SQLiteLight4
Multi table example:
The database has three tables and shows the interaction between these tables.
The tables are intentionnaly kept small with only a few columns to keep a clear overview and show how to handle more than one table.
The interface is kept minimalistic and not all possible mistakes are chaecked to keep the code 'short'.

The code has comments and is, I hope, self explanatory.

EDIT: 2014.04.11
Amended error repoted HERE.

EDIT: 2014.05.04
Added SQLiteLight3
Updated SQLiteLight2

EDIT 2015.10.21
SQLiteLight2 and SQLiteLight3 added the Starter Service.

EDIT 2015.11.26
Added SQLiteLight4
 

Attachments

  • SQLiteLight1.jpg
    SQLiteLight1.jpg
    18.6 KB · Views: 6,048
  • SQLiteLight2_1.jpg
    SQLiteLight2_1.jpg
    41.1 KB · Views: 5,859
  • SQLiteLight2_2.jpg
    SQLiteLight2_2.jpg
    20.1 KB · Views: 5,119
  • SQLiteLight2_3.jpg
    SQLiteLight2_3.jpg
    16.4 KB · Views: 5,025
  • SQLiteLight2_4.jpg
    SQLiteLight2_4.jpg
    32.3 KB · Views: 5,397
  • SQLiteLight1.zip
    130.9 KB · Views: 262
  • SQLiteLight2.zip
    204.2 KB · Views: 228
  • SQLiteLight3.zip
    230.8 KB · Views: 211
  • SQLiteLight4.zip
    229 KB · Views: 250
Last edited:

Beja

Expert
Licensed User
Longtime User
When knowledge and experience is coupled with generosity and selflessness, then we have Klaus.
Thanks man. You gave us the ladder to learn b4a implementation of SQL.
Have a wonderful weekend.
 

Beja

Expert
Licensed User
Longtime User
I don't want to mess with Klaus example, but in my computer I added a menu to navigate through the activities
or jump to any of them.. in the example this is done using the devices Return button.. I mean the return to prev Activity.
 

klaus

Expert
Licensed User
Longtime User
I added a menu to navigate through the activities or jump to any of them..in the example this is done using the devices Return button.. I mean the return to prev Activity.
As I already posted in the other thread, the perception of a user interface is personally.
SQLiteLight2 is my vision of the user interface for such a project, I prefer having a 'main' activity and going forth and back to others instead of jumping around because after a while the user and perhaps even the system doesn't know where they are. But again it's just my vision.
The examples provided are assumed to be minimal projects to show the fundamentals of SQLite and not 'final' projects.
Evereybody can use them and adapt them to it's personal need or 'feeling' :).
.. in the example this is done using the devices Return button..
This is the 'standard' way Android works.
 

cougar

Member
Licensed User
Longtime User
Klaus,

Sorry my "simple" question led to a mountain of work for you but my hats off to you for your efforts. As is obvious from the responses to your code examples; the additional examples were definitely wanted and appear to have been well received. I do realize there are some posts in regards to styles, changes, etc. but I hope you will know that the "basics" you provided were more than enough. As you stated, individuals styles will vary but you have provided the framework. I thank you for that.
 

Beja

Expert
Licensed User
Longtime User
Klaus,
By no means I was directing those comments to your person.. after giving us these complete samples (all thumps up), then we
b4a graders can discuss (among ourselves). But it is good that now I know back key is Android's way of doing business.
please take a rest after having a crazy weekend for us :)
 

mangojack

Well-Known Member
Licensed User
Longtime User
Klaus .. Many thanks for your time and excellent samples.

If I may , this might be of interest to the readers of this thread.

If your are populating a simple ListView with data from your DB , by using the methods ListView.AddSingleLine2 , .AddTwoLines2 or .AddTwoLinesAndBitmap2 allows you to set the records ID number as the last parameter / argument , which will be the return value for any subsequent ListView click events.
In most cases this alleviates the necessity to have an IDList ... as even after records are deleted from the DB and Listview the Click event will always return the current records ID allowing you to do further queries on that recordset.

Cheers mj
 

mangojack

Well-Known Member
Licensed User
Longtime User
Klaus .. my comment was just food for thought for members who have discovered your samples.
In a totally different scenario / interface , where the developer might have need to display some basic db data in a listview.

Again well done and Thanks

Cheers mj
 

westerwäller

New Member
In SQLLiteLight2 I always got an error when filtering and no matching item was found.

I solved it by enclosing it in an Try ... Catch ... End Try - Routine like this.

Maybe sombody else is interested in this ...

Sub UpdateSelectedItem
Dim Query As String
Dim Curs As Cursor

'Fehler abfangen
Try
Query = "SELECT FirstName, LastName FROM persons WHERE ID = " & IDList.Get(CurrentIndex)
Curs = SQL1.ExecQuery(Query)
Curs.Position = 0
lblSelectedItem.Text = Curs.GetString("FirstName") & " " & Curs.GetString("LastName")
Curs.Close
Catch
'Log(LastException)
Msgbox ("No Match!","")
End Try

End Sub
 

westerwäller

New Member
You should instead check the number of results:
B4X:
Curs = SQL1.ExecQuery(Query)
If Curs.RowCount > 0 Then
Curs.Position = 0
...
End If

Thank you very much for your helpful reply.

I have found that the a. m. solution from me has another flaw:

It leaves the program in a not well defined state, which Icircumvented by adding:

...
Catch
'Log(LastException)
Msgbox ("No Match!","")
btnFilter_Click
End Try
bringing you back to the whole table.

You're solution is much more elegant of course. Thanks again ...
 

westerwäller

New Member
Is it a bug or...?

Applying the skeleton-code from SQLiteLight2 to a project of mine I was able to complete this project in a few days ...

Very impressive ...

I stumbled about one thing, I hadn't been aware of. I had used special characters (well, obviously not sooo... special characters) in the columnheaders, namely "-" and "/" like in "M/F" or in "XY-UVW13".

These had been accepted in my favorite desktop-program (SQLiteStudio) but the program crashed reproducibly when using them in SQL-Statements. My first thought was searching for an error in my code, without any help.

Since all other culumnheaders worked fine, I changed these characters to an underline-sign "_" and whoops everything went well.

I had been aware of the fact that there are reserved words for SQL-columnheaders but not of this fact ...

Maybe it helps other users who are facing the same problem.
 

klaus

Expert
Licensed User
Longtime User
In SQLLiteLight2 I always got an error when filtering and no matching item was found.
This cannot happen in the original SQLiteLight2 project because the Spinners are filled only with existing data !
 

klaus

Expert
Licensed User
Longtime User
What do you mean with the table header ?
The column names in the table or the headers in the display.
If you want to change the headers in the display you can change this line:
Query = "SELECT ID, FirstName As [First name], LastName As [Last name], City FROM persons"
to
Query = "SELECT ID, FirstName As [My text], LastName As [My text 2], City As [My 3 text] FROM persons"
in SQLiteLightTwo.
 
Top