I am storing SQL statements in a SQLite database and these in these statements there are some other non-SQL actions as well, such as stating the produced data types. Currently I have these actions in the comment of the SQL, but this has some drawbacks, so I am changing this to storing these actions separate as a Type. So, for that purpose I wrote a Sub that does this change. It serializes the type, so I can store it in SQLite as a blob.
There need to be a reference to B4XCollections.
All seems to be working well, except when transforming the byte array back to the type, not
all the type members show up. Strangely, if I set the type members directly (as shown in the
testing code) all works fine.
I had a look at all the bytes of the type members in case there was something funny going on
with null bytes, but nothing like that. Also the bytes of the byte arrays produced by the real code
are just the same as the bytes after setting the type members directly.
This seems baffling to me and maybe somebody can shed some light on this.
RBS
There need to be a reference to B4XCollections.
B4X:
Sub Process_Globals()
Private ser As B4XSerializator
Type tSQL_Extra(strData_Types As String, _
strChart_Title As String, _
strChartLine_Names As String, _
strSQLForDate_Line1 As String, _
strSQLForDate_Line2 As String, _
bRun_Chart As Boolean)
End Sub
Sub TestSerialize()
Dim strSQL As String
Dim strInsert As String
Dim RS1 As ResultSet
Dim iStartPos As Int
Dim str As String
Dim arrBytes() As Byte
Dim iPosDataTypes As Int
Dim iPosChartTitle As Int
Dim iPosRunChart As Int
Dim iPosChartLineNames As Int
Dim iPosDateLine1 As Int
Dim iPosDateLine2 As Int
Dim iKey As Int
Dim iEndPos As Int
strSQL = "drop table if exists TestX"
SQL1.ExecNonQuery (strSQL)
strSQL = "create table TestX(SQL TEXT)"
SQL1.ExecNonQuery (strSQL)
strInsert = $"select id, numeric_value from num_values /*>>>>I,R<<<<Serum vitamin B12{{{{}}}}B12*/"$
strSQL = "insert into TestX values(?)"
SQL1.ExecNonQuery2(strSQL, Array As String(strInsert))
RS1 = cConn.SQL1.ExecQuery("select SQL from TestX")
Do While RS1.NextRow
str = RS1.GetString2(0).SubString(iStartPos)
Log ("TestSerialize, str: " & str)
iStartPos = str.LastIndexOf("/*")
Log ("TestSerialize, iStartPos: " & iStartPos)
str = str.SubString(iStartPos)
Log ("TestSerialize, str: " & str)
iPosDataTypes = str.LastIndexOf(">>>>")
iPosChartTitle = str.LastIndexOf("<<<<")
iPosRunChart = str.LastIndexOf("{{{{")
iPosChartLineNames = str.LastIndexOf("}}}}")
iPosDateLine1 = str.LastIndexOf("%%%%")
iPosDateLine2 = str.LastIndexOf("££££")
Dim OM As B4XOrderedMap = B4XCollections.CreateOrderedMap
OM.Initialize
If iPosDataTypes > -1 Then
OM.Put(iPosDataTypes, 1)
End If
If iPosChartTitle > -1 Then
OM.Put(iPosChartTitle, 2)
End If
If iPosChartLineNames > -1 Then
OM.Put(iPosChartLineNames, 3)
End If
If iPosRunChart > -1 Then
OM.Put(iPosRunChart, 4)
End If
If iPosDateLine1 > -1 Then
OM.Put(iPosDateLine1, 5)
End If
If iPosDateLine2 > -1 Then
OM.Put(iPosDateLine2, 6)
End If
If OM.Size > 0 Then
'sort the OM by string position
'------------------------------
OM.Keys.Sort (True)
For i = 0 To OM.Size - 1
iKey = OM.Keys.Get(i)
If i < OM.Size - 1 Then
iEndPos = OM.Keys.Get(i + 1)
Else
iEndPos = str.LastIndexOf("*/")
End If
Dim t2 As tSQL_Extra
t2.Initialize
'If the type members are set like this all works fine!
'-----------------------------------------------------
't2.strData_Types = "I,R"
't2.strChart_Title = "Serum vitamin B12"
't2.strChartLine_Names = "B12"
't2.bRun_Chart = True
't2.strSQLForDate_Line1 = ""
't2.strSQLForDate_Line2 = ""
'Type tSQL_Extra(strData_Types As String, _
' strChart_Title As String, _
' strChartLine_Names As String, _
' strSQLForDate_Line1 As String, _
' strSQLForDate_Line2 As String, _
' bRun_Chart As Boolean)
Select Case OM.Get(iKey)
Case 1 'data types
t2.strData_Types = str.SubString2(iKey + 4, iEndPos).Trim
Log ("TestSerialize, t2.strData_Types: |" & t2.strData_Types & "|")
Case 2 'chart title
t2.strChart_Title = str.SubString2(iKey + 4, iEndPos).Trim
Log ("TestSerialize, t2.strChart_Title: |" & t2.strChart_Title & "|")
Case 3 'chart line names
t2.strChartLine_Names = str.SubString2(iKey + 4, iEndPos).Trim
Log ("TestSerialize, t2.strChartLine_Names: |" & t2.strChartLine_Names & "|")
Case 4 'run chart (boolean)
t2.bRun_Chart = True
Log ("TestSerialize, t2.bRun_Chart: |" & t2.bRun_Chart & "|")
Case 5 'date line1 pos
t2.strSQLForDate_Line1 = str.SubString2(iKey + 4, iEndPos)
Log ("TestSerialize, t2.strSQLForDate_Line1: |" & t2.strSQLForDate_Line1 & "|")
Case 6 'date line2 pos
t2.strSQLForDate_Line2 = str.SubString2(iKey + 4, iEndPos)
Log ("TestSerialize, t2.strSQLForDate_Line2: |" & t2.strSQLForDate_Line2 & "|")
End Select
Next
arrBytes = ser.ConvertObjectToBytes(t2)
'this shows the problem is not with the storage in SQLite
'--------------------------------------------------------
Dim t3 As tSQL_Extra
t3 = ser.ConvertBytesToObject(arrBytes)
Log (" ")
Log ("TestSerialize, t3.strDataTypes: " & t3.strData_Types)
Log ("TestSerialize, t3.strChart_Title: " & t3.strChart_Title)
Log ("TestSerialize, t3.strChartLineNames: " & t3.strChartLine_Names)
Log ("TestSerialize, t3.bRun_Chart: " & t3.bRun_Chart)
Log ("TestSerialize, t3.strSQLForDate_Line1: " & t3.strSQLForDate_Line1)
Log ("TestSerialize, t3.strSQLForDate_Line2: " & t3.strSQLForDate_Line2)
Log ("--------------------------------------------------------------------------- ")
End If 'If OM.Size > 0
Loop
End SubAll seems to be working well, except when transforming the byte array back to the type, not
all the type members show up. Strangely, if I set the type members directly (as shown in the
testing code) all works fine.
I had a look at all the bytes of the type members in case there was something funny going on
with null bytes, but nothing like that. Also the bytes of the byte arrays produced by the real code
are just the same as the bytes after setting the type members directly.
This seems baffling to me and maybe somebody can shed some light on this.
RBS