Android Question [SOLVED] Problem with SQLite and image in table

Nikos Koromilas

Member
Licensed User
Longtime User
Hello.
This is my first post to the forum, so please excuse me if i make any mistakes regarding the posting procedure.
I have a little app as a test in order to save a png file from the File.DirAssets directory in a SQLite table named Images and in a field named Image.

The saveImage Sub is the following:

Saving Sub: 
Private Sub SaveImage

    Private InputStream1 As InputStream
    Private OutputStream1 As OutputStream
    Private Buffer() As Byte
    
    InputStream1 = File.OpenInput(File.DirAssets,"map.png")
    OutputStream1.InitializeToBytesArray(1000)
    File.Copy2(InputStream1,OutputStream1)
    Buffer = OutputStream1.ToBytesArray
    
    SQL1.ExecQuery("Delete from Images")
    SQL1.ExecNonQuery2("INSERT INTO Images  VALUES (?)", Array As Object(Buffer))
    
End Sub

(please note that this is called from a simple button and for example reasons each time the sub is triggered the table is deleted and a new single entry is posted)

The LoadImage Sub is the following:

LoadImage Sub: 
Private Sub LoadImage
     Private rs As ResultSet
    rs = SQL1.ExecQuery("SELECT image from Images")
    Do While rs.NextRow
        Private Buffer() As Byte
        Buffer = rs.GetBlob("Image")
        Private inputStream1 As InputStream
        inputStream1.InitializeFromBytesArray(Buffer,0,Buffer.Length)

        Private b As Bitmap
        b.Initialize2(inputStream1)

        inputStream1.Close
    Loop
End Sub

(as with the SaveImage, this sub is called by a button)

The problem is in sub LoadImage and in line b.Initialize2(inputStream1)
The error in Log is


What am i doing wrong?

Thank you in advance
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
Nikos Koromilas said:
What am i doing wrong?
Click to expand...
First you need to make sure the data base is saved from File.DirAssets to File.DirInternal before you can save to it. You cannot access a database in assets folder. Your code does not say where the database is saved.
Second. make sure you close the output stream when done with it:
OutputStream1.close

ONE MORE IMPORTANT THING:
These 2 lines,
rs = SQL1.ExecQuery("SELECT image from Images")
and
Buffer = rs.GetBlob("Image")
one line has Image, the other has image. They must be the same lower or upper start depending on how you saved the column name to the table. I think, this is your culprit.
 
Last edited:
Upvote 0

Nikos Koromilas

Member
Licensed User
Longtime User
Thank you very much for the reply. I replaced the word Image so both references have the same case but the result is the same. Also the database is not in the File.DirAssets Folder. On the Activity_Create InitDB Sub is called:

InitDB: 
Private Sub InitDB
    Private rp As RuntimePermissions
    Private dbDir As String
    dbDir = rp.GetSafeDirDefaultExternal("testing") & "/"
    Log("DBDir is : " & dbDir)
    
    If SQL1.IsInitialized = False Then
        Try
            SQL1.Initialize(dbDir, "imagedb.db", False)
        Catch
            If File.Exists(File.DirAssets,"imagedb.db") = True Then
                File.Copy(File.DirAssets,"imagedb.db",dbDir,"imagedb.db")
                SQL1.Initialize(dbDir, "imagedb.db", False)
            End If
        End Try
    End If
End Sub

If i change the code and load directly the image to the Bitmap Variable, everything executes correctly.
What am i missing?
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Nikos Koromilas said:
What am i missing?
Click to expand...
B4X: 
If File.Exists(File.DirAssets,"imagedb.db") = True Then
                File.Copy(File.DirAssets,"imagedb.db",dbDir,"imagedb.db")
                SQL1.Initialize(dbDir, "imagedb.db", False)
            End If
should be,
B4X: 
If File.Exists(dbDir,"imagedb.db") = False Then
                File.Copy(File.DirAssets,"imagedb.db",dbDir,"imagedb.db")
                SQL1.Initialize(dbDir, "imagedb.db", False)
            End If
Otherwise you are copying the file to dbDir every time:
I plugged your code that you have in post 1 with the slight change I mentioned into a project and it worked fine for me. If you like to export your project as zip to the forum, I or anyone else can look at it and I am sure someone will find the problem.
Also, you do not need the / in:
dbDir = rp.GetSafeDirDefaultExternal("testing") & "/"
 
Upvote 0

Nikos Koromilas

Member
Licensed User
Longtime User
Thank you very much for the effort. I have attached the project in a zip format. Thank you again
 

Attachments

  • ImageSQL.zip
    84.6 KB · Views: 180
Upvote 0

Nikos Koromilas

Member
Licensed User
Longtime User
One problem was with the copy of the database as you suggested. The other was with the Image field. The last was with the record in the database. I deleted the Image from the files and recopy it. After that the app executed correctly.
Thanks again for everything.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…