B4J Question Remotely Host Mysql

codie01

Active Member
Licensed User
Hi Guys, I have a remotely hosted mysql database on my website. I am trying to write an app to access the database. I have read a lot of online info, but none seems to explain it clearly. Here is what I have so far and it does not work.

#Region Project Attributes
#MainFormWidth: 600
#MainFormHeight: 400
#AdditionalJar: mysql-connector-java-5.1.35-bin.jar
#End Region

Sub Process_Globals
Private fx As JFX
Private MainForm As Form
Private sql1 As SQL
Private sqlsend As Button
Private box As TextField
Private street As TextField
End Sub

Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.RootPane.LoadLayout("sqltest") 'Load the layout file.
MainForm.Show
sql1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://sql6.freemysqlhostingt.net", "username", "password")
End Sub

Sub sqlsend_MouseClicked (EventData As MouseEvent)

End Sub

Can anyone post some sample B4J code here. From this I will be able to work it out. What I'm after is four things:

Connect, write data, read data, close the connection.

Thanks guys
 

codie01

Active Member
Licensed User
Wow, Thanks Erel for a quick response.

First error: Error occurred on line: 20 (main)
Second error: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I have read all your postings.
 

codie01

Active Member
Licensed User
Hi Alienhunter,

Would you have some sample code? Or a good link for bj4, Thanks in advance
 

codie01

Active Member
Licensed User
Hi Alienhunter, Erel,

I have had to use Php scripts to remote access. That said I can save data and now want to read that back into a tableview. Can you please, please help here.

App Code

B4X:
 Public Sub GetSites
      Dim ListSites As HttpJob
    ListSites.Initialize("ListP", Me)
    ListSites.download2("http://codie01.freewebhost.co.nz/site_GetList.php", Array As String ("action", "GetSites"))
    Dim res As String
    Dim ListOfSites As List'
    Dim parser As JSONParser
    parser.Initialize(res)
    ListOfSites = parser.NextArray 'returns a list with maps
    siteView.SetColumns(Array As String("Col1", "Col2"))   
    For i = 0 To ListOfSites.Size - 1
        Dim Site As Map
        Dim row(2) As Object
        Dim lbl As Label
        lbl.Initialize("")
        lbl.Text = Site.Get("siteDisplay")
        row(1) = lbl
        siteView.Items.Add(row)                           
    Next
 End Sub
and the php code

B4X:
<?

$host = "sql306.freewebhost.co.nz";
$db = "freew_16364963_SITEJOBS";
$user = "username";
$pw = "password";

$con = mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
        $q = mysql_query("SELECT siteDisplay FROM sites");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);

?>
 

codie01

Active Member
Licensed User
To further assist I have again modified my app code as follows:

B4X:
Public Sub GetSites
      Dim ListSites As HttpJob
    ListSites.Initialize("ListP", Me)
    ListSites.download2("http://codie01.freewebhost.co.nz/site_GetList.php", Array As String ("action", "GetSites"))
    Dim res As String
    Dim parser As JSONParser
    parser.Initialize(res)
    Dim ListOfPersons As List
    ListOfPersons = parser.NextArray 'returns a list with maps
    siteView.SetColumns(Array As String("Col1"))
    If ListOfPersons.Size=0 Then
      Log("None")
    Else   
    For i = 0 To ListOfPersons.Size - 1
       Dim Site As Map
        Site = ListOfPersons.get(i)
        Dim Row(1) As Object
        Dim lbl As Label
        lbl.Initialize("")
        Row(0) = Site.Get("siteDisplay")
        siteView.Items.Add(Row)                           
    Next
    End If
 End Sub
This is the error:

Program started.
main._getsites (java line: 121)
org.json.JSONException: End of input at character 0 of
at org.json.JSONTokener.syntaxError(JSONTokener.java:450)
at org.json.JSONTokener.nextValue(JSONTokener.java:97)
at anywheresoftware.b4j.objects.collections.JSONParser.NextArray(JSONParser.java:60)
at simFOLD.pro.main._getsites(main.java:121)
at simFOLD.pro.main._appstart(main.java:72)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at simFOLD.pro.main.start(main.java:36)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$159(LauncherImpl.java:863)
at com.sun.javafx.application.LauncherImpl$$Lambda$53/12019059.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$172(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl$$Lambda$46/3711894.run(Unknown Source)
at com.sun.javafx.application.PlatformImpl.lambda$null$170(PlatformImpl.java:295)
at com.sun.javafx.application.PlatformImpl$$Lambda$48/4191146.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$171(PlatformImpl.java:294)
at com.sun.javafx.application.PlatformImpl$$Lambda$47/7816767.run(Unknown Source)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$145(WinApplication.java:101)
at com.sun.glass.ui.win.WinApplication$$Lambda$36/11714529.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)


Thanks in advance
 

alienhunter

Active Member
Licensed User
Hi ,
as promised this works too
i am downloading the online table to a local table

your php is basicly the the same



B4X:
<?

$databasehost = "lwebsite.com";
$databasename = "name";
$databaseusername ="name";
$databasepassword = "pass";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>






B4X:
Dim ServerUrl As String
  ServerUrl = "http://website.com//hsm.php"



ExecuteRemoteQuery("SELECT * FROM hsmcust","cust")


Sub ExecuteRemoteQuery(Query As String, JobName As String)
   Dim job As HttpJob
   job.Initialize(JobName, Me)
   job.PostString(ServerUrl, Query)
End Sub
...
Sub JobDone(job As HttpJob)
   ProgressDialogHide
   If job.Success Then
   Dim res As String
     res = job.GetString
     Log("Response from server: " & res)
     Dim parser As JSONParser
     parser.Initialize(res)
Select job.JobName
      Case "cust"
      Dim custm As List
      custm=parser.NextArray
      For i=0 To custm.Size-1
      Dim m As Map
      m=custm.Get(i)
      Log(m)
      sqlc.ExecNonQuery2("INSERT INTO table1 VALUES(?,?,?,?,?,?,?,?,?)",Array As Object(m.Get("cust"),m.Get("custadr"),m.Get("custc1"),m.Get("custc1p"),m.Get("custc1e"),m.Get("custc2"),m.Get("custc2p"),m.Get("custc2e")))
      Next
End Select
   Else
     ToastMessageShow("Error: " & job.ErrorMessage, True)
   End If
   job.Release
End Sub
 
Top