Android Question DBUTILS update record with image and WHERE clause

FormCommander

Member
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
 

DonManfred

Expert
Licensed 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

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

Star-Dust

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