Android Question Db creation error

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
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
 
Sort by date Sort by votes
mangojack

mangojack

Expert
Licensed User
Longtime 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
 
Upvote 0
Giusy

Giusy

Active Member
Licensed User
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...

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

mangojack

Expert
Licensed User
Longtime 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.
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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...

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
 
Upvote 0
Giusy

Giusy

Active Member
Licensed User
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...
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

  • DBRB.zip
    8.1 KB · Views: 239
Upvote 0
aeric

aeric

Expert
Licensed User
Longtime 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

  • DBRB2.zip
    8.2 KB · Views: 299
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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...

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
 
Upvote 0
Giusy

Giusy

Active Member
Licensed User
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...
Perfect, tank you RBS
 
Upvote 0
You must log in or register to reply here.

Similar Threads

B
  • Solved
Android Question Sqlite Could not open database
Replies
4
Views
1K
Brad Henderson
B
D
  • Locked
  • Question
Android Question MSSQL Query to MSSQL DB
Replies
10
Views
2K
Erel
Erel
Daniel44
  • Question
Android Question How to Restore a sqlite DB from a backed one
Replies
4
Views
2K
Daniel44
Daniel44
K
  • Question
Replacing .db from internet gives error
Replies
15
Views
3K
luke2012
luke2012
klaus
  • Article
Android Tutorial SQLiteDB
2 3 4
Replies
74
Views
45K
DonManfred
DonManfred
Top