Backup of iOS SQLite file to DeskTop

Hi there,
I’m trying to send a local SQLite DB from a iOS device into a MySQL server and the store it on a DeskTop for ‘Rescue Situations’, but I don’t get a valid SQLIte file. Valentina Reports will not open den file s I guess some data is gone missing in the process.

FileSize is all equal: [iOS SQLite File] = [MySQL BLOB] = [Saved DeskTop File]

Can you see what I’m doing wrong?

First I Read the SQLite file from my iPhone and send it via HTTP in JSON format to my WebService

Var ReadStream As BinaryStream = BinaryStream.Open(dbFile, BinaryStream.LockModes.Read )
ReadStream.LittleEndian = True

Var hashMB As New Xojo.Core.MemoryBlock(ReadStream.Read(ReadStream.Length))
fSize = ReadStream.Length
ReadStream.Close

Var joiner() As Text
For i As Integer = 0 To hashMB.Size - 1
Var b As UInt8 = hashMB.UInt8Value(i)
joiner.AddRow(b.ToHex(2))
Next
HexDB = Text.Join(joiner, “”)

After that my WebService stores the DecodeHex string directly in a MySQL LONGBlob field

Try
Var mbHex As MemoryBlock = DecodeHex(jsonUnPacked.Lookup(“DataBlob”, “”))
Var mb As MemoryBlock
mb = mbHex

Var UserID As Integer = jsonUnPacked.Lookup(“UserID”, 0)
Var UserName As String = jsonUnPacked.Lookup(“UserName”, “”)
Var CompanyName As String = jsonUnPacked.Lookup(“CompanyName”, “”)
Var fSize As Integer = jsonUnPacked.Lookup(“FileSize”, “”)
Var DeviceID As String = jsonUnPacked.Lookup(“DeviceID”, “”)

If dbIsPresent Then
Var InsertSQL As String = “INSERT INTO GRADUS.DEVICEDATA ( UserID, UserName, CompanyName, DataType, DataSize, DeviceID, SQLiteDB ) VALUES ( ?, ?, ?, ?, ?, ?, ?) ;”
myDB.ExecuteSQL(InsertSQL, UserID, UserName, CompanyName, “SQLITE”, fSize, DeviceID, mb )
End If

Catch err As JSONException
End Try

And lastly my Adm Program read the MySQL Blob and stores it in a file:

If rs.Column(“SQLiteDB”).Value <>Nil Then

Var WriteFile As FolderItem = GetSaveFolderItem("", fn)

If writeFile <> Nil Then
Var writeStream As BinaryStream = BinaryStream.Create(writeFile, True)
writeStream.LittleEndian = True

writeStream.Write(rs.Column("SQLiteDB").Value)
writeStream.Close

End If

End If

Hmm…maybe try with a very small database and compare the resulting file with the original. Something has to be diff. but it’s hard to say what that is.

You are translating things several times and hoping you get back the original results. I’d recommend checking the data at each step to make sure it actually contains what you think it does. Are you sure encodings are not messing you up in some way here? Does your technique work with other binary files (say a picture)? Would it be possible to have your web service receive the binary data directly and stuff that into the MySQL DB to eliminate conversion issues?

It is only 110.562 bytes long (I keep the local DB ver small), and the technique is exactly the same as I use sending Images from iOS to MySQL via JSON.

I did try to debug and find the differences but the MemoryBlock seems the same in the Debugger. And I use this approach with Images.

How can I send binary data to my webService? I use HTTP via JSON, is there another way?

To send binary data with URLConnection, set the binary data using SetRequestContent before you send it to your server. On the server extract the data as binary rather than JSON.

If these DB files are exactly the same then there is no reason the copy cannot be opened. Have you checked with a diff to ensure they exactly match? I still suggest trying another binary file, such as a small picture, to verify your overall process works as you expect.

Since your DB is small, another option is to serialize the entire thing to JSON and use that for restoring. Having it in text would also be safer as you’d be able to hand-edit something in a “rescue situation” and you could more easily do diffs.

[quote=488735:@Paul Lefebvre]To send binary data with URLConnection, set the binary data using SetRequestContent before you send it to your server. On the server extract the data as binary rather than JSON.

If these DB files are exactly the same then there is no reason the copy cannot be opened. Have you checked with a diff to ensure they exactly match? I still suggest trying another binary file, such as a small picture, to verify your overall process works as you expect.

Since your DB is small, another option is to serialize the entire thing to JSON and use that for restoring. Having it in text would also be safer as you’d be able to hand-edit something in a “rescue situation” and you could more easily do diffs.[/quote]
So if I make a External DataClass for each ‘transaction type’ that both iOS and WebService can access, then I can send the class via HTTP and recieve it ‘automatic’ ? How about the Reply to iOS what format is that?

I can’t seem to find a way to send a DataClass via SetRequestContent, but would It be possible to make a Class that have Properties (data fields) and then a Method that combines all the properties (also Arrays of Data) into a Memoryblock, that then can be sent via HTTP?

Bobby,

At your source (iOS app) you have to serialize your data before you send it via your urlconnection post. I believe Paul was showing above using the binary stream class to serialize your data at the source and then deserialize at the desktop app using https://documentation.xojo.com/api/files/binarystream.html. After you deserialize you then can convert the data back into your memory block (structured data) for processing.

Using a restful implementing just makes this easier unless of course you are responsible for writing the Restful APi at the desktop app side. Let us know what you end up working with on this as we are also writing an iOS app to talk to our rest api in the cloud (no sql cloud side).

Additionally Kem Tekinay has a great set of serialization / deserialization classes available here: GitHub - ktekinay/Data-Serialization: Xojo class to serialize and deserialize classes via JSON

HTH,
Mike

Hi there,
I used a simple and ‘dirty’ method of getting the binary data onto my webService.

This is what iOS is doing:

hashMB = ReadStream.Read(ReadStream.Length)
fSize = ReadStream.Length
ReadStream.Close

Changes_http = New MyHTTPSocket
Changes_http.RequestHeader(“FileSize”) = fSize.ToText
Changes_http.RequestHeader(“UserID”) = App.ActiveUser.ID.ToText
Changes_http.RequestHeader(“UserName”) = App.ActiveUser.Name
Changes_http.RequestHeader(“CompanyName”) = App.ActiveUser.CompanyName
Changes_http.RequestHeader(“DeviceID”) = App.DeviceIdentifier
Changes_http.SetRequestContent(hashMB.Clone, “application/octet-stream”)
Changes_http.Send(“POST”, hostname)

And my WebService receives it like this:
Var output As New JSONItem

Var myDB As New MySQLCommunityServer
myDB = ConnectDB

Var FileSize As String = param.GetRequestHeader(“FileSize”).DefineEncoding(Encodings.UTF8)
Var UserID As String = param.GetRequestHeader(“UserID”).DefineEncoding(Encodings.UTF8)
Var DeviceID As String = param.GetRequestHeader(“DeviceID”).DefineEncoding(Encodings.UTF8)

If myDB <> Nil Then

If dbIsPresent Then
Var InsertSQL As String = “INSERT INTO GRADUS.DEVICEDATA ( DeviceID, UserID, DataType, DataSize, SQLiteDB ) VALUES ( ?, ?, ?, ?, ? ) ;”
myDB.ExecuteSQL(InsertSQL, DeviceID, UserID, “SQLITE”, FileSize, param.Entity )
End If

output.Value(“Databaserecieved”) = “OK”
myDB.Close
Return output

Else
output.Value(“DB Error In Function: SendDB”) = “No DB Connection Possible”
Return output
End If

Using this technique it would be very simple to use a iosSQLITE MemoryDatabase to store all fields in, and then just send the SQLite Database as binary, and extract all the data from that on the Server.

This is probably not ‘The Correct Way’ and most would probably think its cutting corners, but if it works. And I don’t believe that SQLite files carry that much ‘overhead’ that it would matter to performance