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

 

[schemer]

Thank you klaus for the examples. After much reading and trying I finally have made some progress with my crash course in SQLite. I have a few questions. First, in my example using SQLite Example2, my table only accepts 25 entries and I have over 100 so I would like advice on what is the best control (or view) to best fill my needs of being able to click on an item in a list, to further process the number in column 2? A ListView? Or something else? Any examples?
Thank you again, These examples (after some serious trying) really helped me a lot!
schemer

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

p.s.s. I just realized the table is scrollable! But still I need to know if this is the best option.

 

[klaus]

But still I need to know if this is the best option.

It is one option.
Another option could be the Flexible Table class.

 

[schemer]

Thanks Klaus, I will check it out.

 

[Reyes5]

Klaus, thank you for this very good example ! Learned a lot from it !
Does anybody have some commented example how to combine several relational tables in one view ?
Example : 1 table contains person ID and person name, 1 table contains car ID and car name, 3th table contains per record 2 ID's, one of a person and one of the car the person is driving + some other fields with date from and to (for car rental).
How to display content of 3th table, but showing person name, car name and dates from/to for rental period ?
Thanks to all members who have an idea to do this in the way Klaus did his examples !

 

[Devv]

Very good , this is exactly what i'm searching for, thanks bro

 

[klaus]

I have added a forth example SQLiteLight4 with three tables in the first post.

 

[B4A_!ADT]

I am using Klaus's SQLLiteLight ver 1.2 as a template and i was able to run his code without problems. Then I changed the "FirstName" VAR to be an autocompletebox ( no other changes ). I was able to do the autocompletebox(ACB) setup code in Activity_Complete() and a DB that's already stored on the cellphone device is put in the ACB correctly. But when I try to add new entries ( On "enter" event it jumps to the add_sub() ), an error ( error 8 -- can't write to a readonly DB ) is thrown. I've traced it to the add_sub() on the SQLNONEXEC2() line.

I have B4a ver 3.20

Any help is appreciated ( frustrating... ! ).

Sub  Global

' other vars declared

Public  edtMEDName  as AutocompleteEditText

end  sub

Sub  Activity_Create

Public   temp_name_list   as List

'...other code snippets here....

  ReadDataBase    'read the database

   If IDList.Size > 0 Then    'check if the database has entries

     temp_name_list.Initialize
     temp_Cursor1 = SQL1.ExecQuery("SELECT First_name FROM persons" ) ' WHERE ID = ") ' & ID)

     For Row = 0 To RowNumber - 1
         temp_Cursor1.Position = Row  'Needed !
         temp_name_list.Add( temp_Cursor1.GetString("First_name") )
     Next

   End If

   Activity.LoadLayout("Main")
   edtMEDName .initialize("edtMEDName ")
   Activity.AddView( edtMEDName  , 140dip, 80dip, 180dip, 60dip)
   edtMEDName  .SetItems( temp_name_list)

end Sub

 

[klaus]

Unfortunately you don't give enough information.
It would be much easier for us to help you if you posted a small project showing the problem.
Otherwise we must try to guess what you could have done wrong.

 

[B4A_!ADT]

I've attached the code and the *.bal ( they were on another PC which doesn't have internet access. ). I had changed some VAR names on my previous post since it made it easier to show your original code VAR names for a point of reference ( I haven't changed much in your code ). At this point, I'm only inputting COLUMN_1 values and I leave the others blank during entries. Eventually, the code will only have one column.

I used your EXACT code to build my DB ( just arbitrary words ) and confirm that I had added your code correctly before I started changing things.

 

[klaus]

I had a look at the files you posted.
Unfortunately it doesn't work. The bal file is from the MyFirstProgram example in the Beginner's Guide and has nothing to do with SQLLitLight program.
Instead of posting two files you should post a working project using Export As Zip in the IDE File menu !
I saw that you are using a very old version of B4A, version 3.20 !?
Why do you add the AutoCompleteEditText view in the code and not in the Designer.

 

[B4A_!ADT]

I've attached the exported files, I forgot about that option. I've been away for a couple of yrs and I had been unemployed for awhile, hence the older version ( but I'll upgrade soon. ).

I'm definitely using the SQLLitLight code, since your "main" file starts out with many comment lines and references that name and the version and in the Designer, opening "main" ( the only Designer file I can access w/my version ), 5 buttons are arranged in 2 rows. I changed your edtFirstName member to my own as an ACB and did click on generate member. I saw that I wouldn't need to initialize that object if added by Designer, but I got an error, and so did it by code.

 

[klaus]

Now the layout file is OK.
I moved the initialization of the two lists IDList and temp_med_list at the beginning of Activity_Create just after If FirstTime.
The AutoCompleteEditText view addded in the Designer works.
Attached a modified version.

 

[B4A_!ADT]

Klaus,
To summarize some of what I did B 4 I began posting about this issue: I ran your example code and that's how I got my DB. Then I modified your SQL code to have the ACEB and that's when issues popped up.

When I ran the modified version from reply #72, I got the same error msg: "Error 8, attempting to write to a readonly DB" ( something like that ). I guessed that I might get lucky if I deleted the DB and re-entered values. YES, that got rid of the error 8 !

Also, while my ACEB function worked if I added init code manually, I've learned that the reason it didn't work using the Designer is because I was using the SETITEMS() member BEFORE the LOADLAYOUT() line. Which thru an err that ACEB wasn't initialized ( even though it should've been from Designer ). It must come AFTER that line (as I said... it's been awhile). Everything works like it should.

Thank you for your time/effort.

 

[Fausto Loss]

Klaus, first of all, thanks for the examples.
I am using SQLiteLight2 and I can not add or update data.

Follows file with error found.

 

[klaus]

Where did you get it from?
From the link in my signature or from the User's Guide SourceCode folder.
Can you please post the project as a zip file.

 

[Fausto Loss]

Where did you get it from?
From the link in my signature or from the User's Guide SourceCode folder.
Can you please post the project as a zip file.

Hi,
Im use the link in this post.

 

[klaus]

Remove line 30 in the Starter module.
File.Copy(File.DirAssets, "persons.db", File.DirInternal, "persons.db")
The zip file HERE has been updated.

 

[Fausto Loss]

Remove line 30 in the Starter module.
File.Copy(File.DirAssets, "persons.db", File.DirInternal, "persons.db")
The zip file HERE has been updated.

Works !!! Thanks !!!

What was the problem ?

 

[klaus]

The line File.Copy(File.DirAssets, "persons.db", File.DirInternal, "persons.db") was two times in the routine.

 

[Pencil3]

Thank you for the examples. I have compiled your SQLiteLight2 example and have a question. I can add to the persons.db but as soon as I restart the app everything that I have added to the db is no longer there, only the data that was originally included in the example is present. Is the data still in the db and it is not being shown in the table?