Android Question ¿How to Synchronize Desktop App & Android App Databases? [ SQLite ] [ Android ] [ PC ]

[Jausa]

Greetings.

I have been developing an Android app which allows the user to manually add data (Native SQLite DB) and then Sync that data (Bidirectionally) to a PC Native Database (Any MS OleDB Capable Database).

The development of the app was going perfect, but i faced a great wall:
It is required to explicitly Sync the data through USB and to be compatible with almost every device and older versions of Windows (XP - Vista - 7 - 8), so i deceided to use the famous MTP Protocol, which was first implemented on Windows Media Player v10 (XP)

But i don't know if this was a good election.

Searching for hours and hours on the internet(Google) and some other sites dedicated to programing, i found some references:

The "Almighty" WPDAPI (Windows Portable Devices API), this API should make easy the use of the MTP Protocol, and it was implemented Since WMP10. But actually there are no examples (I didn't find anything) of code on the MSDN Site and it seems that is written on C++, like a small rock on the shoe.

The WIA (Windows Image Adquisition) that actually should work for this Job, but it doesn't because it only works with Imaging Devices Such as Cameras and Scanners (.jpg files and stuff).

And a miracle of Lbrary named #MtpSharp, an Open Source (And currently abandoned) Proyect developed almost 10 years ago to be used with any language of the .Net platform (This is important because i'm actually using the MS.Net platform to develop the app) and it encapsulates the Elements of the WPD allowing us to use them naturally.

Doing the Trial and Error mechanism i managed myself to get some information about the connected MTP Devices. (Because of the lack of documentation of the proyect).

This is so difficult for me.
So i deceided to send an E-Mail to the creator of the library asking how to Copy/Delete Documents Bidirectionally.

His response:

Hello

Unfortunately, retrieving files of the device is currently not supported but It should not be that hard to add support for it.
I have developed this library for an mp3 sync application and therefor jmtp currently only supports creating folders, copying files to the device and deleting files.

I currently do not work on jmtp anymore, but feel free to add support for it yourself.

Kind regards,
Pieter

I will not surrender about this. But i actually think that it would be better to detect the Attached Device on the PC[Mass Storage Mode] and execute a Natural Copy - Paste operation.

Also i want to add the feature of Sending the Database VIA Internet (On the android device) (It could be any service or e-mail).

So what i wanted to ask is that if there is anyone that have been on a scenario like this and could bring a little help. Greetings

 

[Erel]

I also searched for a working MTP Windows solution in the past and didn't find one.

 

[Jausa]

[Partially Solved(?)]

I had to sacrifice the "Every Device" Compatibility.
For this case you have to connect the device in [Mass Storage Mode]
By doing this, not every Android Phone/Tablet will be able to run the app.

On the computer you have to make an USB Listener which will listen the different events and trace the Drive letter to make an automatic copy of a document/database/anything from the device.

Here is the code [VB.NET]

'First you have to make a reference to the
'System.Management DLL

Imports System.Management
Imports System.IO

Public Class Sync

Private WithEvents Watcher As ManagementEventWatcher
    Public USB_Name As String = ""
    Public USB_Letter As String = ""
    Private Source As String
    Private Target As String = Application.StartupPath

'In this case i use a RadioButton to Trigger
'The device dettection

Private Sub RAD_Common_CheckedChanged(sender As Object, e As EventArgs) Handles RAD_Comun.CheckedChanged
        If RAD_Common.Checked = True Then
            StartDetection()
            LBL_Status.Text = "Waiting connection"
        End If
    End Sub

Public Sub StartDetection()
        Dim xQuery As New WqlEventQuery("SELECT * FROM __InstanceOperationEvent WITHIN 1 " & "WHERE TargetInstance ISA 'Win32_DiskDrive'")
        Watcher = New ManagementEventWatcher
        Watcher.Query = xQuery
        Watcher.Start()
    End Sub

Private Sub Eventuality(ByVal sender As Object, ByVal e As System.Management.EventArrivedEventArgs) Handles Supervisor.EventArrived
        Dim Gest As ManagementBaseObject = CType(e.NewEvent, ManagementBaseObject)
        Dim xObject As ManagementBaseObject = CType(Gest("TargetInstance"), ManagementBaseObject)

        Select Case Gest.ClassPath.ClassName
            Case "__InstanceCreationEvent"
                If xObject.Item("InterfaceType").ToString = "USB" Then
                    USB_Name= xObject.Item("Caption").ToString
                    USB_Letter= GetLetter(xObject.Item("Name").ToString)
                    LBL_Status.Text = "Connected: " & USB_Name
                    LBL_Letter.Text = USB_Letter
                End If
            Case "__InstanceModificationEvent"
                If xObject.Item("InterfaceType").ToString = "USB" Then
                    USB_Name= xObject.Item("Caption").ToString
                    USB_Letter= GetLetter(xObject.Item("Name").ToString)
                    LBL_Status.Text = "Detected Change On: " & USB_Name
                    LBL_Letter.Text = USB_Letter
                End If
            Case "__InstanceDeletionEvent"
                If xObject.Item("InterfaceType").ToString = "USB" Then
                    LBL_Status.Text = "Disconnected: " & USB_Name
                    LBL_Letter.Text = USB_Letter& " is not available"
                    If xObject.Item("Caption").ToString = USB_Name Then
                        USB_Letter = "Disconnected"
                        USB_Name = ""
                    End If
                End If
            Case Else
                If xObject.Item("Caption").ToString.Length <> 0 Then
                    LBL_Status.Text = "Unable to connect to device: " & xObject.Item("Caption").ToString & "Please try again"
                    LBL_Letter.Text = ""
                Else
                    LBL_Status.Text = ("Unrecognizable device. Try again")
                    LBL_Letter.Text = ""
                End If
        End Select
    End Sub

Private Function GetLetter(ByVal Name As String) As String
        Dim Query_P, Query_D As ObjectQuery
        Dim Finder_P, Finder_D As ManagementObjectSearcher
        Dim Object_P, Object_D As ManagementObject
        Dim X As String = ""

        Name= Replace(Name, "\", "\\")
        Query_P = New ObjectQuery("ASSOCIATORS OF {Win32_DiskDrive.DeviceID=""" & Name & """} WHERE AssocClass = Win32_DiskDriveToDiskPartition")
        Finder_P = New ManagementObjectSearcher(Query_P)

        For Each Object_P In Finder_P.Get()
            Query_D = New ObjectQuery("ASSOCIATORS OF {Win32_DiskPartition.DeviceID=""" & Object_P.Item("DeviceID").ToString & """} WHERE AssocClass = Win32_LogicalDiskToPartition")
            Finder_D = New ManagementObjectSearcher(Query_D)
            For Each Object_D In Finder_D.Get
                X &= Object_D.Item("Name").ToString & "\"
            Next
        Next
        X.Trim("\"c)
        Return X
    End Function

Private Sub Sync_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        Watcher.Stop()
        Watcher .Dispose()
    End Sub

Getletter returns the device letter if it is valid, so the drive letter will be the root folder in which i will store my XML or .DB document.

I wanted to copy the database, but i think XML is better this time.

I will search on the forum how to write XML data from a Database.
The app can be even more compatible if i add a "Send by e-mail XML file", but i will search on the forum a how-to.

Regards.

 

[Reviewnow]

Jausa,

here is an example on how to export your SQLite db to xml
http://www.b4x.com/android/forum/th...abases-are-now-simple.8475/page-7#post-221218

 

[Jausa]

Thank you, Reviewnow i think it should not be hard to Import that XML file to a VB.Net Database.

 

[Jausa]

When i try to use the ExportTableToXmlString Method it throws an exception at:

LogCat connected to: emulator-5554
Copying updated assets files (10)

** Activity (main) Create, isFirst = true **
ExecuteMemoryTable: SELECT name FROM route
** Activity (main) Resume **
ExecuteMemoryTable: SELECT name FROM route
** Activity (main) Pause, UserClosed = false **
** Activity (sync) Create, isFirst = true **
** Activity (sync) Resume **
exporting Table - readings

Cannot get methods of class: com.jamesmurty.utils.XMLBuilder, disabling cache.

Error occurred on line: 446 (dbutils)
java.lang.TypeNotPresentException: Type javax.xml.transform.TransformerException not present

at java.lang.Class.getDeclaredMethods(Native Method)
at java.lang.ClassCache.getDeclaredPublicMethods(ClassCache.java:166)
at java.lang.ClassCache.getDeclaredMethods(ClassCache.java:179)
at java.lang.ClassCache.findAllMethods(ClassCache.java:249)
at java.lang.ClassCache.getFullListOfMethods(ClassCache.java:223)
at java.lang.ClassCache.getAllPublicMethods(ClassCache.java:204)
at java.lang.Class.getMethod(Class.java:1013)
at anywheresoftware.b4a.shell.Shell$MethodCache.getMethod(Shell.java:878)
at anywheresoftware.b4a.shell.Shell.getMethod(Shell.java:549)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:635)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:162)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:158)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:66)
at android.view.View.performClick(View.java:2364)
at android.view.View.onTouchEvent(View.java:4179)
at android.widget.TextView.onTouchEvent(TextView.java:6541)
at android.view.View.dispatchTouchEvent(View.java:3709)
at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
at com.android.internal.policy.impl.PhoneWindow$DecorView.superDispatchTouchEvent(PhoneWindow.java:1659)
at com.android.internal.policy.impl.PhoneWindow.superDispatchTouchEvent(PhoneWindow.java:1107)
at android.app.Activity.dispatchTouchEvent(Activity.java:2061)
at com.android.internal.policy.impl.PhoneWindow$DecorView.dispatchTouchEvent(PhoneWindow.java:1643)
at android.view.ViewRoot.handleMessage(ViewRoot.java:1691)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:4363)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
at dalvik.system.NativeStart.main(Native Method)



At this part of the ExportTable Method it throws an error:

For col = 0 To c.ColumnCount - 1
Try
dbxml = dbxml.element(c.GetColumnName(col)).text(c.GetString2(col)).up
Catch
End Try
Next

 

[Reviewnow]

did you include the xmlbuilder library?

 

[Jausa]

Yeah, it is included. :/

 

[Reviewnow]

can you post the code section you use to call the sub ExportTableToXmlString?

 

[Jausa]

If File.ExternalWritable Then
        File.WriteString(File.DirDefaultExternal, _
        "doc.xml", _
        DBUtils.ExportTableToXmlString(Main.SQLite,"jausalecturas","lecturas"))
    Else
    ...
    End If