Android Tutorial [B4X] Create and use SQlite databases with "DB Browser"

I often use Sqlite for my apps and I came across "DB Browser". It's a free tool to browse and edit SQlite databases and tables. Download it from here: http://sqlitebrowser.org/ For B4J apps I recommend you to use the latest sqlite-jdbc-3.21.0.jar file. Get it from here: https://bitbucket.org/xerial/sqlite-jdbc/downloads/

B4X:
#Region Project Attributes
    #MainFormWidth: 1200
    #MainFormHeight: 600
    #AdditionalJar: sqlite-jdbc-3.21.0
#End Region

Start DB Browser, select File -> New Database and select a filename. Don't forget the suffix like "db" (e.g. MyDB.db). It automatically opens the "Create Table" menu:

1.JPG


Click on "Add field" to add a column. Repeat it for every column you need:

2.JPG


It's a good practice to add a "id" field for every table you create. It uniquely identifies the row. Later you will update this row by

B4X:
Update customers SET address='234 Great Portland Street' WHERE cid=23345

Never do this:

B4X:
Update customers SET address='234 Great Portland Street' WHERE cname='Peter Miller' and birthdate ='1970.05.01'

as you can have more than one Peter Miller born on May, 1st 1970 in your db!

Another good thing is that with every insert of a new row (customer) this id is increased automatically, so you don't have to care.

2nd step is to create an index. An index is used to access and sort data very fast (like a phone register). It depends on the needs of you app. Here we want to search for the customer by name and a second need is to search for a street (or both then):

3.JPG

I use the column name for the name of the index (you don't need the name later):

4.JPG

Click on the column "cname" and the arrow to add it to the index. Click on ok and repeat it for "caddress". If needed, add a third index in which you combine both columns. Then you can search for both at the same time like "Miller" and "Grafton St.".

5.JPG

What I don now is to copy the create statements to my B4x app. Just right click on the lines with create statements and select "Copy Create statement" to copy it to the clipboard:

6.JPG


Now copy it to you B4x app and add a "Sql1.ExecNonQuery(" to it:

B4X:
Sql1.ExecNonQuery("CREATE TABLE `customers` ( `cid` INTEGER PRIMARY KEY AUTOINCREMENT, `cname` TEXT NOT NULL, `cbirthdate` TEXT NOT NULL, `caddress` TEXT NOT NULL )")

Add a "IF NOT EXISTS" to all create statements (so it will be created only if it wasn't created before):

B4X:
Sql1.ExecNonQuery("CREATE TABLE IF NOT EXISTS `customers` ( `cid` INTEGER PRIMARY KEY AUTOINCREMENT, `cname` TEXT NOT NULL, `cbirthdate` TEXT NOT NULL, `caddress` TEXT NOT NULL )")

Repeat it for all the tables and after that for all the indexes.

If you made a mistake, just close your app, delete the *.db file, add the changes and go on.
 

Star-Dust

Expert
Licensed User
Longtime User
You should build a browser in B4J and share the sources. Someone who has time ..
 

Star-Dust

Expert
Licensed User
Longtime User
I suggested creating an application with B4J that would allow you to navigate to a sqlite database, such as ms access, possibly making the source code public
 
D

Deleted member 103

Guest
Thanks Klaus, very useful tutorial. :)
Especially the tip with the application "DB Browser".
 
Top