Android Question Db creation error

mangojack said:

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

Click to expand...

Giusy said:

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

Click to expand...

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

RB Smissaert said:

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

Click to expand...

Giusy said:

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

Click to expand...

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

RB Smissaert said:

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

Click to expand...

Giusy said:

Perfect, tank you RBS

Click to expand...