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]

Try this code instead of ReadDataBaseIDs , not tested:

Dim Cursor1 As Cursor
Query ="SELECT " & ColNames(0) & " FROM " & SQLTabelName & " WHERE " & ColNames(1) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(2) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(3) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(4) & " LIKE " & "'%"  & SeaVal & "%'"  '"

Cursor1 = SQL1.ExecQuery(Query)
If Cursor1.RowCount > 0 Then  
    RowNumber = Cursor1.RowCount  
    IDList.Initialize  
    For Row = 0 To  RowNumber - 1
        Cursor1.Position = Row   
        IDList.Add(Cursor1.GetInt(ColNames(0)))  
    Next
    CurrentIndex = 0  
Else
    CurrentIndex = -1     
    ToastMessageShow("No items found", False)
End If
Cursor1.Close

 

[3394509365]

Where can I find this file physically on the phone? (File.DirInternal, "persons.db")

 

[klaus]

You won't find it.
If you want to acces it from another application you should replace File.DirInternal by File.DirRootExternal.

 

[3394509365]

ok

but if I want to use the external sd memory?

I would change the name of the db instead of persons.db?

 

[klaus]

You can write on an external sdcard, but you need to know the path for it.
Unfortunately Android doesn't provide a standard path naming for external sdcard.
This means that the path name is different on different devices.
You might have a look HERE.

Since Android 4.4 it is no more possible to write anywhere onto an external sdcard, only into an application special folder, look HERE.

 

[3394509365]

hello everyone I scaricatoo the project (SQLiteLight3 project) and I'm studying, I would like to add a button but I can not figure out where they are created.

 

[klaus]

On what activity do you want to add a button ?
All buttons are defined in the Designer.
There are three layout files, one for each activity.

 

[3394509365]

Le activity sono vuote.

 

[klaus]

Le activity sono vuote.

Which one is empty ?
When you run the Designer you must select the layout file you want to edit.

 

[3394509365]

Si lo so ma volevo aggiungere via codice come già fa il programma. Ok lo stesso

 

[klaus]

Sorry, I don't understand what exactly what you are speaking of.
In the program the Buttons are all added in the Designer in layout files, no button is added in the code !?
So you can either:
- add buttons in the Designer to any of the layout files.
- add buttons in the code.

Or are you speaking of the Label, EditText and Spinner views in the Edit or Filter activity ?
These are added automatically in the code according to the number columns in the InitEditPanel and InitSpinners routines.

 

[tdocs2]

Thank you, Klaus for this and all of your other contributions to the forum. If I am allowed t quote Beja, "When knowledge and experience is coupled with generosity and selflessness, then we have Klaus."

Two minor issues related to column names with a space:

Column Name: [Last Name]
In SQLite3, set

ColNames(ColNumber) = "[Last Name]"

However,

in sub: UpdateSelectedItem

Curs.GetString expects "Last Name" not "[Last Name]"

I developed a work around, but I wanted to get your view on this.

Best regards.

Sandy

 

[klaus]

In SQLiteLight3 you have two arrays one ColNames() with the column names and one ColAliasNames() with names for the columns shown in the table header.
For Last Name, ColNames(2) = "LastName" and ColAliasNames(2) = "Last name".
LastName is used in the database and in the SQL query and 'Last name' is shown in the table header.
The advantage doing this is that you could change the names in the table header without changing the names in the database (different language for example).

 

[tdocs2]

Thank you, Klaus.

I failed to ask the question correctly. I have an existing table where column names contain spaces. For example,

Existing Column Names
[Date Last Seen on Forum]
[Ask Klaus]
[Person Responsible]

Two minor issues related to column names with a space:
Column Name:[Ask Klaus]

In SQLite3, set
ColNames(ColNumber) = "[Ask Klaus]"

However,
In SQLite3 sub: UpdateSelectedItem
Curs.GetString expects "Ask Klaus" not "[Ask Klaus]"

I developed a work around, but I wanted to get your view on this.

Best regards.
Sandy

 

[klaus]

I have never used column names with spaces so no need for the brackets.

 

[tdocs2]

Thank you, Klaus.

I agree with you - it is better for ColNames not to have spaces. However, the table I am working with contains them.

I found a workaround by creating a second set of column names to accommodate the Curs.GetString expects "Ask Klaus" not "[Ask Klaus]"

Final point on SQLite3 - this is a great tool, Klaus, and like many others, I am grateful you developed it.

Would you consider adding your Flexible Table Class to display the table?

Best regards.

Sandy

 

[klaus]

I have never written a program with SQL and the Flexible Table Class.
But you can find a project using a table based on a ScrollView, SQLiteDB Example, it might be a bit old.

 

[tdocs2]

Thank you, Klaus.

 

[LucaMs]

I have an existing table where column names contain spaces.

Many DBMS allow the use of column names (and also table names) containing spaces.

For the construction of the query sql, common practice is to use a function that adds the square brackets if they do not already exist.

Dim Query As String = "SELECT " & AddSqBr(FieldName) ..."

Sub AddSqBr(Text As String) As String
    If Not(Text.StartsWith("[")) Then
        Text = "[" & Text & "]"
    End If
    Return Text
End Sub

 

[tdocs2]

Thank you, Luca.