Android Question Inserting Image into SQL BLOB with many fields

Kintara

Member
Licensed User
Longtime User
I have a SQL database with 255 fields. One of the fields is a BLOB type, and has the label “Sketch”. In this field I want to save a picture from “File.DirDefaultExternal” called "Temp1.jpg"
At the moment I am populating the fields from an array “Main.Arg” which has 255 elements. Main.Arg(76) corresponds to the position of the BLOB field.
I thought I could put the image data into Main.Arg(76) and then put the whole Main.Arg array into the SQL record.
I tried to put the image in to a SQL record with the following:-
'This Bit seems very standard as used in several examples
Dim InputStream1 AsInputStream
InputStream1=File.OpenInput(File.DirAssets,"Temp1.jpg")
Dim OutputStream1 AsOutputStream
OutputStream1.InitializeToBytesArray(50000)
File.Copy2(InputStream1,OutputStream1)
Dim Buffer() AsByte
Buffer=OutputStream1.ToBytesArray
InputStream1.Close
OutputStream1.Close
'Putting the image into Main.Arg.(76)
Main.Arg.Set(76,ArrayAsObject (Buffer))
Main.SQLOffice.BeginTransaction
'Main.Fields Is a String of value “?,?,?,?,? etc repeated 255 times
Main.SQLOffice.ExecNonQuery2("INSERT OR REPLACE INTO Records Values(" & Main.Fields & ")",Main.Arg)
Main.SQLOffice.TransactionSuccessful
Main.SQLOffice.EndTransaction

This does not work. It creates the record and all the fields are populted, but the image in "Sketch" cannot be extracted.
Is there a way to just insert the Image into the BLOB field after setting the other fields?
i.e. not doing this bit of code :-

'Putting the image into Main.Arg.(76)
Main.Arg.Set(76,ArrayAsObject (Buffer))

…then doing the equivalent of this pseudocode:-
Main.SQLOffice.ExecNonQuery("UPDATE Records SET Sketch =“ & ArrayAsObject (Buffer)) & "WHERE Record_ID = " & RecordID)
 

Mahares

Expert
Licensed User
Longtime User
You can update the record' s BLOB field with something like this:
B4X:
Main.SQLOffice.ExecNonQuery2("UPDATE Records SET Sketch = ? WHERE Record_ID = ?",Array As Object (Buffer,RecordID))
 
Upvote 0

Kintara

Member
Licensed User
Longtime User
Thanks, I'll give that a try.
I do tend to hit a blank on how to do some of these things, especally when it comes to arrays and objects etc.

I gave it a try and it works a treat!
Many thanks, I also understand a bit more about the use of ? and Array as Object.
 
Last edited:
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…