Android Question SqLite problem inserting blob (picture)

[Philippe Huysmans]

I am trying to insert a bitmap in a blob field, with no luck until now...

Tried

info : Type TPhoneContact (DisplayName As String, Phone As String, BmPicture As Bitmap)

Sub ImportContactToSureSms(Ct As TPhoneContact) As Boolean
Dim Om As OutputStream
Dim B() As Byte
Dim SQL As String
Om.InitializeToBytesArray(0)
Ct.BmPicture.WriteToStream(Om,100,"PNG")
B=Om.ToBytesArray
SQL="INSERT INTO Users(Key,DisplayName,Phone,Picture) VALUES("
SQL=SQL & "''," 'Key
SQL=SQL & "'" & Pure(Ct.DisplayName) & "'," 'DisplayNAme
SQL=SQL & "'" & Pure(Ct.Phone) & "'," 'Phone
SQL=SQL & Array As Object(B) & ")" 'Picture
Msgbox(SQL,"")
SqlDb.ExecNonQuery(SQL)
End Sub

But this throws an exception

android.database.sqlite.SQLiteException: unrecognized token: "[Ljava.lang.Object;@211c3a38)" (code 1): , while compiling: INSERT INTO Users(Key,DisplayName,Phone,Picture) VALUES('','Charlotte hername','[PhoneType=mobile, Number=012121212, IsInitialized=true

What am I doing wrong ?

 

[JohnD]

Click ~> Here
See LucasMs PhotoBlob.zip post

 

[Philippe Huysmans]

Dear John,

The case seems to be slightly different. Here, I already have a Bitmap. Note that I have also tried with an array of byte()

The problem apparently comes from : Array As Object(B)

What is exactly the meaning of this in the query ? b being an array of bytes containing a serialized bitmap.

Note that all is right if I do not insert the blob...

I try to figure, but I confess that I am a bit lost :

I converted the bitmap into byte() using :

Public Sub BitmapToBytes(Bm As Bitmap) As Byte()
If Bm.IsInitialized Then
Dim Om As OutputStream
Om.InitializeToBytesArray(1024)
Bm.WriteToStream(Om,100,"PNG")
Om.Close
Return(Om.ToBytesArray)
End If ' Bm.IsInitialized
End Sub

 

[LucaMs]

INSERT INTO Users(Key,DisplayName,Phone,Picture) VALUES('','Charlotte hername','[PhoneType=mobile, Number=012121212, IsInitialized=true

[B]'[PhoneType=mobile[/B] apice?

I suggest you add Log(SQL).

For convenience and clarity, you could use a function that adds apices:

Sub AddApices(FieldStringValue As String) As String
    Return "'" & FieldStringValue & "'"
End Sub

Also, you could use the function InsertMaps of the DBUtils module


However, I have not yet tried to insert images into a SQLite db.
Now I try.

(a lot of work on this site but little pay. I'm kidding: no pay at all . But we have the pleasure to help each other, also learning)

 

[Philippe Huysmans]

Thanks for response, Lucas, even if I do not see exactly the point.

My Sql, say is :

INSERT INTO Users(Key,DisplayName,Phone, Picture) VALUES('MYKEY','Charlotte','1234567',[Ljava.lang.Object;@21144af8)

Single quotes are present, except for the blob, afaik there must be no quotes there.

The error raised is :

android.database.sqlite.SQLiteException: unrecognized token: "[Ljava.lang.Object;@21144af8)" (code 1): , while compiling: INSERT INTO Users(Key,DisplayName,Phone, Picture) VALUES('MYKEY','Charlotte','1234567',[Ljava.lang.Object;@21144af8)

I am puzzled.

TIA,


Philippe

 

[LucaMs]

#4 modified

 

[LucaMs]

I've attached a sample project.

(it would be better to use a class for each table, instead of a Type)

 

[Philippe Huysmans]

Hello Lucas,

First of all, thanks a lot for helping me, even if the given example is more an hydra than a single blob insertion example. Do not take it bad, but I use only code that I fully understand, this way I learn to do it by myself, going from simple to more complex. Here, I had to understand WHY my code was not working.

Beside this, I did not find any convincing example of such an insertion in the whole forum, for the very reason that it is (seems to me) impossible, at least this way.

Let me explain :

When I do "Sql="INSERT INTO ... VALUES('aaaa','bbb','ccc', OBJECT), the compiler tries to convert it in... string : whatever you may try...

Even if you declare a var as object and pass it to the query, the damn thing will try to convert it in string. So we got a query with the ID of the object... as string. Not funny.

The only way is to use SqlDb.ExecNonQuery2 : and this way, the example becomes really simple.

SQL="INSERT INTO [Users]([Key],[DisplayName],[Phone],[Picture]) VALUES('KEY','DISPLAYNAME','PHONE',?)"

SqlDb.ExecNonQuery2(SQL,Array As Object(Ct.Picture))

(where ct.picture is an array of byte)

This one works.

Again, thank you for your patience and kindness,

Ciao,

ps : was that YOU on the photo ?

 

[LucaMs]

You're welcome.

No, that's not me: when I was a child, I was much more irascible