### Connect MySQL RemoteDB 1

Described is one way on how to connect to and manage data on a Remote MySQL Database?
It is based on this B4J HowTo example "Access a MySQL Remote Database".
MySQL Reference Manuals: http://dev.mysql.com/doc/
Principle
The remote MySQL database (f.e. hosted on your Internet provider server) is accessed from the
B4J app using a Http job (can be more then one! depending on the requests).
The Http job sends a request to the server, picked up by a php script which name has been passed (url)
as a parameter by the http job.
The php script connects to the MySQL database and executes the request as given by the B4J app.
The result of the request is then passed from the php script back to the B4J app in JSON format.
The B4J app handles the result by the Http job Jobdone event.
More specifics on Http job requests from B4J to the Remote Server:
• All Http jobs have same setup: init the Http job first and the execute postdownload2.
• The Http job event Jobdone will handle the result of the server php script.
• IMPORTANT: Download2 (which submits a HTTP GET request) is used to ensure parameters are escaped (Encodes illegal parameter characters).
• More http://www.b4x.com/b4j/help/jhttputils2.html#httpjob_download2
What is required?
Access data as required for the Http job
Following connection parameter are used by the Http job:
"hostname", "databasename", "username", "password".
A remote MySQL Database with Tables
Depending on the B4J app, the database and the tables must exist, else these have to be created by the app.
A remote php script to handle the B4J requests via Http jobs
This remote php script requires the connection data. For security reasons, the connection data should be stored
in a different file as the remote php script. Example:
File mysqlremoteconnect.php with the connection parameters
$host = "hostname";
$db = "databasename";
$user = "username";
$pw = "password";
?>
To include the connect file use a statement like below in the remote php script:
// File:
// mysqlremote.php
// Purpose:
// PHP script to access a remote MySQL database and select records
$today = date("Y-m-d H:i:s");
// Set the database parameter to connect
// Define the include file: $host, $db, $user, $pw
include ("mysqlremoteconnect.php");
// etc
?>
B4J define the url to access mysqlremote.php
in the var urlbase in Process_Globals
' This url is passed to the remote server from httpjob.download2 method. Additional parameter are passed as well (see below subs)
Private urlbase As String = "/mysqlremote.php"
B4J Http job to select data
Dim jobSelect As HttpJob
jobSelect.Initialize("JobSelect", "Main")
jobSelect.Download2(urlbase, Array As String ("action", "select"))
B4J Http JobDone event
' Handle the result of the HttpJob by checking if successfull first, then get the return string and
' depending on the jobname carry out the appropriate action
`Sub JobDone (job As HttpJob)
' Log(DateTime.Time(DateTime.Now) & " :: JobName = " & job.JobName & ", Success = " & job.Success)
If job.Success = True Then
Dim result As String
Try
result = job.GetString
Catch
txtOutput.Text = txtOutput.Text & CRLF & LastException.Message
Return
End Try
' msg.show("Result", job.GetString)
Select job.JobName
' Parse the result data string which has JSON format
Case "JobSelect"
Dim parser As JSONParser
parser.Initialize(result)
' Clear all items from the tableview
tvwMain.Items.Clear
' Parse the data into a list with maps using nextarray
updateTable(parser.NextArray)
...
B4J Example
As mention at the beginning, look into the example code. It is quite comprehensive documented.
`

Changed: 2018-06-07 09:17, Category: MySQL, File: T6C223M1646.htm, ID: 1646