Android Question Sqlchipper & SQLite Database

[sktanmoy]

I've a database with 30000 data. I wrote an app using B4A to read the sqlite database and write back into another one with sqlchiper encrypted. Let me paste my code

Sub Process_Globals
    Dim sqln As SQL
    Dim sqlc As SQLCipher   
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
    #Region Main DB
    File.Copy(File.DirAssets, "NewBndict.sqlite", File.DirInternal,  "NewBndict.sqlite")
    Try
        sqln.Initialize(File.DirInternal, "NewBndict.sqlite", False)
    Catch
        Log("Main DB Initialization failed")
    End Try
    #End Region
   
    #Region Encrypted Database
    Try
        sqlc.Initialize(File.DirRootExternal, "bndict.db", True, "MYFAKEKEY", "")   
    Catch
        Log("Encrypted DB Initialization failed")
    End Try
    #End Region
   
    #Region Add Table to enc databse
    Try
        sqlc.ExecNonQuery("CREATE TABLE words(ID INTEGER PRIMARY KEY, English TEXT, Bangla TEXT, Type TEXT, Bm TEXT, Ext TEXT)")
    Catch
        Log("Table Creation Failed")
    End Try
    #End Region
   
   
    ProgressDialogShow2("Staring copy...", False)
   
    Dim Cursor1 As Cursor
    Cursor1 = sqln.ExecQuery("SELECT * FROM words")
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        Dim ID, Eng, Bng, Typ, qur, qur1, qur2, qur3, Query As String
       
        ID = Cursor1.GetString("ID")
        Eng = Cursor1.GetString("English")
        Bng = Cursor1.GetString("Bangla")
        Typ = Cursor1.GetString("Type")
        qur = "INSERT INTO words (ID, English, Bangla, Type) VALUES (" & ID & ", """
        qur1 = Eng & """, """
        qur2 = Bng & """, """
        qur3 = Typ & """ )"
       
        Query = qur & qur1 & qur2 & qur3
       
        'Log(Query)

        sqlc.ExecNonQuery(Query)
    Next
    Cursor1.Close
   
    ProgressDialogHide
   
End Sub

And I got a database called bndict.db which had all of the rows and fully non-encrypted! Please help me find out my mistake.

 

[Erel]

I don't see any error here. Have you checked the logs for any errors?

 

[sktanmoy]

I don't see any error here. Have you checked the logs for any errors?

No, there was no error. I ran the app in real device instead of emulator, in debug(legacy) mode, it took around 20 minutes to be completed with just "Activity Started" & "Activity Paused" message in log window.

As the database will be a commercial one, I need that encrypted, so cannot attach the project here. I'll create a dummy database and the project so that can get help from you.

 

[Erel]

I run this code in rapid debug mode. It took about 30 seconds and the complete database was encrypted:

Sub Activity_Create(FirstTime As Boolean)
   File.Copy(File.DirAssets, "bndict", File.DirInternal,  "bndict-fresh.db")
   File.Delete(File.DirRootExternal, "bndict-enc.db")
   sqln.Initialize(File.DirInternal, "bndict-fresh.db", False)
   sqlc.Initialize(File.DirRootExternal, "bndict-enc.db", True, "FAKEKEY", "")   
   sqlc.ExecNonQuery("CREATE TABLE words(ID INTEGER PRIMARY KEY, English TEXT, Bangla TEXT, Type TEXT, Bm TEXT, Ext TEXT)")
   ProgressDialogShow2("Staring...", False)
   
   Dim Cursor1 As Cursor
  Cursor1 = sqln.ExecQuery("SELECT * FROM words")
   sqlc.BeginTransaction
  For i = 0 To Cursor1.RowCount - 1
  Cursor1.Position = i
     Dim Eng, Bng, qur, qur1, qur2, Query As String
     
  Eng = Cursor1.GetString("English")
  Bng = Cursor1.GetString("Bangla")
     qur = "INSERT INTO words (English, Bangla) VALUES ("""
     qur1 = Eng & """, """
     qur2 = Bng & """ )"
     
     Query = qur & qur1 & qur2
     
     Log(Query)
     
     sqlc.ExecNonQuery(Query)
  Next
  Cursor1.Close
   sqlc.TransactionSuccessful
   sqlc.EndTransaction
   ProgressDialogHide
   
End Sub

1. You should use transactions when inserting data.
2. I guess that your code failed for some reason. You didn't see it because of all these Try / Catch block. There is no reason to catch a fatal error. If the database cannot be opened then it is better to let your program crash.
3. The icudt file was missing from the Files tab.

 

[sktanmoy]

Excellent, worked for me this time

Thanks.