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,238
  • SQLiteLight2_1.jpg
    SQLiteLight2_1.jpg
    41.1 KB · Views: 6,033
  • SQLiteLight2_2.jpg
    SQLiteLight2_2.jpg
    20.1 KB · Views: 5,365
  • SQLiteLight2_3.jpg
    SQLiteLight2_3.jpg
    16.4 KB · Views: 5,204
  • SQLiteLight2_4.jpg
    SQLiteLight2_4.jpg
    32.3 KB · Views: 5,594
  • SQLiteLight1.zip
    130.9 KB · Views: 451
  • SQLiteLight2.zip
    204.2 KB · Views: 399
  • SQLiteLight3.zip
    230.8 KB · Views: 380
  • SQLiteLight4.zip
    229 KB · Views: 416
Last edited:

IslamQabel

Active Member
Licensed User
Longtime User
Dear Klaus...
I used your program and i changed the header successfully, but i want to change later in the following line as follows
Query = "SELECT ID, Name As [Name], ID Number As [ID Number], City As [City] FROM persons"....so how to eliminate First name, last name and city ???
second issue, when i open the application for the first time a list is shown with database about US presidents.......I want an empty table without any data......How to do it?

Is the displayed US presidents table created from the first program you post?
I am really so confused and i want to understand
Thanks a lot
 
Last edited:

klaus

Expert
Licensed User
Longtime User
Sorry, but without seeing your code it's impossible to give you a concrete answer !
I tried to change Persons.db to another file name but an error occurs displayed also why?
What error do you get ?
You need to be more precice in your questions.
 

IslamQabel

Active Member
Licensed User
Longtime User
Ok kalus, i will summarize my questions in the following points...
First of all, i am new in using SQL and try to answer my questions one by one.....with my apology for asking a lot of questions

Part 1 of Questions for the 1st attached program:

  1. The ID is not updated automatically, I just click "Add" then add all data and then click on "Update" and then to add another name i click on "Add".....message appears "Entry already exits"...can not add any new entry...Why?
  2. How to build a data base file have another name not persons.db as well as other parameters not like "first name,last name,....."?
Part 2 of Questions for the 2nd attached program:

  1. When i run it, i see a table that has entered data for US presidents...why?
  2. If i want to show an empty table, how to do that?
  3. How to build a data base file have another name not persons.db as well as other parameters not like "first name,last name,.....?

Thanks
 

klaus

Expert
Licensed User
Longtime User
Have you already tried to modify these projects ?
If yes, what and how did you modify it ?

1.1 To add a new entry, modify the data in the EditTexts and click on Add, the new data will be added to the database.
If you want to update an entry, select it, modify it and click on Update.
In my programs I minimize the number of clicks to execute functions.
Why should I clicking on Add, modify the data in the EditTexts and then need to click Update.
1.2 You need to modify the program to fit your needs!
- replace all persons.db by your name
- modify the CreateDataBase routine to create your database
- modify everywhere the column names by yours.

2.1 As explained in the first post, this program has a default database to let the users 'play' with the filter functions.
2.2 In Activity_Create remove File.Copy(File.DirAssets, "persons.db", File.DirInternal, "persons.db")
2.3 You can define a database in another SQLite program and copy it to the project like persons.db.
Or create a CreateDatabase routine where you define your database like in SQLiteLight1.
You need to modify all database names, table names and column names everywhere in the program.

These two simple example programs are intended to show beginners what can be done and how.
The names of database, table and columns were intentionally 'hardcoded', instead of using variables, to keep the code easier to read.
The SQLiteDB example seemed to be too complicated for beginners therefore these two 'simple' programs.
 
Last edited:

netchicken

Active Member
Licensed User
Longtime User
Hi Klaus, thats so much for your code, its great to update skills :)

I have a database calls class and am using functions to return the cursor. It works OK, but I saw in your example how you closed off the cursor after using it.

I can't do this with a function, as of course the return ends the code run, is this an issue with performance not closing off?

B4X:
Sub LoadAllEntries As Cursor
Dim cursor1 As Cursor
cursor1 = SQL1.ExecQuery("SELECT * FROM bikefit")
'cursor1.Close   
Return cursor1
End Sub
 

klaus

Expert
Licensed User
Longtime User
In the example programs I close the Cursor because I don't need it anymore.
What are you doing with the cursor which is returned by LoadAllEntries ?
I prefer using the Cusor directly in a routine to fill displays or other treatments, and therefore don't need it anymore and close it.
If I need other data from the database I launch a new query.
I don't know if there are any concerns not closing a Cursor.
 

IslamQabel

Active Member
Licensed User
Longtime User
Dear Klaus
Thanks for your reply

I succeeded to to change and create my own database ( 5 columns ) and use the the program as you told me.....
Now, i am asking how to create database file in this program to be created n file folder in my project?
After creating it, i will replace your database file persons.db by my file Qabel.db....for the first project SQLite1
 

klaus

Expert
Licensed User
Longtime User
Not sure I understand what exactly you are asking for.
- If you have created a database with any other program on the PC you can add this one in the Files folder of your project (DirAssets folder)
and in the program copy it to another folder on the device. Like persons.db in SQLiteLight2.
- Or can create the database once in the program and use it like in SQLiteLight1.
 

IslamQabel

Active Member
Licensed User
Longtime User
I know that i created a database by SQLight1....Now i want to create database file (.db) in file folder of my project , Later i will use this file in another project Got it?Just create it in SQLight1
Thanks
 

klaus

Expert
Licensed User
Longtime User
Where and with what name did you initialized the database file in SQLight1 ?
If you save it in DirRootExternal or another accessible folder you can find it on the device and either recuperate it or access it from the other program.
If, in SQLight1, you used SQL1.Initialize(File.DirInternal, "persons.db", True), I'm afraid you won't find it because DirInternal is a 'private' folder for the program and not accessible from other programs.
You might use SQL1.Initialize(File.DirRootExternal, "mydatabase.db", True)
 

klaus

Expert
Licensed User
Longtime User
A third project has been added.

- 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
 

Mahares

Expert
Licensed User
Longtime User
There is nothing light about what Klaus contributes to the forum. He is a powerhouse.
A very minuscule observation: Since you assign variables to all the database objects, this line in Activity_resume:
B4X:
SQL1.Initialize(File.DirInternal, SQLDateBaseName, True)
should be:
B4X:
SQL1.Initialize(SQLDataBasePath, SQLDateBaseName, True)
 

Watchkido1

Active Member
Licensed User
Longtime User
@ westerwäller: So geht's auch:
@ Klaus: Danke für diese einfache Version.
B4X:
Sub UpdateSelectedItem

    Dim Query As String
    Dim Curs As Cursor
  
    If IDList.Size = 0 Then
        ToastMessageShow("No Data",True)
    Else
  
        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
    End If
  
End Sub
 

ginsh

Member
Licensed User
Longtime User
Hi,

Any one please help me.
In the below query, how can I get the only the clicked cell value to lblSelected Item.text.


B4X:
Sub UpdateSelectedItem
 
    Dim Query As String
    Dim Curs As Cursor
 
    If IDList.Size = 0 Then
        ToastMessageShow("No Data",True)
    Else
 
        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
    End If
 
End
 

klaus

Expert
Licensed User
Longtime User
Like this:
B4X:
'Routine from the DBUtils demo program
Sub wbvTable_OverrideUrl (Url As String) As Boolean
    'parse the row and column numbers from the URL
    Dim values() As String
    values = Regex.Split("[.]", Url.SubString(7))
    Dim col, row As Int
    col = values(0)
    row = values(1)
    CurrentIndex = row
'    UpdateSelectedItem
    UpdateSelectedItem1(col)
    Return True 'Don't try to navigate to this URL
End Sub

Sub UpdateSelectedItem1(col As Int)
    Dim Query As String
    Dim Curs As Cursor
  
    Query = "SELECT * FROM persons WHERE ID = " & IDList.Get(CurrentIndex)
    Curs = SQL1.ExecQuery(Query)
    Curs.Position = 0
    lblSelectedItem.Text = Curs.GetString2(col)
    Curs.Close
End Sub
 

ginsh

Member
Licensed User
Longtime User
Hi Klaus,

Thanks for your reply. One more problem i am facing.

I have added a search option to SQLite3 example. Below is the code. The below code searches the entire table for a value and returns the matching rows. But when i click on one the cells of the searched rows, it returns the value of the cell of the original row on the same position on lblSelectedItem.Text not the searched row value. What could be wrong and how can i get the searched row value.

B4X:
Sub Search

Dim SeaVal As String
SeaVal = EditText1.Text.Trim

Dim Query As String
  
    Query = "SELECT "
    For i = 0 To ColNumber - 1
        If i < ColNumber - 1 Then
            Query = Query & ColNames(i) & " As [" & ColAliasNames(i) & "], "
        Else
            Query = Query & ColNames(i) & " As [" & ColAliasNames(i) & "] "
        End If
    Next
    Query = Query & " FROM " & SQLTabelName & " WHERE " & ColNames(1) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(2) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(3) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(4) & " LIKE " & "'%"  & SeaVal & "%'"  '"
  
  
  
    wbvTable.LoadHtml(ExecuteHtml(SQL1, Query, Null, 0, True))
    ReadDataBaseIDs

End Sub
 

klaus

Expert
Licensed User
Longtime User
The problem is ReadDataBaseIDs.
This routine loads the IDs of the rows according to the filter included in the program.
You change the filter so calling ReadDataBaseIDs gives wrong results.
You need to either modify ReadDataBaseIDs to your needs or read the IDs specifically in your routine.

The principle in the original application is to filter only on existing entries.
The Spinners in the Filter activity are filled only with existing entries.
 
Last edited:

ginsh

Member
Licensed User
Longtime User
Hi Klaus,

Can you help me please. i tried a lot and couldn't find a solution. Thanks in advance.
 
Top