Android Example SQLiteLight four simple SQLite projects

Discussion in 'Tutorials & Examples' started by klaus, Dec 7, 2013.

  1. klaus

    klaus Expert Licensed User

    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

    Attached Files:

    Last edited: Nov 29, 2018
  2. NJDude

    NJDude Expert Licensed User

    I suggest you add this post to your signature.
 and Xardoz like this.
  3. klaus

    klaus Expert Licensed User

    Good idea, done :).
 and u2005k like this.
  4. Beja

    Beja Expert Licensed 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.
    ZJP,, tdocs2 and 5 others like this.
  5. LucaMs

    LucaMs Expert Licensed User

    When knowledge and experience is coupled with generosity and selflessness, then we have Klaus.

    (i like copy & paste :))
 likes this.
  6. Beja

    Beja Expert Licensed 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.
  7. klaus

    klaus Expert Licensed User

    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' :).
    This is the 'standard' way Android works.
  8. cougar

    cougar Member Licensed User


    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.
  9. Beja

    Beja Expert Licensed User

    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 :)
  10. mangojack

    mangojack Well-Known Member Licensed 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
  11. klaus

    klaus Expert Licensed User

    I agree with you but in my example I use IDList in two activities.
    Listview is an activity object so it's data is lost when you start a new Activity.
  12. mangojack

    mangojack Well-Known Member Licensed 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
  13. klaus

    klaus Expert Licensed User

    I agree with you, I wanted to bee pricise, indicating the limit :).
    Last edited: Dec 8, 2013
  14. westerwäller

    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
    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")
    Msgbox ("No Match!","")
    End Try

    End Sub
    Devan likes this.
  15. Erel

    Erel Administrator Staff Member Licensed User

    You should instead check the number of results:
    Curs = SQL1.ExecQuery(Query)
    If Curs.RowCount > 0 Then
     Curs.Position = 
    End If
  16. westerwäller

    westerwäller New Member

    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:

    Msgbox ("No Match!","")
    End Try
    bringing you back to the whole table.

    You're solution is much more elegant of course. Thanks again ...
  17. westerwäller

    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.
  18. klaus

    klaus Expert Licensed User

    This cannot happen in the original SQLiteLight2 project because the Spinners are filled only with existing data !
  19. Tom Christman

    Tom Christman Active Member Licensed User

    Thanks so much for this Klaus!
  20. klaus

    klaus Expert Licensed 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"
    Query = "SELECT ID, FirstName As [My text], LastName As [My text 2], City As [My 3 text] FROM persons"
    in SQLiteLightTwo.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice