Android Question SQL - replacing a BLOB

[Arf]

Hi,
I have a load of blobs in my database that store test data. I've recently changed the format of the data stored and need to convert the existing blobs to the new format. I have managed to retrieve all of the blobs into a Cursor, it looks like this (simplified):

Sub ConvertV4toV5exams()
    Dim Cursor1 As Cursor
    'Cursor1 = SQL.ExecQuery("SELECT ExamData FROM Exams WHERE ExamTime='"&eT&"' AND ExamPatient_ID='"&eP.patID&"'")
    'let's just get all examdata in one go instead
    Cursor1 = SQL.ExecQuery("SELECT ExamData FROM Exams")
 
    For tstcnt = 0 To Cursor1.RowCount-1
        Dim Buffer() As Byte
        Cursor1.Position = tstcnt
        Buffer = Cursor1.GetBlob("ExamData")

        Dim newtstlist As List
        newtstlist.Initialize

        newtstlist = ConvertV4blobtoV5(Buffer)

       'now serialise the data
       Dim raf As RandomAccessFile
       File.Delete(tempFolder, tempFile)
       raf.Initialize(tempFolder, tempFile, False)
       raf.WriteObject(newtstlist, True, raf.CurrentPosition)
       raf.Flush
       Dim buffer(raf.CurrentPosition) As Byte
       raf.ReadBytes(buffer, 0, buffer.Length, 0)
       raf.Close
        'replace the data stored in the current blob, with this data in 'buffer'
        'not sure how...

    Next
End Sub

I am new to SQL so not sure what the neatest way to do this would be, or if I can update each blob as I go along at the comment "not sure how..". Is there a statement I can use to do so?

I don't currently have provision in the project for updating blob data, once an exam is stored it just stays there. I use The current method I use for putting these blobs of examdata into the DDB is below, just in case it has any impact on the question above:

Sub AddExam (pExam As Exam, pPat As PatientStruct)
    Dim ListOfMaps As List
    ListOfMaps.Initialize
    Dim m As Map
    m.Initialize
 
    m.Put("ExamTime", pExam.TimeOfExam)
    m.Put("ExamPatient_ID", pPat.patID)
    m.Put("ExamTestNum", pExam.NumberOfTests)
    m.Put("ExamUploadedStatus", pExam.UploadedStatus)
 
    Dim raf As RandomAccessFile
    File.Delete(tempFolder, tempFile)
    raf.Initialize(tempFolder, tempFile, False)
    raf.WriteObject(pExam.tests, True, raf.CurrentPosition)
    raf.Flush
    Dim buffer(raf.CurrentPosition) As Byte
    raf.ReadBytes(buffer, 0, buffer.Length, 0)
    raf.Close
    m.Put("ExamData", buffer)
 
    ListOfMaps.Add(m)
    DBUtils.InsertMaps(SQL, "Exams", ListOfMaps)
End Sub

thanks

 

[Erel]

If there is a primary key in the table then you can use an update command to update the blob field: http://www.tutorialspoint.com/sqlite/sqlite_update_query.htm

 

[Arf]

OK thanks.
So, if I change the conversion function as follows, does this sound right?
First, get examTime along with the blob data, so I can use that to access specific blobs later

Sub ConvertV4toV5exams()
    Dim Cursor1 As Cursor
    'Cursor1 = SQL.ExecQuery("SELECT ExamData FROM Exams WHERE ExamTime='"&eT&"' AND ExamPatient_ID='"&eP.patID&"'")
    Cursor1 = SQL.ExecQuery("SELECT ExamData,ExamTime FROM Exams")

Then at the point where I've got the blob into the new format and stored in 'Buffer', update the blob like this:

'in order to find the blob we are currently updating, we need to know the exam time
        Dim examtime As Long = Cursor1.GetInt("ExamTime")
        SQL.ExecQuery("UPDATE Exams SET ExamData='&Buffer&' WHERE ExamTime='"&examtime&"'")

Hopefully that should do it. I am not 100% sure that the '&Buffer&' part is correct though, will that pass the buffer correctly? Up to this point I have only being passing strings, not objects.

 

[Mahares]

Since Buffer is an array, I think the statement should be:

SQL.ExecQuery2("UPDATE Exams SET ExamData=? WHERE ExamTime=" & examtime , Buffer)

 

[Arf]

Thank you, Mahares.

 

[Arf]

I get an error,
Cannot cast type: {Type=Byte,Rank=1, RemoteObject=True} to: {Type=String,Rank=1, RemoteObject=True}
on that line now.


Where can I read more about all these '""' and & and formatting of these exec calls? I need to understand them better.
Are they specific to the B4A DDB module, or general SQL?

 

[Arf]

Ok, so if I change it to the following:

SQL.ExecNonQuery2("UPDATE Exams SET ExamData=? WHERE ExamTime='"&examtime&"'", Array As Object(Buffer))

it compiles and runs, but when I retrieve it when I bring up an exam, I get an error:
java.io.StreamCorruptedException

So something is still not right with my approach.

 

[Arf]

Well actually the new blob might be updating over the old, the error I am getting is when I try de-serialise the blob using raf:
Error occurred on line: 228 (DDB)
java.io.StreamCorruptedException
at java.ibjectInputStream.readStreamHeader(ObjectInputStream.java:2068)
at java.ibjectInputStream.<init>(ObjectInputStream.java:371)
at anywheresoftware.b4a.randomaccessfile.RandomAccessFile.readHelper(RandomAccessFile.java:432)
at anywheresoftware.b4a.randomaccessfile.RandomAccessFile.ReadObject(RandomAccessFile.java:395)

 

[Arf]

Solved, the examData wasn't being updated because:
Dim examtime As Long = Cursor1.GetInt("ExamTime")

should have been
Dim examtime As Long = Cursor1.GetLong("ExamTime")