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

Sergio Castellari · Jul 14, 2019

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 · Jul 14, 2019

Sergio Castellari said:
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 · Jul 14, 2019

Sergio Castellari said:
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.

Select the SQL library
Place > Dim SQL As SQLCipher 'SQL > in Process_Globals
SQL.Initialize. <look at the image below>
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 · Jul 14, 2019

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 · Jul 14, 2019

This is how I do normally.

Starter Service Module:

B4X:

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

B4X:

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

B4X:

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 · Jul 14, 2019

aeric said:
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 ...

B4X:

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 · Jul 14, 2019

mangojack said:
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 ...

B4X:

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 · Jul 14, 2019

Peter Simpson said:
At some point you will need to learn SQL for querying the database.

Select the SQL library

Place > Dim SQL As SQLCipher 'SQL > in Process_Globals

SQL.Initialize. <look at the image below>

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 · Jul 14, 2019

mangojack said:
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 · Jul 14, 2019

Hello @aeric,

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

B4X:

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 · Jul 14, 2019

Sergio Castellari said:
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.

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

Sergio Castellari

Active Member

ilan

Expert

Peter Simpson

Expert

mangojack

Expert

aeric

Expert

mangojack

Expert

aeric

Expert

Sergio Castellari

Active Member

Sergio Castellari

Active Member

Sergio Castellari

Active Member

aeric

Expert

Similar Threads

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

Active Member

Expert

Expert

Expert

Expert

Expert

Expert

Active Member

Active Member

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency