B4J Question DBUtils ExecuteMemoryTable

[ThRuST]

I'm using DBUtils v2.02 and can load contents from an SQLite database into a TableView.
So far so good right, but here comes the tricky part..

Now I want to mirror the server contents FROM a MySQL database on a domain that runs PHP (www.one.com) to a LOCAL SQLite db = Security risk.
and would like to know what options is there to do this? I could send jobs to download the data with jOkHttpUtils2 and convert the Arrays to Lists, then store the data in a temporary SQLite database on the drive and have it loaded with ExecuteTableView, as concerns this question and also alternative ways to use, like ExecuteMap but it would be better to load the data from memory directly in the TableView. This would be more secure since there will be encrypted hash-strings for each user. Even if the encryption will be secure there must be a better way. so imagine there's 7 jOkHttpUtils2 jobs that downloads data in each array from the server using a PHP script. How to view the data in a TableView? Thanks for your help.

 

[OliverA]

Get a SSL certificate for your domain. Use HTTPS to access the PHP script. Use username/password or pin or some secret for access control to the PHP script.

 

[OliverA]

Get a SSL certificate for your domain

Let me clarify this one. Get a SSL certificate from a trusted certificate authority (CA). In you application only accept SSL connections that have a trusted certificate associated with them. This should make man-in-the-middle attacks harder. You could cook up you own encryption scheme, but effective encryption is hard, very hard to get correct.

 

[ThRuST]

Thanks OliverA. Your advice is just what jOkHttpUtils2 library is used for. To send and retrieve data between a mySQL server that runs PHP.
However a webview will not help, since I want the mySQL contents to be viewed in the TableView in my client application.
The calls can be handled through jOkHttpUtils2 to send POST requests to the server, were PHP scrips will act like a gateway between my application and the server.

 

[ThRuST]

I mean something like this

job.Initialize("Job", Me)
    job.PostString("http://www.domain.com/transfer.php", "p1=send")

Sub JobDone (Job As HttpJob)
    
   Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
       If Job.Success = True Then
         'Log(Job.GetString)
        
        Dim MyList1 As List
        MyList1.Initialize
        
               ' Put the array in a list
        Dim Item As String
        For Each Item As String In Regex.Split(Chr(13), Job.GetString)
            MyList1.Add(Item & Chr(13))
            Log(Item)
        Next

' Do the same with job1-7 then generate the lists to be viewed in DButils with ExecuteMemoryTable...
    
End Sub

 

[ThRuST]

My domain offers SSH to encrypt traffic.

 

[ThRuST]

How can I get the mySQL server contents to be viewed in a Tableview?

 

[OliverA]

Could create JSON on the server and then use JSON parser to extract the List (via JSON parser's NextArray). Instead of 7 http job's, could create a more complex JSON structure to download all at once (size permitting) and use JSON parsers NextObject to extract a map. Can use JsonTree (https://www.b4x.com/android/forum/t...help-with-json-parsing-b4a-b4j.35963/#content) to play/create a JSON structure that will work (and create the B4A code to access it). Then you can take the code of DBUtils ExecuteTableView and modify it to access the JSON information for column headers/table information. For a code fragment related to this see https://www.b4x.com/android/forum/threads/how-to-bind-sql-data-in-grid.76700/#post-486301.

 

[ThRuST]

Interesting solution to create a Json package on the server through PHP and have it sent over. DButils should be able to display the data from a map, I assume JSON have to be converted into a MAP. Any code example on how to do this?

 

[OliverA]

1. JSONParser documentation
2. This may be what you are looking for (https://www.b4x.com/android/forum/threads/mysql-to-tableview.35088/)
3. Make sure JSON is returned from your HTTPJob (https://www.b4x.com/android/forum/t...check-type-of-data-coming-back.48818/#content)
4. Use Try/Catch for all other exceptions that may occur while parsing the returned JSON (https://www.b4x.com/android/forum/threads/issue-with-json-parser-error-handling.31064/)

 

[ThRuST]

Great job, I will have a look at this. Goodnight!!

 

[ThRuST]

Since this is so far the only post for B4j that concerns ExecuteMemoryTable since year 2012 I can refer to B4A questions and one of these can be found >here<

If there's a complete tutorial that covers all aspects of the DBUtils modules please post them here. Thanks

 

[OliverA]

?

Now I'm confused. Maybe I understand your premise incorrectly. It seemed to me that you needed to download securely some information from on online DB onto your device. You then display this information. You did not want to store this downloaded information on your device due to security concerns and only keep the downloaded information in memory. You would like to display the information in a TableView. I'm half way sure posts #2 and #3 covered the secure transfer of the information to the device and posts #8 and #10 covered a means to a format the information for downloading and keeping the information in the memory of the device. You can then display that information in your TableView.

DBUtils deals with accessing databases. Right now, your DB is hosted and queried via PHP on the host. The current proposed solution does not require DBUtils, but portion of DBUtils code (executeTableView) can be used (after modifying it) to extract the information from the JSON structure to a list that is added to a TableView. If you just want to create the list, them modify ExecuteMemoryTable.

If you insist on using DBUtils for the data on the device side, then what may be appropriate is using an in-memory SQLite database (see https://www.b4x.com/android/forum/threads/sqlite-in-memory-databases.88697/). You still need to figure out a way to "mirror" your MySQL DB. For secure transfer I would still go with SSL (trusted certificates) and for data transfer you could still go with JSON or open up a new thread to ask or search the forum for existing answers on this issue.

 

[OliverA]

If there's a complete tutorial that covers all aspects of the DBUtils modules please post them here

1. B4J - https://www.b4x.com/android/forum/threads/dbutils-example.34611/#content
   
2. B4A - https://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/#content
3. B4i - https://www.b4x.com/android/forum/threads/module-dbutils.46563/#content

 

[ThRuST]

I suppose this is the closest we'll get, reading posts that is from year 2011 and for B4A. But hey, as long as it works, why fix it

Some YouTube tutorials, perhaps?

 

[OliverA]

reading posts that is from year 2011 and for B4A

1. A lot of stuff that works in B4J works in B4A and vice versa. If not, a small change will usually make it work.
2. SQL is very mature and therefore what worked a long time ago still works now. If you want bleeding edge and ever-changing API, then try the Firebase stuff (as an example).

 

[ThRuST]

I see your point, and mine is that it would be great with a complete tutorial series that covers every part in-depth. Youtube is freely waiting. Thanks OliverA