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

[Sergio Castellari]

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

 

[ilan]

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

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

 

[Peter Simpson]

I need to know how I can create a SQLite DB from the APP.

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.

Enjoy...

 

[mangojack]

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.

 

[aeric]

This is how I do normally.

Starter Service Module:

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

 

[mangojack]

This is how I do normally.

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

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

 

[aeric]

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

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)")
End Sub

Just Saying ...

Yes, we can start by using ExecNonQuery for single command.

 

[Sergio Castellari]

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.

View attachment 82148

Enjoy...

Hello!

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

Thank you,
Sergio

 

[Sergio Castellari]

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.

Hello @mangojack !!!

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

Regards,
Sergio

 

[Sergio Castellari]

Hello @aeric,

Based on your idea and that of @mangojack, I made my small implementation.
Is what I do here correct?

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

 

[aeric]

DB.ExecNonQuery("Insert Into PARAMETROS (APPNOMBRE,VERSION) Values ('TomaEstado','1')")

Yes, your code is correct except you put '1' as text instead of 1 since your column for version is set as Integer.