Android Question How to save image to SQL blob fields after downloaded from url?

[eSolution]

Hi,
I get a JSON string from a webservice (like Facebook Graph). Among the "items" that I got when parsing the JSON string are image urls. I want to save all of my items in a SQLite database table but I want to download the image, transform it in to a BLOB and save it in this format in the table. I have seen examples on how to transform an image file in a BLOB. The ImageDownloader service is saving the downloaded image to a imageview ...

Does anyone have a code snippet on how to put all this together?

Thank you!

 

[Mahares]

query = "CREATE TABLE vid (ID INTEGER PRIMARY KEY AUTOINCREMENT, IMG BLOB,TITLE TEXT,VIDID TEXT)"

Your statement for creating the table. is good. But what you might want to do is: Put this code above that line to start fresh with a new table

query ="DROP TABLE IF EXISTS vid"
SQL.ExecNonQuery(query)

 

[eSolution]

Your statement for creating the table. is good. But what you might want to do is: Put this code above that line to start fresh with a new table

query ="DROP TABLE IF EXISTS vid"
SQL.ExecNonQuery(query)

I already have this in my code

 

[Mahares]

I already have this in my code

I do not see that DROP statement in the code you posted in the previous post. Other things you might want to do to debug is comment all the try catch statements and all the transaction statements and see where the stop is. Also, you might want to start with a brand new database. I am out of ideas.

 

[eSolution]

This is the code right now:

Sub btn_refresh_Click
    Log("Clicked: Refresh")
       
    DropTable
    CreateTable
    YTPlayListRequest

End Sub

Sub DropTable
    Dim query As String
    query = "DROP TABLE IF EXISTS vid"
    Log("DropTable: " & query)
    Starter.VIDEODB.ExecNonQuery(query)
End Sub


Sub CreateTable
    Starter.VIDEODB.BeginTransaction
    Try
        Dim query As String
        query = "CREATE TABLE vid (ID INTEGER PRIMARY KEY AUTOINCREMENT, IMG BLOB,TITLE TEXT,VIDID TEXT)"
       
        Log("CreateTable: " & query)
        Starter.VIDEODB.ExecNonQuery(query)
    Starter.VIDEODB.TransactionSuccessful
    Catch
        Log(LastException.Message)
    End Try
    Starter.VIDEODB.EndTransaction
   
End Sub

 

[eSolution]

Problem Solved! It seems that the database that I created externally with SqliteBrowser and attached to the assets is not accepted by B4A. The solution was to create the db file in the app itself. Now everything works fine and I can create the table and save all the data in the database (blobs included)

Thank you everyone for the help!

 

[mangojack]

Problem Solved! It seems that the database that I created externally with SqliteBrowser and attached to the assets is not accepted by B4A. The solution was to create the db file in the app itself. Now everything works fine and I can create the table and save all the data in the database (blobs included)

Thank you everyone for the help!

pleased to hear that ..
Strange though... have used that many times in past and never a problem.

 

[eSolution]

@mangojack & @eurojam & @Mahares I need some help again

I want to read the database and load the data in a CustomListView.
The code is reading the items from the database, I can display the strings values in the log and I can see that the BLOB is read in the input stream but I got an error when I try to initialize the bitmap with the input stream ... I have no idea where the mistake is ... it seem that the BLOB data is corrupted or something...

This is my code:

Sub BuildItemsYT

    clvYT.Clear
'Start DB Query
    Dim cursor1 As Cursor

    'cursor1 = Starter.VIDEODB.ExecQuery("SELECT IMG, TITLE, VIDID FROM vid")
    cursor1 = Starter.VIDEODB.ExecQuery2("SELECT IMG, TITLE, VIDID FROM vid", Null)
        Log(cursor1.RowCount)
      
    For i = 0 To cursor1.RowCount - 1
    cursor1.Position = i
  
        Log(cursor1.GetString("TITLE"))
        Log(cursor1.GetString("VIDID"))
      
    Dim V() As Object
    V = Array As Object(cursor1.GetBlob("IMG"), cursor1.GetString("TITLE"), cursor1.GetString("VIDID"))

    Dim p As Panel
            p.Initialize("")
            clvYT.Add(p, 120dip, V)
            p.LoadLayout("ListItemYT")
            lblYTtitle.Text = cursor1.GetString("TITLE")
            lblYTcode.Text = "VideoID: " & cursor1.GetString("VIDID")
          
'transforming the BLOB in an image

    Dim Buffer() As Byte 'declare an empty byte array
    Buffer = cursor1.GetBlob("IMG")
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
 
    Dim Bitmap1 As Bitmap
    Bitmap1.Initialize2(InputStream1)
    InputStream1.Close
    imgvYT.Initialize("imgvYT")
    imgvYT.Bitmap = Bitmap1
  
    Next
    cursor1.Close  
  
End Sub

When the execution gets here: Bitmap1.Initialize2(InputStream1) I got this error:

java.lang.RuntimeException: Error loading bitmap.

any idea on how may I fix this error?

Thank you!

 

[mangojack]

Hi ... your code looks OK. What is the image format ?
View the DB with SqliteBrowser if you can .. it should show the image format if stored correctly.

 

[eSolution]

Hi ... your code looks OK. What is the image format ?
View the DB with SqliteBrowser if you can .. it should show the image format if stored correctly.

This is how the database looks:

I don't think this is the right format So the problem may be in the download process. If I use the links from the JSON and use "ImageDownloader" then the image is displayed correctly in an imageview.

 

[mangojack]

I don't think this is the right format So the problem may be in the download process.

Agree .. I think I'd be struggling to help further .. (will do some testing though)
Hopefully @eurojam , @Mahares or others can help

 

[eurojam]

Hopefully @eurojam , @Mahares or others can help

as I follow it sounds like the problem lies in the part when you stram the image and load it into the blob.... One thing you can try is to use Erels sqlite viewer based on B4J:
https://www.b4x.com/android/forum/threads/sqlite-viewer.34967/
then you can see if there is a valid blob image in your database...

 

[eSolution]

the result from the SQLite Viewer is this:

So no image shown, the software can't "translate" the blob, same as in my B4A app. I made the code to save the JobResult directly in to a file (the same stream that I put in a BytesArray) and all the images are saved in my app folder and I can see then (the mobile file viewer shows me even thumbnails for the images) So the problem is in the code that put the downloaded stream in to the BytesArray or Blob ...

        JobResult = Job.GetInputStream
         'this save the downloaded data in BytesArray and to BLOB and is corrupted data
out.InitializeToBytesArray(10000)   
           File.Copy2(JobResult, out)
           out.close
           'this save the images on the device and they are ok
        out1 = File.OpenOutput(File.DirDefaultExternal, K &".jpg",False )
        File.Copy2(Job.GetInputStream, out1)
        out1.Close

Any ideas why?

 

[eurojam]

I think the problem is still in the JobDone Sub. You have this block in jobDone:

        VIDEODB.BeginTransaction
        Try
        Dim s() As String 'define the array
        s = Job.tag'get it from tag
        VIDEODB.ExecNonQuery2("INSERT INTO vid VALUES( ?, ?, ?)", Array As Object(out,s(0),s(1))) ' <put it into the parameter array
    
        VIDEODB.TransactionSuccessful
        Catch
        Log(LastException.Message)
        End Try
        VIDEODB.EndTransaction

If suppose you have to get rid of VIDEODB.BeginTransaction - VIDEODB.EndTransaction. Because it will get in conflict with the next operation which can be at the same time and you will have a second VIDEODB.BeginTransaction - VIDEODB.EndTransaction because they are asynchronous. put the transaction to that point where your initialize your download, e.g. in the loop when parsing your json...and put the EndTransaction at the end of the loop...I hope you understand what I mean....

 

[eSolution]

I think the problem is still in the JobDone Sub. You have this block in jobDone:

        VIDEODB.BeginTransaction
        Try
        Dim s() As String 'define the array
        s = Job.tag'get it from tag
        VIDEODB.ExecNonQuery2("INSERT INTO vid VALUES( ?, ?, ?)", Array As Object(out,s(0),s(1))) ' <put it into the parameter array
   
        VIDEODB.TransactionSuccessful
        Catch
        Log(LastException.Message)
        End Try
        VIDEODB.EndTransaction

If suppose you have to get rid of VIDEODB.BeginTransaction - VIDEODB.EndTransaction. Because it will get in conflict with the next operation which can be at the same time and you will have a second VIDEODB.BeginTransaction - VIDEODB.EndTransaction because they are asynchronous. put the transaction to that point where your initialize your download, e.g. in the loop when parsing your json...and put the EndTransaction at the end of the loop...I hope you understand what I mean....

It does not work, same results... but I think you are right, the blobs are not completely written or something like this, because the database file has different dimensions every time ...

 

[eurojam]

can you attach your project?

 

[eSolution]

can you attach your project?

I will put only this parts in a new project and attach it here in 20 min

 

[eurojam]

oh that is great, I will be in a meeting now, then playing 2 hours badminton, then some beer, then dinner,....but I am sure, that in the worlds best forum anybody will solve this problem soon

and I will be curious about the solution!

 

[Erel]

(I haven't followed the whole thread.)

JobDone is executed on the main thread. It will never fire in the middle of another code execution.

Storing a downloaded image can be done in a single line:

sql.ExecNonQuery2("INSERT INTO table VALUES(?)", Array (Bit.InputStreamToBytes(Job.GetInputStream)))

Or in two lines:

Dim b() As Bytes = Bit.InputStreamToBytes(Job.GetInputStream)
sql.ExecNonQuery2("INSERT INTO table VALUES(?)", Array (b))

You don't need to create a transaction for a single insert command (though nothing bad will happen if you do).

 

[eSolution]

(I haven't followed the whole thread.)

JobDone is executed on the main thread. It will never fire in the middle of another code execution.

Storing a downloaded image can be done in a single line:

sql.ExecNonQuery2("INSERT INTO table VALUES(?)", Array (Bit.InputStreamToBytes(Job.GetInputStream)))

Or in two lines:

Dim b() As Bytes = Bit.InputStreamToBytes(Job.GetInputStream)
sql.ExecNonQuery2("INSERT INTO table VALUES(?)", Array (b))

You don't need to create a transaction for a single insert command (though nothing bad will happen if you do).

Thank you Erel, I can save the images in the device so I know that the download is working. The problem is with the Blob, it is saved in a form that can't be put back in an image. I have attached a project with this functionality from my own bigger project. If you populate the database the app will go to Youtube, get a json file then parse it and try to GET the images from Youtube servers using the links from the parsed JSON. It will populate the database on the fly.
Wen you try to display the DB content it will get an error when it will try to initialize the bmp with the blob content.

I have no idea atm where is the problem

Edit: Problem solved with Erel 1 line code

sql.ExecNonQuery2("INSERT INTO table VALUES(?)", Array (Bit.InputStreamToBytes(Job.GetInputStream)))

Now I can see the images in the DB with the QSLiteViewer

 

[Erel]

VIDEODB.ExecNonQuery2("INSERT INTO vid VALUES(?, ?, ?, ?)", Array As Object(Null,out,s(0),s(1)))

This code is wrong. You cannot put an OutputStream in a database. You should use the code I posted to convert the downloaded data to an array of bytes and then put the array of bytes in the database.