Android Question DBUTILS update record with image and WHERE clause

FormCommander

Member
Licensed User
Longtime User
Hello,
have successfully made my first steps with databases, works great, but now I stuck on a simple thing. I want to insert a image in a BLOB field. Found the way to do this, but cannot create the UPDATE statement, get always an SQL query error. How can I do this with DBUTILS or a SQL statement ?
regards
Rudolf

Sub Insert_rec (nPUserID As Int,cPOrderID As String ,cPText As String ,cPBarCode As String,cPObject As String,cPLong As String,cPLat As String ,cPDate As String,cPImage As String) As Boolean

Dim ListOfMaps As List
ListOfMaps.Initialize
Dim nID As Int
Dim dbmap As Map
dbmap.Initialize

dbmap.put("ORDER_ID",cPOrderID)
dbmap.put("USER_ID",nPUserID)
dbmap.put("TEXT",cPText)
dbmap.put("BARCODE",cPBarCode)
dbmap.put("LONG",cPLong)
dbmap.put("LAT",cPLat)
dbmap.put("DATETIME",cPDate)
dbmap.put("OBJECT",cPObject)
dbmap.put("IMAGE",cPImage)
dbmap.put("SYNC",0)

ListOfMaps.Add(dbmap)
DBUtils.InsertMaps(SQLobj,"photos",ListOfMaps)

Dim cCur As Cursor
cCur = SQLobj.ExecQuery("SELECT last_insert_rowid()")
cCur.Position = 0
nID = cCur.GetInt2(0)
Log("Last ID:" & nID)
ListOfMaps.Clear

InsertBlob(nID,cPhotoDir,cPhotoFile)

Return True
End Sub

Sub InsertBlob(nID As Int, cDir As String,cFile As String)
'convert the image file to a bytes array
Dim InputStream1 As InputStream
InputStream1 = File.OpenInput(cDir,cFile)
Dim OutputStream1 As OutputStream
OutputStream1.InitializeToBytesArray(1000)
File.Copy2(InputStream1, OutputStream1)
Dim Buffer() As Byte 'declares an empty array
Buffer = OutputStream1.ToBytesArray

SQLobj.ExecNonQuery2("UPDATE photos SET IMAGE (?) WHERE ID = " & nID, Array As Object(Buffer))



End Sub
 

DonManfred

Expert
Licensed User
Longtime User
1. Use code tags when posting code!

UPDATE photos SET IMAGE (?) WHERE ID = " & nID, Array As Object(Buffer))

2. Try it with
B4X:
SQLobj.ExecNonQuery2("UPDATE photos SET IMAGE =? WHERE ID = ?", Array As Object(Buffer,nID))
 
Upvote 0

FormCommander

Member
Licensed User
Longtime User
Hello DonManfred,
Thank you ! seems to work, no more error.
I did not find a way to tag code, no icon for this in the editor, how to do this ?
regards
Rudolf
 
Upvote 0

Star-Dust

Expert
Licensed User
Hello,
have successfully made my first steps with databases, works great, but now I stuck on a simple thing. I want to insert a image in a BLOB field. Found the way to do this, but cannot create the UPDATE statement, get always an SQL query error. How can I do this with DBUTILS or a SQL statement ?
regards
Rudolf

Sub Insert_rec (nPUserID As Int,cPOrderID As String ,cPText As String ,cPBarCode As String,cPObject As String,cPLong As String,cPLat As String ,cPDate As String,cPImage As String) As Boolean

Dim ListOfMaps As List
ListOfMaps.Initialize
Dim nID As Int
Dim dbmap As Map
dbmap.Initialize

dbmap.put("ORDER_ID",cPOrderID)
dbmap.put("USER_ID",nPUserID)
dbmap.put("TEXT",cPText)
dbmap.put("BARCODE",cPBarCode)
dbmap.put("LONG",cPLong)
dbmap.put("LAT",cPLat)
dbmap.put("DATETIME",cPDate)
dbmap.put("OBJECT",cPObject)
dbmap.put("IMAGE",cPImage)
dbmap.put("SYNC",0)

ListOfMaps.Add(dbmap)
DBUtils.InsertMaps(SQLobj,"photos",ListOfMaps)

Dim cCur As Cursor
cCur = SQLobj.ExecQuery("SELECT last_insert_rowid()")
cCur.Position = 0
nID = cCur.GetInt2(0)
Log("Last ID:" & nID)
ListOfMaps.Clear

InsertBlob(nID,cPhotoDir,cPhotoFile)

Return True
End Sub

Sub InsertBlob(nID As Int, cDir As String,cFile As String)
'convert the image file to a bytes array
Dim InputStream1 As InputStream
InputStream1 = File.OpenInput(cDir,cFile)
Dim OutputStream1 As OutputStream
OutputStream1.InitializeToBytesArray(1000)
File.Copy2(InputStream1, OutputStream1)
Dim Buffer() As Byte 'declares an empty array
Buffer = OutputStream1.ToBytesArray

SQLobj.ExecNonQuery2("UPDATE photos SET IMAGE (?) WHERE ID = " & nID, Array As Object(Buffer))



End Sub
See also this library:
https://www.b4x.com/android/forum/threads/sd-sqliteextra.90805/

Method ReadBitmap and UpdateBitmap
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
but Rudolf is a woman's name in Austria?
Only in the Evening when Rudolf transform itself into a Rudolfine :D
Sorry, couldn´t resist. Don´t take it too serious, Rudolf ;-)
 
Upvote 0

FormCommander

Member
Licensed User
Longtime User
Hello,
DonManfred: you are right, but I dont think that there is a Rudolfine here in Austria with 190 tall and 120 kilos ;-)
regards
Rudolf
 
Upvote 0

Star-Dust

Expert
Licensed User
Dimenticato(1).jpg
 
Upvote 0
Top