Android Question Db creation error

Discussion in 'Android Questions' started by Giusy, Apr 18, 2019.

  1. Giusy

    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
    Code:
    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
     
  2. mangojack

    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.

    Code:
    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
     
  3. Giusy

    Giusy Active Member Licensed User

    Hi @mangojack
    With your program I have the same result as my first post :(
     
  4. mangojack

    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.
     
  5. RB Smissaert

    RB Smissaert Well-Known 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:

    Code:
    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 likes this.
  6. aeric

    aeric Active Member Licensed User

    @Giusy Can you upload your project?
     
  7. Giusy

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

    Attached Files:

  8. aeric

    aeric Active Member Licensed User

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

    Attached Files:

    Giusy likes this.
  9. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    This works fine with me:

    Code:
    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
     
    Giusy likes this.
  10. aeric

    aeric Active Member Licensed User

    Try to avoid using Dir.RootExternal
     
    Giusy likes this.
  11. Giusy

    Giusy Active Member Licensed User

    Perfect, tank you RBS
     
  12. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    No problem.

    RBS
     
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