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

 

[klaus]

Look in the Starter module.
Line 48
' File.Delete(File.DirInternal, "persons.db") ' only for testing, removes the database
must be commented, if it is not, comment it.

 

[Pencil3]

Thanks for your help Klaus. Have another question with your SQLiteLight2 project. If I wanted to have the table column "Last name" display in the webview based on a PreferenceManager would that be done with CASE expressions? This is all new for me. Thanks again.

 

[klaus]

The column names are given in the query in the showTable routine, in this Lines:
Query = "SELECT ID, FirstName As [First name], LastName As [Last name], City FROM persons"
The displayed text is the one between the square braquets.
If you want to use a variable, you could use this:
Private AnyName = "Any namw" As String
Query = "SELECT ID, FirstName As [First name], LastName As [" & AnyName & "], City FROM persons"
The same is valid for the other columns.
You may have a look at SQLiteLight3, in this project the colum names and column pseudo names are with variables and not hard coded.

 

[Pencil3]

I think I follow. So if I don't want the table column "Last name" to display in the webview based on a PreferenceManager I could use:

Private AnyName = "AnyName" As String

If Manager.GetBoolean ("AnyNameDisplay") = False Then AnyName StillNeedToFigureOutHowtoRemoveFromBelowQuery

End If

Query = "SELECT ID, FirstName As [First name], LastName As [" & AnyName & "], City FROM persons"

Am I on the right track?

The column names are given in the query in the showTable routine, in this Lines:
Query = "SELECT ID, FirstName As [First name], LastName As [Last name], City FROM persons"
The displayed text is the one between the square braquets.
If you want to use a variable, you could use this:

The same is valid for the other columns.
You may have a look at SQLiteLight3, in this project the colum names and column pseudo names are with variables and not hard coded.

 

[klaus]

Am I on the right track?

Yes.
But again, you might have a look at SQLiteLight3.

 

[Pencil3]

Thank you again Klaus, I was able to figure it out by looking more closely at the SQLiteLight3 example.

Now I have another question. In the ShowTable Sub I use a ORDER BY DateTime(Date) DESC. I seem to be running into an issue with the UpdateSelectedItem Sub where I can select an entry but the information displayed in the lblSelectedItem label is not correct for the selection.

It appears to me that the Cursor is selecting based on a ascending list? I've been focusing on the Curs.Position = 0 in the UpdateSelectedItem Sub, but have not gotten very far. Any ideas?

Yes.
But again, you might have a look at SQLiteLight3.

 

[klaus]

You need to post your project as a zip file.
Without seeing what exactly you have done and how it's impossible to give a concrete advice.
In the examples I read the row IDs in a List in the ReadDataBaseIDs routine !
This ensures that you know the row ID for each row according to the query.
Do you use it ?

 

[Pencil3]

Thank you again Klaus, figured it out. I had to change my ExecQuery around in the ReadDataBaseID sub. Thanks again for pointing me in the right direction.

You need to post your project as a zip file.
Without seeing what exactly you have done and how it's impossible to give a concrete advice.
In the examples I read the row IDs in a List in the ReadDataBaseIDs routine !
This ensures that you know the row ID for each ro according to the query.
Do you use it ?

 

[Junctbr]

Hello sir Klaus. I just wanna ask how to make another column? In your example SQLiteLigh1 I added an edit text "Age". But it say's column 'Age' does not exist.
Thanks
@klaus

 

[klaus]

You need to modify the database:
All references to the database must be changed.
Just a few examples:

Private Sub CreateDataBase
    Private Query As String
   
    'create the database with 4 columns
    Query = "CREATE TABLE persons (FirstName TEXT, LastName TEXT, City TEXT, Age INTEGER)"
    SQL1.ExecNonQuery(Query)

    'Fill a few entries
    Query = "INSERT INTO persons VALUES (?, ?, ?, ?)"
    SQL1.ExecNonQuery2(Query, Array As Object("John", "KENNEDY", "New York", 45))
    SQL1.ExecNonQuery2(Query, Array As Object("Peter", "FALK", "Los Angeles", 60))
    SQL1.ExecNonQuery2(Query, Array As Object("Jack", "LONDON", "Seattle", 34))
    SQL1.ExecNonQuery2(Query, Array As Object("Ronald", "REGAN", "Los Angeles", 83))
End Sub

Private Sub ShowEntry(EntryIndex As Int)
    Private ResultSet1 As ResultSet
    Private RowID As Int
   
    If RowIDList.Size = 0 Then         'check if the database is empty
        Return                                            'if yes leave the routine
    End If
   
    RowID = RowIDList.Get(EntryIndex)        'get the RowID for the given entry index
    'read the entry with the given RowID
    ResultSet1 = SQL1.ExecQuery("SELECT * FROM persons WHERE rowid = " & RowID)
    lblRowID.Text = RowID                                                                    'display the RowID
    ResultSet1.NextRow                                                                        'set the next row
    edtFirstName.Text = ResultSet1.GetString("FirstName")    'read the FirstName column
    edtLastName.Text = ResultSet1.GetString("LastName")        'read the LasstName column
    edtCity.Text = ResultSet1.GetString("City")                        'read  the City column
    edtAge.Text = ResultSet1.GetString("Age")                        'read  the Age column
    ResultSet1.Close                                        'close the ResultSet, we don't it anymore
End Sub

Private Sub UpdateEntry
    Private Query As String
   
    Query = "UPDATE persons Set FirstName = ?, LastName = ?, City = ?, Age = ? WHERE rowid = " & RowIDList.Get(CurrentIndex)
    SQL1.ExecNonQuery2(Query, Array As String(edtFirstName.Text, edtLastName.Text, edtCity.Text, edtAge.Text))
    ToastMessageShow("Entry updated", False)        'display a confirmation message
End Sub

I leave it up to you to check all the other lines.

You may have a look at SQLiteLight3, there the columns are in variables and not hardcoded, it might be easier to change.
I suggest you to look at the B4X SQLite Database booklet. The SQLiteLight projects are explained.