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

Discussion in 'Tutorials & Examples' started by KMatle, May 1, 2018.

  1. KMatle

    KMatle Expert Licensed User

    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/

    Code:
    #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

    Code:
    Update customers SET address='234 Great Portland Street' WHERE cid=23345
    Never do this:

    Code:
    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:

    Code:
    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):

    Code:
    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.
     
  2. Star-Dust

    Star-Dust Expert Licensed User

    You should build a browser in B4J and share the sources. Someone who has time ..
     
  3. KMatle

    KMatle Expert Licensed User

    Ehm. Which sources? It's about using the tool. Did I miss something?
     
  4. Star-Dust

    Star-Dust Expert Licensed 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
     
    Filippo likes this.
  5. Filippo

    Filippo Expert Licensed User

    Thanks Klaus, very useful tutorial. :)
    Especially the tip with the application "DB Browser".
     
    KMatle likes this.
  6. Diceman

    Diceman Active Member Licensed User

Loading...
  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