Android Question Db creation error

Giusy

Active Member
Licensed User
Hi,
I created a database with one table and one record.
No error is reported, but going to see the database, it is created, but empty (no table and no record) and in addition creates a database with ".db-journal" extension.
Where is the error?
Thank you
B4X:
sub my_sub
If File.Exists(Main.DIRECTORY, DBFileName) = False Then
   
        SQL2.Initialize(Main.DIRECTORY, DBFileName, True)
        CreateDataBase
    Else
        SQL2.Initialize(Main.DIRECTORY, DBFileName, True)
    End If
. . . . . . . 
        Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
        SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
SQL2.close
end sub
Sub CreateDataBase
    Dim Query As String
    Query = "CREATE TABLE TABLEFASE1 (dato1  INTEGER, dato2 TEXT, dato3 TEXT)"
    SQL2.ExecNonQuery(Query)
End Sub
 

mangojack

Well-Known Member
Licensed User
I am presuming this code is run from another activity / service from Main activity because you use .. Main.DIRECTORY
without knowing exactly it is hard to test / debug

All I can say is running the following code from Starter service works as expected.

B4X:
Sub Process_Globals
    Public SQL2 As SQL
    Public DBDirectory, DBFileName As String
End Sub
Sub Service_Create  
    DBDirectory = File.DirInternal
    DBFileName = "mydata.db"
End Sub
Sub Service_Start (StartingIntent As Intent)
    my_sub
End Sub
Sub my_sub
    Dim dato1, dato2, dato3 As String
    dato1 = 1
    dato2 = "Test"
    dato3 = "abc"
          
    If File.Exists(DBDirectory, DBFileName) = False Then  
        SQL2.Initialize(DBDirectory, DBFileName, True)
        CreateDataBase
    Else
        SQL2.Initialize(DBDirectory, DBFileName, False) 'False .. the db is there so do not create new one.
    End If
      
    '. . . . . . .
    Dim Query As String  = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
    SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))      
End Sub
Sub CreateDataBase
        Dim Query As String
        Query = "CREATE TABLE TABLEFASE1 (dato1  INTEGER, dato2 TEXT, dato3 TEXT)"
        SQL2.ExecNonQuery(Query)
End Sub
 

Giusy

Active Member
Licensed User
I am presuming this code is run from another activity / service from Main activity because you use .. Main.DIRECTORY
without knowing exactly it is hard to test / debug

All I can say is running the following code from Starter service works as expected.

B4X:
Sub Process_Globals
    Public SQL2 As SQL
    Public DBDirectory, DBFileName As String
End Sub
Sub Service_Create 
    DBDirectory = File.DirInternal
    DBFileName = "mydata.db"
End Sub
Sub Service_Start (StartingIntent As Intent)
    my_sub
End Sub
Sub my_sub
    Dim dato1, dato2, dato3 As String
    dato1 = 1
    dato2 = "Test"
    dato3 = "abc"
         
    If File.Exists(DBDirectory, DBFileName) = False Then 
        SQL2.Initialize(DBDirectory, DBFileName, True)
        CreateDataBase
    Else
        SQL2.Initialize(DBDirectory, DBFileName, False) 'False .. the db is there so do not create new one.
    End If
     
    '. . . . . . .
    Dim Query As String  = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
    SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))     
End Sub
Sub CreateDataBase
        Dim Query As String
        Query = "CREATE TABLE TABLEFASE1 (dato1  INTEGER, dato2 TEXT, dato3 TEXT)"
        SQL2.ExecNonQuery(Query)
End Sub
Hi @mangojack
With your program I have the same result as my first post :(
 

mangojack

Well-Known Member
Licensed User
I am at a loss why the table is not created / record inserted on your side. You are testing on actual device? vs virtual device.
Unsure if that could even be possible cause ... clutching at straws.
 

RB Smissaert

Well-Known Member
Licensed User
Hi,
I created a database with one table and one record.
No error is reported, but going to see the database, it is created, but empty (no table and no record) and in addition creates a database with ".db-journal" extension.
Where is the error?
Thank you
B4X:
sub my_sub
If File.Exists(Main.DIRECTORY, DBFileName) = False Then
  
        SQL2.Initialize(Main.DIRECTORY, DBFileName, True)
        CreateDataBase
    Else
        SQL2.Initialize(Main.DIRECTORY, DBFileName, True)
    End If
. . . . . . .
        Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
        SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
SQL2.close
end sub
Sub CreateDataBase
    Dim Query As String
    Query = "CREATE TABLE TABLEFASE1 (dato1  INTEGER, dato2 TEXT, dato3 TEXT)"
    SQL2.ExecNonQuery(Query)
End Sub
I haven't tested anything, but to me it looks you are mixing up missing table and missing database.

Again, untested, but try this:

B4X:
sub my_sub

 Dim bCreateDB as boolean
 Dim Query As String

 bCreateDB = File.Exists(Main.DIRECTORY, DBFileName) = False

 SQL2.Initialize(Main.DIRECTORY, DBFileName, bCreateDB)

 CreateTable


. . . . . . . 
        Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
        SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
SQL2.close

end sub

Sub CreateTable
    Dim Query As String
    Query = "CREATE TABLE IF NOT EXISTS TABLEFASE1 (dato1  INTEGER, dato2 TEXT, dato3 TEXT)"
    SQL2.ExecNonQuery(Query)
End Sub

RBS
 

Giusy

Active Member
Licensed User
I haven't tested anything, but to me it looks you are mixing up missing table and missing database.

Again, untested, but try this:

B4X:
sub my_sub

 Dim bCreateDB as boolean
 Dim Query As String

 bCreateDB = File.Exists(Main.DIRECTORY, DBFileName) = False

 SQL2.Initialize(Main.DIRECTORY, DBFileName, bCreateDB)

 CreateTable


. . . . . . .
        Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
        SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
SQL2.close

end sub

Sub CreateTable
    Dim Query As String
    Query = "CREATE TABLE IF NOT EXISTS TABLEFASE1 (dato1  INTEGER, dato2 TEXT, dato3 TEXT)"
    SQL2.ExecNonQuery(Query)
End Sub

RBS
Hi,
I have copy your program and the error is te same as my first post (Two db are created MYTEST.DB and MYTEST.DB-journal - no table - no record)
see the attached zip
 

Attachments

aeric

Well-Known Member
Licensed User
B4X:
Sub my_sub

    Dim bCreateDB As Boolean
    Dim Query As String

    bCreateDB = File.Exists(File.DirInternal, "MYTEST.DB") ' = False
    
    If bCreateDB Then
        SQL2.Initialize(File.DirInternal, "MYTEST.DB", False)
    Else
        SQL2.Initialize(File.DirInternal, "MYTEST.DB", True)
        CreateTable
    End If

    dato1=1
    dato2="aaa"
    dato3="bbb" ' dato2="bbb"
    Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
    SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
    'SQL2.close

    CheckData
End Sub

Sub CheckData
    Dim cur As Cursor
    Dim Query As String = "SELECT * FROM TABLEFASE1"
    cur = SQL2.ExecQuery(Query)
    If cur.RowCount > 0 Then
        cur.Position = cur.RowCount - 1
        Log("Data: " & cur.GetInt2(0) & ", " & cur.GetString2(1)& ", " & cur.GetString2(2))
    End If
End Sub

Sub CreateTable
    Dim Query As String
    Query = "CREATE TABLE IF NOT EXISTS TABLEFASE1 (dato1  INT, dato2 TEXT, dato3 TEXT)"
    SQL2.ExecNonQuery(Query)
End Sub
 

Attachments

RB Smissaert

Well-Known Member
Licensed User
Hi,
I have copy your program and the error is te same as my first post (Two db are created MYTEST.DB and MYTEST.DB-journal - no table - no record)
see the attached zip
This works fine with me:

B4X:
Sub my_sub

 Dim bCreateDB As Boolean
 Dim Query As String
 Dim RS1 As ResultSet
 
 File.Delete(File.DirInternal, "MYTEST.DB")
 
 bCreateDB = File.Exists(File.DirInternal, "MYTEST.DB") = False
 
 SQL2.Initialize(File.DirInternal, "MYTEST.DB", bCreateDB)

 CreateTable

 dato1=1
 dato2="aaa"
 dato3="bbb"
 
 Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
 
 SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
 
 Query = "select * from TABLEFASE1"
 RS1 = SQL2.ExecQuery(Query)
 
 Do While RS1.NextRow
  Log(RS1.GetInt2(0) & ", " & RS1.GetString2(1) & ", " & RS1.GetString2(2))
 Loop

End Sub
Note that you had done dat02="bbb", but that is not really relevant.

RBS
 

aeric

Well-Known Member
Licensed User
Try to avoid using Dir.RootExternal
 

Giusy

Active Member
Licensed User
This works fine with me:

B4X:
Sub my_sub

 Dim bCreateDB As Boolean
 Dim Query As String
 Dim RS1 As ResultSet
 
 File.Delete(File.DirInternal, "MYTEST.DB")
 
 bCreateDB = File.Exists(File.DirInternal, "MYTEST.DB") = False
 
 SQL2.Initialize(File.DirInternal, "MYTEST.DB", bCreateDB)

 CreateTable

 dato1=1
 dato2="aaa"
 dato3="bbb"
 
 Query = "INSERT INTO TABLEFASE1 VALUES (?, ?, ?)"
 
 SQL2.ExecNonQuery2(Query, Array As String(dato1,dato2,dato3))
 
 Query = "select * from TABLEFASE1"
 RS1 = SQL2.ExecQuery(Query)
 
 Do While RS1.NextRow
  Log(RS1.GetInt2(0) & ", " & RS1.GetString2(1) & ", " & RS1.GetString2(2))
 Loop

End Sub
Note that you had done dat02="bbb", but that is not really relevant.

RBS
Perfect, tank you RBS
 
Top