Android Question How do I create a DB SQLite from the APP itself?

Discussion in 'Android Questions' started by Sergio Castellari, Jul 14, 2019 at 12:48 AM.

  1. Sergio Castellari

    Sergio Castellari Member Licensed User

    Hello people,

    I need to know how I can create a SQLite DB from the APP. In such a way that when starting the APP, verify the existence of the DB, and if it does not exist, create it and you can even add predetermined information.
    I've been looking for information about SQLite, but everything I find takes for granted that the DB already exists.
    I want to know how to add Tables and indexes, besides adding, deleting and modifying records. In principle the idea is to be able to 'configure' the APP with different parameters.

    Regards,
    Sergio
     
  2. ilan

    ilan Expert Licensed User

    when you initialize the sql object then that process checks if the db exists. if not it is created as shown in the hint message

    sql.png
     
    Sergio Castellari and mangojack like this.
  3. Peter Simpson

    Peter Simpson Expert Licensed User

    At some point you will need to learn SQL for querying the database.
    1. Select the SQL library
    2. Place > Dim SQL As SQLCipher 'SQL > in Process_Globals
    3. SQL.Initialize. <look at the image below>
    4. In the Initialize method, make sure to set CreateIfNecessary to True
    I'm not sure if Erel has create a video about it, but it's simple to create an SQLite database in B4X.

    Untitled-2.png

    Enjoy...
     
  4. mangojack

    mangojack Well-Known Member Licensed User

    Have you read the latest B4X Booklets ... https://www.b4x.com/android/files/Booklets.zip

    B4X SQLite DataBase pdf booklet.

    Page 8 Section 2.1 Basics ... with some handy links

    Page 8 Section 2.1.1 Database Initialization ... shows you how to initialize / create a empty database (as @ilan has highlited above)

    Page 9 Section 2.1.2 Table Creation ... shows how to then create tables in your new database

    There is also discussion on adding , deleting records as well as sample apps for you to study.
     
  5. aeric

    aeric Active Member Licensed User

    This is how I do normally.

    Starter Service Module:

    Code:
    Sub Process_Globals
        
    Dim DB As SQL
        
    Dim DBFileDir As String = File.DirInternal
        
    Dim DBFileName As String = "app.db"
    End Sub
    Code:
    Sub Service_Create
        
    If File.Exists(DBFileDir, DBFileName) Then
            ReadData
        
    Else
            CreateDB
        
    End If
    End Sub
    Code:
    Sub CreateDB
        
    Dim strSQL As String
        
    Try
            DB.Initialize(DBFileDir, DBFileName, 
    True)
            strSQL = 
    "CREATE TABLE `DBInfo` ("
            strSQL = strSQL & 
    " `APP_Version` INTEGER,"
            strSQL = strSQL & 
    " `DB_Version` INTEGER )"
            DB.AddNonQueryToBatch(strSQL, 
    Null)
            strSQL = 
    "INSERT INTO `DBInfo` ("
            strSQL = strSQL & 
    " `APP_Version`,"
            strSQL = strSQL & 
    " `DB_Version` )"
            strSQL = strSQL & 
    " VALUES (?, ?)"
            DB.AddNonQueryToBatch(strSQL, 
    Array(Application.VersionCode, DB_VERSION))        
            
    Dim SenderFilter As Object = DB.ExecNonQueryBatch("DB")
            
    Wait For (SenderFilter) DB_NonQueryComplete (Success As Boolean)
            
    Log("DB.ExecNonQueryBatch('DB'): " & Success)
            
    If Success Then
                DB.Close
            
    Else
                strError = 
    "Fail to create database"
                
    If strMessage <> "" Then
                    strMessage = strMessage & 
    CRLF & strError
                
    Else
                    strMessage = strError
                
    End If
            
    End If
        
    Catch
            LogColor(
    "[Starter] CreateDB: " & LastException.Message, Colors.Red)
            strError = 
    "Fail to create database"
            
    If strMessage <> "" Then
                strMessage = strMessage & 
    CRLF & strError
            
    Else
                strMessage = strError
            
    End If
        
    End Try
    End Sub
     
    Sergio Castellari and ilan like this.
  6. mangojack

    mangojack Well-Known Member Licensed User

    Thank you for the above code .. I have learn't quite a bit from it .


    But ... at this very moment I think @Sergio Castellari might be a little more comfortable with something like ...

    Code:
    Sub Process_Globals
        
    Dim DB As SQL
        
    Dim DBFileDir As String = File.DirInternal
        
    Dim DBFileName As String = "app.db"
    End Sub

    Sub Service_Create
        
    If File.Exists(DBFileDir, DBFileName) Then
            ReadData
        
    Else
            CreateDB
        
    End If
    End Sub

    Sub CreateDB
        DB.Initialize(DBFileDir, DBFileName, 
    True)
        DB.ExecNonQuery(
    "CREATE TABLE MyTable(FirstName TEXT, LastName TEXT, Age INTEGER)"'refer section 2.1.3 of SQL Booklet regarding INTEGER Primary Key
    End Sub
    Just Saying ... :)
     
    Last edited: Jul 14, 2019 at 8:19 AM
    Sergio Castellari likes this.
  7. aeric

    aeric Active Member Licensed User

    Yes, we can start by using ExecNonQuery for single command.
     
    Sergio Castellari and mangojack like this.
  8. Sergio Castellari

    Sergio Castellari Member Licensed User

    Hello!

    How do you get SQLCipher? Is it an encrypted SQLite?

    Thank you,
    Sergio
     
  9. Sergio Castellari

    Sergio Castellari Member Licensed User

    Hello @mangojack !!!

    Many thanks!!!. I already downloaded the material and I'm looking at it !!!
    Cool!!!

    Regards,
    Sergio
     
    mangojack likes this.
  10. Sergio Castellari

    Sergio Castellari Member Licensed User

    Hello @aeric,

    Based on your idea and that of @mangojack, I made my small implementation.
    Is what I do here correct?
    Code:
    Sub Process_Globals
       
    'These global variables will be declared once when the application starts.
       'These variables can be accessed from all modules.
      Dim DB         As SQL
      
    Dim DBFileDir  As String = File.DirInternal
      
    Dim DBFileName As String = "APP.db"
    End Sub

    Sub Service_Create
       
    'This is the program entry point.
       'This is a good place to load resources that are not specific to a single activity.
      If File.Exists(DBFileDir, DBFileName) Then
        
    'ReadData
           Log("DB existente...")
      
    Else
        CreateDB
      
    End If

    End Sub

    Sub CreateDB
       
    Try
        DB.Initialize(DBFileDir, DBFileName, 
    True)
        DB.ExecNonQuery(
    "Create Table PARAMETROS (APPNOMBRE Text, VERSION Integer)"'refer section 2.1.3 of SQL Booklet regarding INTEGER Primary Key
        DB.ExecNonQuery("Insert Into PARAMETROS (APPNOMBRE,VERSION) Values ('TomaEstado','1')")
           
    Log("DB creada correctamente !!!")
       
    Catch
           LogColor(
    "[Starter] CreateDB: " & LastException.Message, Colors.Red)
       
    End Try
    End Sub
    I've tried it and it works, but I see that your implementation has more controls.

    Greetings and many thanks for the help!
    Sergio
     
  11. aeric

    aeric Active Member Licensed User

    Yes, your code is correct except you put '1' as text instead of 1 since your column for version is set as Integer.
     
    Sergio Castellari likes this.
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