How to save UDT to SQLite?

[Pappsegull]

Is it possible to save this UDT to SQLite? If YES how?:sign0085:

If NO, is there any other way to save it?

   Type MenuItems(ID, ParentID, SubID() As Int, Text As String, Icon As Bitmap)
   Dim mnu() As MenuItems

Like in VB6

Save:
   Open Filename$ For Binary As #X%: Put #X%, 1, MyUDT: Close #X%

Dump the file back into the UDT:
   Open Filename$ For Binary As #X%: Get #X%, 1, MyUDT: Close #X%

 

[Erel]

Do you want to save it as a BLOB?
You can use RandomAccessFile to convert the type to a bytes array.

Sub Process_Globals
   Dim raf As RandomAccessFile
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
      raf.Initialize(File.DirInternalCache, "temp.dat", False)
   End If
End Sub

Sub ConvertTypeToByte(o As Object) As Byte()
   raf.WriteObject(o, True, 0)
   Dim data(raf.CurrentPosition) As Byte
   raf.ReadBytes(data, 0, data.Length, 0)
   Return data
End Sub

 

[Pappsegull]

Error when try to insert the BLOB

Thanks Erel

But I get errors when try to insert the BLOB Any idea?

Dim  b() As Byte
      DB.ExecNonQuery("DROP TABLE IF EXISTS UDT")
      DB.ExecNonQuery("CREATE TABLE UDT(BlobUDT BLOB)")
      
      'No error execute this line:
         DB.ExecNonQuery2("INSERT INTO UDT VALUES('BlobUDT')", b)
      b = (UDT2Byte(UDT))
      'ERROR: android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x21fb18
         DB.ExecNonQuery2("INSERT INTO UDT VALUES('BlobUDT')", b)
      'ERROR: android.database.sqlite.SQLiteException: bind or column index out of range: statement 0x214488
         DB.ExecNonQuery2("INSERT INTO UDT VALUES('BlobUDT')", Array As Object(b))

Sub UDT2Byte(o As Object) As Byte()
   RAF.WriteObject(o, False, 0)
   Dim b(RAF.CurrentPosition) As Byte
   RAF.ReadBytes(b, 0, b.Length, 0): Return(b)
End Sub

I have attached my test project, if you are able to check it.

Thanks!

 

[Erel]

Your query is wrong. See the blobs section here: http://www.b4x.com/forum/basic4android-getting-started-tutorials/6736-sql-tutorial.html#post39108

 

[Pappsegull]

How to dump the byte array back to my UDT?

Thanks again Erel

No more problem to save the UDT:icon_clap:, but no I have a 2nd problem.

When I want to dump it back to my UDT! How?

      C = DB.ExecQuery("SELECT BlobUDT FROM UDT"): C.Position = 0
      bA = C.GetBlob("BlobUDT")
      I.InitializeFromBytesArray(bA, 0, bA.Length)
      
      'How to dump the byte array back to my UDT?
      'I have try this
      Dim Obj As Object: Obj = I 
      UDT = Obj 'Return error: java.lang.ClassCastException: java.io.ByteArrayInputStream

...but I'm not reached our goal yet:BangHead:

I did find a way to dump it back, the text and boolean propertys is OK but the Bitmap is Null

C = DB.ExecQuery("SELECT * FROM UDT"): C.Position = 0
bA = C.GetBlob("BlobUDT")
I.InitializeFromBytesArray(bA, 0, bA.Length)
UDT = RAF.ReadObject(0)

Why? Is there another way to do it?

Thanks in advance!

 

[Pappsegull]

Updated test project

This is the differens when saved and read:


1/5***************
BEFORE => ID = 0, Selected = true, Name = Record 0, BMP = (Bitmap): 36 x 36
AFTER => ID = 0, Selected = true, Name = Record 0, BMP = null
2/5***************
BEFORE => ID = 1, Selected = true, Name = Record 1, BMP = (Bitmap): 36 x 36
AFTER => ID = 1, Selected = true, Name = Record 1, BMP = null
3/5***************
BEFORE => ID = 2, Selected = true, Name = Record 2, BMP = (Bitmap): 36 x 36
AFTER => ID = 2, Selected = true, Name = Record 2, BMP = null
4/5***************
BEFORE => ID = 3, Selected = true, Name = Record 3, BMP = (Bitmap): 36 x 36
AFTER => ID = 3, Selected = true, Name = Record 3, BMP = null
5/5***************
BEFORE => ID = 4, Selected = true, Name = Record 4, BMP = (Bitmap): 36 x 36
AFTER => ID = 4, Selected = true, Name = Record 4, BMP = null

PS Attached code updated

Time to sleeeep now....ZZZZZZZZZZZZZZZzzzzzzzzzzzzzz:sign0136:

 

[Erel]

My mistake. WriteObject can only handle B4A custom types, primitive types and Java Serializable types. Bitmap is not such a type.
Therefore you need to manually convert it to bytes.
The following code seems to work (I haven't tested it enough):

Sub UDT2Byte(o() As TestUDT) As Byte()
   For i = 0 To o.Length - 1
      Dim out As OutputStream
      out.InitializeToBytesArray(0)
      o(i).Icon.WriteToStream(out, 100, "PNG")
      o(i).ImageBlob = out.ToBytesArray
      out.Close
   Next
   RAF.WriteObject(o, False, 0)
   Dim bUDT(RAF.CurrentPosition) As Byte
   log2("RAF.CurrentPosition: " & RAF.CurrentPosition, False)
   RAF.ReadBytes(bUDT, 0, bUDT.Length, 0)
   Log(bUDT.Length)
   Return(bUDT)
   
End Sub

'// Get all menu data from table UDT, field DBSetting as BLOB
Sub DBReadUDT() As Boolean
Dim s As String, C As Cursor, bA() As Byte, I As InputStream, UD() As TestUDT
   Log2("", True): s = " DBReadUDT() "
   Try
      C = DB.ExecQuery("SELECT * FROM UDT"): C.Position = 0: bA = C.GetBlob("BlobUDT")
      I.InitializeFromBytesArray(bA, 0, bA.Length): UD = RAF.ReadObject(0): I.Close
      Log2("bA.Length = " & (bA.Length), False): LW.Clear  
      For x = 0 To nRs - 1 
         Dim in As InputStream
         in.InitializeFromBytesArray(UD(x).ImageBlob, 0, UD(x).ImageBlob.Length)
         Dim b As Bitmap
         b.Initialize2(in)
         UD(x).Icon = b
         in.Close
         Log((x + 1) & "/" &(nRs) & "***************")
         Log("BEFORE => ID = " & UDT(x).ID & ", Selected = " & _
           UDT(x).Selected & ", Name = " & UDT(x).Name & ", BMP = " & UDT(x).Icon)
         Log("AFTER  => ID = " & UD(x).ID & ", Selected = " & _
           UD(x).Selected & ", Name = " & UD(x).Name & ", BMP = " & UD(x).Icon)
         LW.AddTwoLinesAndBitmap(UD(x).Name, "Bitmap = " & _
            UD(x).Icon, UDT(x).Icon) 'I miss AddSingleLineAndBitmap ;-)
      Next
   Catch
      s = "ERROR" & s &  LastException.Message: Log2(s, False)
      Msgbox2(s, AppName, "", "OK", "", ICO): Return
   End Try   
   Log2("READ" & s, False): Return(True)
End Sub

I changed the type declaration to:

Type TestUDT (ID As Int, Name As String, Icon As Bitmap, Selected As Boolean, ImageBlob() As Byte)

 

[Pappsegull]

Thanks

Thanks Erel, you're a rock:sign0060:

This is just one example, I have several UDTs and some have underlying arrays.

I will try your suggestion and compare the time to save and read from this method of doing it by creating a table of all entries. I will return when I have time for this. And post the code here.

Right now I'm buzy with another project with deadline.

Thanks again!