Backup of iOS SQLite file to DeskTop

  1. 3 weeks ago

    Bobby K

    May 14 Testers, Xojo Pro Ålsgårde, Denmark

    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
    -------

  2. 2 weeks ago

    Jason P

    May 15 Xojo Inc, Forum Moderators Texas

    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.

  3. Paul L

    May 15 Xojo Inc, Third Party Store

    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?

  4. Bobby K

    May 15 Testers, Xojo Pro Ålsgårde, Denmark

    @Jason P 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.

    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.

  5. Bobby K

    May 15 Testers, Xojo Pro Ålsgårde, Denmark

    @Paul L 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?

    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?

  6. Paul L

    May 15 Xojo Inc, Third Party Store

    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.

  7. Bobby K

    May 15 Testers, Xojo Pro Ålsgårde, Denmark

    @Paul L 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.

    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?

  8. Bobby K

    May 15 Testers, Xojo Pro Ålsgårde, Denmark

    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?

  9. Mike C

    May 19 Testers, Xojo Pro North Carolina (USA)
    Edited 2 weeks ago

    @Bobby K 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://docs.xojo.com/BinaryStream . 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: https://github.com/ktekinay/Data-Serialization

    HTH,
    Mike

  10. Bobby K

    May 20 Testers, Xojo Pro Ålsgårde, Denmark

    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

  11. Bobby K

    May 20 Testers, Xojo Pro Ålsgårde, Denmark

    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

or Sign Up to reply!