Android Question (-: SOLVED :-) Storing Lists or dynamically sized types in SQL DDB

Arf

Well-Known Member
Licensed User
I've been trying to store a list as a blob in a database, but when I retrieve it I can't use the data as a list anymore. OK so that was a bit ambitious now that I think about it.

I'd just like to double check that I am right on that before I completely change everything - There is no way I can save a list (or a map) within a SQL ddb and continue to use it as a list (or map) again after retrieving it, is that right?

Thanks
 

Arf

Well-Known Member
Licensed User
More info:
The data I am storing is a single exam per database entry. The exam comprises up to 8 tests, which I store as a list:
B4X:
Type Test    (result As PktResults, waveform As Wave)
Type Exam     (tests As List, TimeOfExam As Long)
When stashing it away in the database I do it in a few fields, like this:
B4X:
m.Put("ExamTime", pExam.TimeOfExam)
    m.Put("ExamPatient_ID", pPat.patID)
    m.Put("ExamData", pExam.tests)

    ListOfMaps.Add(m)
    DBUtils.InsertMaps(SQL, "Exams", ListOfMaps)
Then I recall the ExamTime and Patient_IDs to get a list of distinct exams, and select an exam and retrieve it like so:
B4X:
Global.thisExam.tests = DDB.GetExamTests(Global.selectedPatient, Global.thisExam.TimeOfExam)
B4X:
Sub GetExamTests(eP As PatientStruct, eT As Long) As List
    Dim List1 As List
    List1 = DBUtils.ExecuteMemoryTable(SQL,"SELECT ExamData FROM Exams WHERE ExamTime='"&eT&"' AND ExamPatient_ID='"&eP.patID&"'",Null,0)
    Return List1
End Sub
Then I try access the retrieved data like this:
B4X:
Dim thisTest As Test
        thisTest.Initialize
        thisTest = Global.thisExam.tests.Get(0)
and that's where it all goes wrong, on the last line.
java.lang.ClassCastException: java.lang.String[] cannot be cast to Spiro.Home.types$_test

So I was thinking that I should instead us a fixed number of tests as 8 per exam even though there usually would be less than 8 (8 is max), and then I would try to manually unpack the retrieved data into a list of 8 new tests.

I'm very new to SQL and still struggling with types, so really just looking for a nudge in the right direction.
 

Arf

Well-Known Member
Licensed User
I think I might be making progress, this is what I'm attempting. unfortunately I can't figure out how to get the inputstream into my type :-(

B4X:
Sub GetExamTests(eP As PatientStruct, eT As Long) As List
    Dim Cursor1 As Cursor
    Cursor1 = DBUtils.ExecuteMemoryTable(SQL,"SELECT ExamData FROM Exams WHERE ExamTime='"&eT&"' AND ExamPatient_ID='"&eP.patID&"'",Null,0)
    Dim Buffer() As Byte
    Cursor1.Position = 0
    Buffer = Cursor1.GetBlob("ExamData")
    Dim inputstream1 As InputStream   
    Dim i As Byte
    Dim TestSize As Int
    TestSize = Buffer.Length/8
    For i=0 To 7
        Dim thisTest As Test
        inputstream1.InitializeFromBytesArray(Buffer, i*TestSize, TestSize)
   
        'now if only I could figure out how to get the inputstream packed into Global.thisExam.tests(i)
       
    Next
 

Arf

Well-Known Member
Licensed User
I got nowhere with that, so reverted back to trying to use tests as a List and pupulate it another way, after reading this:
Initialize2 (ArrayAsList)

Initializes a list with the given values. This method should be used to convert arrays to lists.
So I tried this:
B4X:
Sub GetExamTests(eP As PatientStruct, eT As Long) As List
    Dim Cursor1 As Cursor
    Cursor1 = SQL.ExecQuery2("SELECT ExamData FROM Exams WHERE ExamTime='"&eT&"' AND ExamPatient_ID='"&eP.patID&"'", Null)
    Dim Buffer() As Byte
    Cursor1.Position = 0
    Buffer = Cursor1.GetBlob("ExamData")
    Dim inputstream1 As InputStream   
    inputstream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
    Global.thisExam.tests.Initialize2(inputstream1)
    inputstream1.Close
End Sub
Which seemed quite promising, but I get an error on the "Global.thisExam.tests.Initialize2(inputstream1)" line:
java.lang.ClassCastException: java.io.ByteArrayInputStream cannot be cast to java.util.List

Someone please tell me if I am massively overthinking this.
 

Arf

Well-Known Member
Licensed User
Hi Erel,
They're both process globals
B4X:
Dim pWave As Wave
    Dim Results As PktResults
They get initialised like this in Activity Create
B4X:
If pWave.IsInitialized = False Then
        pWave.Initialize
    End If
   
    If Results.IsInitialized = False  Then
        Results.Initialize
    End If
pWave is a set size of 1000, before a test starts I pack it with zeroes like this
B4X:
For FvtRef = 0 To 999
        pWave.point(FvtRef).Vol = 0
        pWave.point(FvtRef).Flow = 0
        pWave.point(FvtRef).Time = 0
    Next
Then as samples come in they overwrite from zero onwards, so I expect when I save the structure the waveform bit should always be a fixed size
They get overwritten like this
B4X:
pWave.point(FvtRef).Vol = Unit_Comms.Vol
    pWave.point(FvtRef).Flow = Unit_Comms.Flow
    pWave.point(FvtRef).Time = Unit_Comms.Time
    FvtRef = FvtRef + 1
I tried to change the exam to contain a fixed size of 8 exam types rather than a list of however many tests there were, and when I read the blob back I am getting a smaller amount of data than what I expected, I had to stop after 8 hrs on the same problem last night but am trying to look with fresh eyes now.
 

Arf

Well-Known Member
Licensed User
I can see that if I do one test in an exam, the waveform values for that test are all there, and all 1000 points are initialised.
All data points for the rest of the 7 tests are there as zeroes too, but the individual points are Unintialised.
Would this be of any concern when I try store the whole block of data in the database?
I am not too sure what I need to be initialising.. during last night's final lost hours I ended up with the code below to try init my types
B4X:
    If Global.thisExam.IsInitialized = False Then
        Global.thisExam.Initialize
        Global.thisExam.tests.Initialize
        For m=0 To 7
            Dim blankTest As Test
            blankTest.Initialize
            blankTest.result.Initialize
            blankTest.waveform.Initialize           
            For u=0 To 999
                blankTest.waveform.point(u).Flow = 0
                blankTest.waveform.point(u).Vol = 0
                blankTest.waveform.point(u).Time = 0
            Next
            Global.thisExam.tests.Add(blankTest)
        Next
    End If       
   
    If testref = 0 Then Global.thisExam.TimeOfExam = DateTime.Now
    Dim thisTest As Test
    thisTest.Initialize
    thisTest.result.Initialize
    thisTest.waveform.Initialize
    For u=0 To 999
        thisTest.waveform.point(u).Flow = 0
        thisTest.waveform.point(u).Vol = 0
        thisTest.waveform.point(u).Time = 0
    Next
 

Arf

Well-Known Member
Licensed User
It looks like this:
B4X:
    Dim m As Map
    m.Initialize 'clear the map
    m.Put("Patient_No", DBUtils.DB_TEXT)
    m.Put("First_Name", DBUtils.DB_TEXT)
    m.Put("Last_Name", DBUtils.DB_TEXT)
    m.Put("Date_of_Birth", DBUtils.DB_INTEGER)
    m.Put("Sex", DBUtils.DB_TEXT)
    m.Put("Patient_ID", DBUtils.DB_INTEGER)
    DBUtils.CreateTable(sq, "Patients", m, "") 'no primary key assigned
   
    m.Initialize 'clear the map
    m.Put("ExamTime", DBUtils.DB_INTEGER)
    m.Put("ExamPatient_ID", DBUtils.DB_INTEGER)
    m.Put("ExamData", DBUtils.DB_BLOB)
    DBUtils.CreateTable(sq, "Exams", m, "") 'no primary key assigned
    DBUtils.SetDBVersion(sq, 2)
So one table of Patients, each patient has a patient ID field amongst the fields. Then an Exams table, which also contains a patient ID so that I can track which exam belongs to which patient.
The database is working fine mostly, its just dealing with the ExamData blobs that is torturing me. Making slow progress now that I got the rapid debugger going with USB debug. I ca see that if I extract a Map of ExamData that there is stuff in there, just looking at whether its the expected size and working out how to dcode it back into my Type so I can work with it.

This is what the watch window tells me is in the Map after extraction from the DDB:
[[waveform=[point=[LSpiroConnect.Home.types$_flowvoltime;@220af330, IsInitialized=true], result=[Index=[S@22198d50, IsInitialized=true], IsInitialized=true
], [waveform=[point=[LSpiroConnect.Home.types$_flowvoltime;@22071fd8, IsInitialized=true], result=[Index=[S@22071f98, IsInitialized=true], IsInitialized=true
], [waveform=[point=[LSpiroConnect.Home.types$_flowvoltime;@22041eb0, IsInitialized=true], result=[Index=[S@22041e70, IsInitialized=true], IsInitialized=true
], [waveform=[point=[LSpiroConnect.Home.types$_flowvoltime;@22046580, IsInitialized=true], result=[Index=[S@22046540, IsInitialized=true], IsInitialized=true
], [waveform=[point=[LSpiroConnect.Home.types$_flowvoltime;@2204ac50,......
 

keirS

Well-Known Member
Licensed User
Why are you trying to store the test data in a BLOB? Why not have another table to store test data?
 

Arf

Well-Known Member
Licensed User
Well all the exam data is stored in Types after a test is done, some the main Type contains sub types, like this:
B4X:
Type PktResults(Index(48) As Short)
Type Wave (point(1000) As FlowVolTime)
Type Test    (result As PktResults, waveform As Wave)
Type Exam     (tests (8) As Test, TimeOfExam As Long)
I thought if all the data is in that Type that I can use, it would be best if I can storethe whole Type as a blob, recall it as a chunk od data from the database and shove the data back into my Type so that I can access it again in the way I usually do, like: pushupResult = exam.tests(5).PktResults.Index(24)

I just can't for the life of me work out how to put the chunk of data I get out the DDB back into a Type.
 

Arf

Well-Known Member
Licensed User
I've made a small example project and attached it, there's a breakpoint and comment at the part I can't understand.

I know that I can can store these fields in a DDB with more fields, thats not what I'm trying to solve though - what I need to figure out is how I can cast the data or somehow shift it from the map into a type. Perhaps there is better SQL method to use to get data into a buffer or something rather than a map, I don't know.


If anyone can guide me back onto the right path I would be forever grateful, I've been trying to get past this for 13 hours now :-(
 

Attachments

keirS

Well-Known Member
Licensed User
Your problem is you have tried something which appears to work; but infect isn't supported AFAIK. What appears to be happening is that when you store the type data into a BLOB something is serializing the type data. Serializing is the process of turning an object in to stream of bytes so you can store it in a file or DB. B4A types are represented as classes in Java. Whilst Java supports serialization, the class generated to represent a B4A type doesn't support it.

Perhaps Erel can clarify what is going on because I wouldn't have expected storing an instance of a type to a Blob to work in the first place. The major issue you have come is there does not appear to be a way of de-serializing the BLOB back into a type.
 
  • Like
Reactions: Arf

Arf

Well-Known Member
Licensed User
OK. so, if I were to convert my Type into a big byte array and store that, then retrieve and re-assemble the byte array into the type I guess that is the only way forward? I'll try that instead.
Thanks, your help is massively appreciated.
 

keirS

Well-Known Member
Licensed User
That's one way of doing it. If I wanted to store that type of data I would create my own B4A classes instead of using Types; and then write methods in the class (subs) to store and retrieve the data from a Blob.
 

KMatle

Expert
Licensed User
What about building a JSON string with the types and store it into a longchar (without conversation). Did not try this with binary data. With it you can retrieve the string in one unit and "get" the types back... Just an idea...
 

Arf

Well-Known Member
Licensed User
thanks - I'll have a look, currently I have no idea at all what JSON is!
I'm just busy making a class for the simple example app to check that I can get it working.. just need to get this prototype unit working and sent off then will have some time to consider more more elegant solutions.
 

Arf

Well-Known Member
Licensed User
Oh my word, I actually got it working. My example app, not the main one (yet!).
So what I did was created a class, the class stores all the different members as byte arrays or primitive types.
I have a function which copies all class members into a single byte array and save that as a blob.
Then I recall that and copy it all out to primitives again. Works!

So I'll proceed along those lines. I have learnt that I can't save a Type or a Class instance in a blob and expect to be able to retrieve it. What I am still unsure of is whether I can store Lists of primitives and recall them and access them.
 

Arf

Well-Known Member
Licensed User
Just tried to store a list then retrieve it again, no joy, so I guess byte arrays are the way forward.
 

Arf

Well-Known Member
Licensed User
All working now on the main app. I abandoned trying to store lists etc but kept using the Types, and unpack the types into a byte array before sending them off to a blob, then after getting them back I reassembled them. I thought that would be hard but actually it worked first time! And its rare that things work before the 30 or 40th time for me. InputStream and OutputStream made short work of it, code below if you're interested. Will still convert the Types to classes when I have time.
Thanks for the input all who helped!

B4X:
Sub serializeTests(tsts As List) As Byte()
    Dim bc As ByteConverter
    Dim out As OutputStream
    Dim tst As Test
    out.InitializeToBytesArray(100)
  
    'now iterate through the tests
    For i = 0 To tsts.Size - 1
        tst = tsts.Get(i)
        Dim buf(8) As Byte
        buf = bc.LongsToBytes(Array As Long(tst.TestTime))
        out.WriteBytes(buf,0,8)
        'TestTime done
      
        Dim buf(96) As Byte
        buf = bc.ShortsToBytes(tst.result.Index)
        out.WriteBytes(buf,0,96)
        'Results done
      
        For cnt=0 To 999
            Dim buf(2) As Byte
            buf = bc.ShortsToBytes(Array As Short(tst.waveform.point(cnt).Flow))
            out.WriteBytes(buf,0,2)
            buf = bc.ShortsToBytes(Array As Short(tst.waveform.point(cnt).Vol))
            out.WriteBytes(buf,0,2)
            buf = bc.ShortsToBytes(Array As Short(tst.waveform.point(cnt).Time))
            out.WriteBytes(buf,0,2)
        Next
        'waveform done
    Next
  
    Dim outbuf(tsts.Size*6104) As Byte
    outbuf = out.ToBytesArray
    out.Close
    Return outbuf
End Sub

Sub deserializeBlob(blob() As Byte) As List
    Dim inp As InputStream  
    Dim numtests As Int
    numtests = blob.Length/6104
    inp.InitializeFromBytesArray(blob, 0, 48832)
    Dim bc As ByteConverter
    Dim tsts As List
    tsts.Initialize

    For i=0 To numtests-1
        Dim tst As Test
        tst.Initialize
        Dim buf(8) As Byte
        inp.ReadBytes(buf, 0, 8)
        tst.TestTime = bc.LongsFromBytes(buf)(0)
        'TestTime done
      
        Dim buf(96) As Byte
        inp.ReadBytes(buf, 0, 96)
        tst.result.Index = bc.ShortsFromBytes(buf)
        'results done
      
        tst.waveform.Initialize
        For cnt=0 To 999
            Dim buf(2) As Byte
            tst.waveform.point(cnt).Initialize
            inp.ReadBytes(buf, 0, 2)
            tst.waveform.point(cnt).Flow = bc.ShortsFromBytes(buf)(0)
            inp.ReadBytes(buf, 0, 2)
            tst.waveform.point(cnt).Vol = bc.ShortsFromBytes(buf)(0)
            inp.ReadBytes(buf, 0, 2)
            tst.waveform.point(cnt).Time = bc.ShortsFromBytes(buf)(0)      
        Next
        'waveform done
  
        tsts.Add(tst)
    Next
  
    inp.Close
    Return tsts
End Sub
 
Top