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!

 

[mangojack]

Which part of the above scenario are you unsure of ? .

If it is saving the blobs to SQL db then the attached example should help.
Combining this and the other forum examples you have studied , you should be able to put together what your trying to achieve ...

 

[eSolution]

Which part of the above scenario are you unsure of ? .

If it is saving the blobs to SQL db then the attached example should help.
Combining this and the other forum examples you have studied , you should be able to put together what your trying to achieve ...

Thank you for your answer.
I know this example from Erel's SQL thread. I wanted to avoid reading an image file from the device to BLOB, I have more then 1000 items in the JSON file, all with an image, if I write every image to the device then make the BLOB, save it to the database then delete the file and do the same with the next item and so on it will take a lot of time and I think it will get a lot of resources.

My problem was how to combine all the examples in to this scenario: (I have the image URL from the JSON) Download the image from the web directly in to the stream and transform it in a BLOB and write it in the database then go to the next url (item) and so on.

 

[eurojam]

you can do it directly with httpUtils2. In the jobDone sub insert the image as stream into the database blob field. something like this, please check for the correct syntax, because it is written without testing:

Sub JobDone (Job As HttpJob)
   Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
   If Job.Success = True Then
     Select Job.JobName
        case "JobImageDownload"
           Dim out As OutputStream
           File.Copy2(Job.GetInputStream, out)
           out.close
           sql1.ExecNonQuery2("INSERT INTO main VALUES(?, ?)", Array As Object("myFieldName", out))
...
end sub

 

[eSolution]

I still have some problems.
I initialize the DB in the Starter, I copy/create the DB in the Starter, I download the image and put it in to the Stream object in the Starter. In my main module I create the table in the database. In my parser module I parse the Json and try to put everything in the database but I got an error about the database being corrupted. This are the code snippets:

Starter:

Sub Process_Globals
    'YT
    Public NextPageToken As String
    Public out As OutputStream
    'DB
    Dim VIDEODB As SQL
End Sub

Sub Service_Create
    'DB
    File.Delete(File.DirDefaultExternal, "videos.db") ' only for testing, removes the database
    File.Delete(File.DirInternal, "videos.db")
  
        'check if the database already exists
    If File.Exists(File.DirDefaultExternal, "videos.db") = False Then
        'copy the default DB
        File.Copy(File.DirAssets, "videos.db", File.DirDefaultExternal, "videos.db")
        'if not, initialize it
        VIDEODB.Initialize(File.DirDefaultExternal, "videos.db", True)
        'and create it
        'CreateDataBase
        'copy the default DB
        File.Copy(File.DirAssets, "videos.db", File.DirDefaultExternal, "videos.db")
    Else
        'if yes, initialize it
        VIDEODB.Initialize(File.DirDefaultExternal, "videos.db", True)
    End If
End Sub

Private Sub YTimgRequest(imgurl As String)
    Dim URLtoGET As String = imgurl
    Log(URLtoGET)
  
    Dim J As HttpJob
        J.Initialize("JobImageDownload", Me)
        J.Download (URLtoGET)
        J.GetRequest.SetHeader("Connection", "Keep-Alive")   
    Log("Downloading Image")
End Sub

Sub JobDone (Job As HttpJob)
     
Dim JobResult As String
 
   Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
    If Job.Success = True And Job.JobName = "JobImageDownload" Then
        JobResult = Job.GetString      
           File.Copy2(Job.GetInputStream, out)
           out.close
         Job.Release  
    Else
      Log("Error: " & Job.ErrorMessage)
      ToastMessageShow("Error: " & Job.ErrorMessage, True)
   End If
 
   Job.Release
End Sub

Main Module:

Sub DropTable(SQL As SQL, TableName As String)
    Dim query As String
    query = "DROP TABLE IF EXISTS [" & TableName & "]"
    Log("DropTable: " & query)
    SQL.ExecNonQuery(query)
End Sub

Sub CreateTable(SQL As SQL, TableName As String)
    SQL.BeginTransaction
    Try
        Dim query As String
        'query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, IMG BLOB (0) NOT NULL, TITLE TEXT (0) NOT NULL, VIDID STRING (0) NOT NULL)"
        query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] (IMG BLOB, TITLE TEXT, VIDID STRING)"
      
        Log("CreateTable: " & query)
        SQL.ExecNonQuery(query)
    SQL.TransactionSuccessful
    Catch
        Log(LastException.Message)
    End Try
    SQL.EndTransaction
  
End Sub

Parser Module:

Public Sub ParseYT(text As String)
 
parse.Initialize(text)
 
Log("parser started ")

Dim root As Map = parse.NextObject
    Dim NextPageToken As String= root.Get("nextPageToken")
    Dim items As List = root.Get("items")
        For Each colitems As Map In items
            Dim media_url As String
            Dim snippet As Map = colitems.Get("snippet")
                Dim resourceId As Map = snippet.Get("resourceId")
                    Dim videoId As String = resourceId.Get("videoId")
                Dim title As String = snippet.Get("title")
                Dim thumbnails As Map = snippet.Get("thumbnails")      
                    Dim default As Map = thumbnails.Get("default")
                    Dim url As String = default.Get("url")
                    media_url = url
         
            Dim linkComp As String = media_url & ";" & title & ";" & videoId
            Starter.LVLlinks.Add(linkComp)


        'Get the blob
        Starter.out.InitializeToBytesArray(100)
        CallSubDelayed2(Starter, "YTimgRequest", media_url)
        'Put to database
        Starter.VIDEODB.BeginTransaction
        Try
        Starter.VIDEODB.ExecNonQuery2("INSERT INTO vid VALUES(?, ?, ?)", Array As Object(Starter.out, title, videoId))
        Starter.VIDEODB.TransactionSuccessful
        Catch
        Log(LastException.Message)
        End Try
        Starter.VIDEODB.EndTransaction


        Next
Log("NextPageToken " & Starter.NextPageToken & " VidID " & videoId & " URL " & url & " TITLE " & title)

Main.PageToken = NextPageToken

End Sub

The error that I get is:

parser_parseyt (java line: 151)
android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed (code 11)

How can I create a table with 3 columns: Image (BLOB), Title (TEXT), VideoID(TEXT or STRING) and put all the data from the parsed JSON in it (downloading the images from the web and making them BLOBs)?

 

[eurojam]

IMO one problem win your code is, that you call the download (
CallSubDelayed2(Starter, "YTimgRequest", media_url)) and then in the same routine you begin the database transaction. But the download is asynchronous. For that reason you should put the insert statement into the jobDone sub, like in my first example.

 

[eSolution]

IMO one problem win your code is, that you call the download (
CallSubDelayed2(Starter, "YTimgRequest", media_url)) and then in the same routine you begin the database transaction. But the download is asynchronous. For that reason you should put the insert statement into the jobDone sub, like in my first example.

yes but I have to add the entire row at the same time: the blob, title and video id ... how do I do that from JobDone? If I save all in global variables I have the same situation: by the time that JobDone is executed the global Title and VideoID are overwritten several times by the parser that is being executed in parallel ... So... What is the solution here?

 

[eurojam]

you can pass id, title to the job.tag and let do the rest by the jobdone sub

Private Sub YTimgRequest(imgurl As String, title as string, id as string)
    Dim URLtoGET As String = imgurl
    Log(URLtoGET)
 
    Dim J As HttpJob
        J.Initialize("JobImageDownload", Me)
        J.Download (URLtoGET)
        J.GetRequest.SetHeader("Connection", "Keep-Alive")   
        j.tag = array as string(title, id) '<- then you have it all in the job done sub
    Log("Downloading Image")
End Sub

 

[eSolution]

And another problem is this err

you can pass id, title to the job.tag and let do the rest by the jobdone sub

Private Sub YTimgRequest(imgurl As String, title as string, id as string)
    Dim URLtoGET As String = imgurl
    Log(URLtoGET)

    Dim J As HttpJob
        J.Initialize("JobImageDownload", Me)
        J.Download (URLtoGET)
        J.GetRequest.SetHeader("Connection", "Keep-Alive")  
        j.tag = array as string(title, id) '<- then you have it all in the job done sub
    Log("Downloading Image")
End Sub

Interesting, I did not know that I will try it now ... another problem is this db error:
android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed (code 11)

If I create the table with an ID column that is the key and auto-incremented then I do not have this error but when I try to insert in to database I need
INSERT INTO vid VALUES(?,?, ?, ?)", ArrayAs Object(id,Starter.out, title, videoId) and I have no idea what to put in the id to be accepted in the table

 

[eurojam]

have you tried without the id in your statement. The database will do it, if it is an autoinkrement...

 

[eSolution]

have you tried without the id in your statement. The database will do it, if it is an autoinkrement...

yes and I got an error about the data type I was thinking that it tries to put the blob in the ID column but may be the blob itself was not saved correctly if the download was slower then the parser.

Anyway, how do I call JobDone to get the title and ID recognized inside the JobDone Sub?

'download images
Private Sub YTimgRequest(imgurl As String, title As String, id As String)
    Dim URLtoGET As String = imgurl
    Log(URLtoGET)
   
    Dim J As HttpJob
        J.Initialize("JobImageDownload", Me)
        J.Download (URLtoGET)
        J.GetRequest.SetHeader("Connection", "Keep-Alive")    
        J.tag = Array As String(title, id) '<- then you have it all in the job done sub
    Log("Downloading Image")
End Sub

Sub JobDone (Job As HttpJob)
      
Dim JobResult As String
  
   Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
    If Job.Success = True And Job.JobName = "JobImageDownload" Then
        JobResult = Job.GetString
            out.InitializeToBytesArray(100)       
           File.Copy2(Job.GetInputStream, out)
           out.close

        VIDEODB.BeginTransaction
        Try

        VIDEODB.ExecNonQuery2("INSERT INTO vid VALUES( ?, ?, ?)", Array As Object(out,title, id)) ' < title and id are in red because is undeclared
       
        VIDEODB.TransactionSuccessful
        Catch
        Log(LastException.Message)
        End Try
        VIDEODB.EndTransaction
       
         Job.Release   
    Else
      Log("Error: " & Job.ErrorMessage)
      ToastMessageShow("Error: " & Job.ErrorMessage, True)
   End If
  
   Job.Release
End Sub

 

[eurojam]

like this

Sub JobDone (Job As HttpJob)
    
Dim JobResult As String

   Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
    If Job.Success = True And Job.JobName = "JobImageDownload" Then
        JobResult = Job.GetString
            out.InitializeToBytesArray(100)     
           File.Copy2(Job.GetInputStream, out)
           out.close

        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
     
         Job.Release 
    Else
      Log("Error: " & Job.ErrorMessage)
      ToastMessageShow("Error: " & Job.ErrorMessage, True)
   End If

   Job.Release
End Sub

 

[eSolution]

Cool! I was just trying a code like this when you post it ) Lets see if is working!

 

[eSolution]

This is the "create table" query that I use:

query = "CREATE TABLE vid (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, IMG BLOB Not Null,TITLE TEXT Not Null,VIDID TEXT Not Null)"

And this is the error that I get now (using the insert from the above post):

android.database.sqlite.SQLiteException: table vid has 4 columns but 3 values were supplied (code 1): , while compiling: INSERT INTO vid VALUES( ?, ?, ?)
So it seems that it doesn't know how to create the ID and auto increment it

If I put in the insert 4 "?" but only 3 arguments I got this error

android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20)
So I think it tries to put the blob in to the ID column

Do u have any idea on how to write the insert query?

Thank you

 

[eurojam]

did you try to pass NULL for the autoink

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

EDIT: and now...Power out of my tablet and of my brain....Good night

 

[eSolution]

did you try to pass NULL for the autoink

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

I get this error when it tries to insert in to the database:

android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed (code 11)

hmmm... I got the same type of error if I create the table with only 3 columns, with no ID column and I execute an insert with 3 "?"

 

[Mahares]

I get this error when it tries to insert in to the database:

It should be like this:

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

 

[eSolution]

It should be like this:

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

the same error:
android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed (code 11)

 

[Mahares]

The statement I gave you is correct. It has to do with a problem somewhere else. Otherwise you would have gotten a syntax error if the statement is bad. But keep that statement I gave you handy. You may need it when you solve the other problem. I will leave it to eurojam to help you as I was not following the whole story and not too familiar with job download. He may ask you or you may want to do it, that is to zip and export your project to the forum. Sometimes, it is much easier to solve a problem if you have the full project in front of you instead scattered code snippet.

 

[eSolution]

The statement I gave you is correct. It has to do with a problem somewhere else. Otherwise you would have gotten a syntax error if the statement is bad. But keep that statement I gave you handy. You may need it when you solve the other problem. I will leave it to eurojam to help you as I was not following the whole story and not too familiar with job download. He may ask you or you may want to do it, that is to zip and export your project to the forum. Sometimes, it is much easier to solve a problem if you have the full project in front of you instead scattered code snippet.

All the other parts of the project work fine now after eurojam helped but the main problem now is the database ... it has to do with the creation of the table I think...
this is my code to create the table:

Sub CreateTable(SQL As SQL, TableName As String)
    SQL.BeginTransaction
    Try
        Dim query As String
        'query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, IMG BLOB (0) NOT NULL, TITLE TEXT (0) NOT NULL, VIDID STRING (0) NOT NULL)"
        'query = "CREATE TABLE IF NOT EXISTS [" & TableName & "] (IMG BLOB, TITLE TEXT, VIDID TEXT)"
        'query = "CREATE TABLE `vid` (`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `IMG` BLOB Not Null,`TITLE` TEXT Not Null,`VIDID` TEXT Not Null)"
        query = "CREATE TABLE vid (ID INTEGER PRIMARY KEY AUTOINCREMENT, IMG BLOB,TITLE TEXT,VIDID TEXT)"
       
        Log("CreateTable: " & query)
        SQL.ExecNonQuery(query)
    SQL.TransactionSuccessful
    Catch
        Log(LastException.Message)
    End Try
    SQL.EndTransaction
   
End Sub

I tried different statements but when I try to use your INSERT I get that error...