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

Discussion in 'Android Questions' started by Arf, Mar 23, 2015.

  1. Arf

    Arf Active 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
     
  2. Arf

    Arf Active 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:
    Code:
    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:
    Code:
    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:
    Code:
    Global.thisExam.tests = DDB.GetExamTests(Global.selectedPatient, Global.thisExam.TimeOfExam)
    Code:
    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:
    Code:
    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.
     
  3. Arf

    Arf Active 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 :-(

    Code:
    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
     
  4. Arf

    Arf Active 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:
    [​IMG] Initialize2 (ArrayAsList)
    Initializes a list with the given values. This method should be used to convert arrays to lists.
    So I tried this:
    Code:
    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.
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Where are pkResult and Wave declared?
     
  6. Arf

    Arf Active Member Licensed User

    Hi Erel,
    They're both process globals
    Code:
    Dim pWave As Wave
        
    Dim Results As PktResults
    They get initialised like this in Activity Create
    Code:
    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
    Code:
    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
    Code:
    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.
     
  7. Arf

    Arf Active 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
    Code:
    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
     
  8. keirS

    keirS Well-Known Member Licensed User

    What is the structure of your DB?
     
  9. Arf

    Arf Active Member Licensed User

    It looks like this:
    Code:
    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,......
     
  10. keirS

    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?
     
  11. Arf

    Arf Active 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:
    Code:
    Type PktResults(Index(48As Short)
    Type Wave (point(1000As FlowVolTime)
    Type Test    (result As PktResults, waveform As Wave)
    Type Exam     (tests (8As 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.
     
  12. Arf

    Arf Active 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 :-(
     

    Attached Files:

  13. keirS

    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.
     
    Arf likes this.
  14. Arf

    Arf Active 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.
     
  15. keirS

    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.
     
  16. KMatle

    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...
     
    keirS likes this.
  17. Arf

    Arf Active 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.
     
  18. Arf

    Arf Active 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.
     
  19. Arf

    Arf Active Member Licensed User

    Just tried to store a list then retrieve it again, no joy, so I guess byte arrays are the way forward.
     
  20. Arf

    Arf Active 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!

    Code:
    Sub serializeTests(tsts As ListAs 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(8As Byte
            buf = bc.LongsToBytes(
    Array As Long(tst.TestTime))
            out.WriteBytes(buf,
    0,8)
            
    'TestTime done
          
            
    Dim buf(96As Byte
            buf = bc.ShortsToBytes(tst.result.Index)
            out.WriteBytes(buf,
    0,96)
            
    'Results done
          
            
    For cnt=0 To 999
                
    Dim buf(2As 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*6104As 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, 
    048832)
        
    Dim bc As ByteConverter
        
    Dim tsts As List
        tsts.Initialize

        
    For i=0 To numtests-1
            
    Dim tst As Test
            tst.Initialize
            
    Dim buf(8As Byte
            inp.ReadBytes(buf, 
    08)
            tst.TestTime = bc.LongsFromBytes(buf)(
    0)
            
    'TestTime done
          
            
    Dim buf(96As Byte
            inp.ReadBytes(buf, 
    096)
            tst.result.Index = bc.ShortsFromBytes(buf)
            
    'results done
          
            tst.waveform.Initialize
            
    For cnt=0 To 999
                
    Dim buf(2As Byte
                tst.waveform.point(cnt).Initialize
                inp.ReadBytes(buf, 
    02)
                tst.waveform.point(cnt).Flow = bc.ShortsFromBytes(buf)(
    0)
                inp.ReadBytes(buf, 
    02)
                tst.waveform.point(cnt).Vol = bc.ShortsFromBytes(buf)(
    0)
                inp.ReadBytes(buf, 
    02)
                tst.waveform.point(cnt).Time = bc.ShortsFromBytes(buf)(
    0)      
            
    Next
            
    'waveform done
      
            tsts.Add(tst)
        
    Next
      
        inp.Close
        
    Return tsts
    End Sub
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice