B4J Question Remotely Host Mysql

Discussion in 'B4J Questions' started by codie01, Jun 28, 2015.

Similar threads

B4J Tutorial SQL Tutorial
B4J Tutorial [B4X] Resumable Subs - Sleep / Wait For
B4J Tutorial Pagination Container
B4J Tutorial Accordion container
B4J Tutorial Unhandled exceptions
B4J Tutorial Customized ListView
  1. codie01

    codie01 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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Please use [code]code here...[/code] tags when posting code.

    Which error do you get?
     
  3. codie01

    codie01 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.
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Based on this error message it is a network problem. The hosting service probably doesn't open the port for external connections.
     
  5. codie01

    codie01 Member Licensed User

    Thanks Erel.
     
  6. alienhunter

    alienhunter Active Member Licensed User

    hi , i had the same problem with other free website , i wrote a small php to connect to the sql and it worked
     
  7. codie01

    codie01 Member Licensed User

    Hi Alienhunter,

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

    alienhunter Active Member Licensed User

    hi sure it just has to wait until the weekend ,sorry
    but if i remember well i found this here in some post
    it is very a simple php code that you have to upload to your website
     
  9. codie01

    codie01 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

    Code:
    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(2As 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

    Code:
    <?

    $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);

    ?>
     
  10. codie01

    codie01 Member Licensed User

    To further assist I have again modified my app code as follows:

    Code:
    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(1As 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
     
  11. Erel

    Erel Administrator Staff Member Licensed User

  12. alienhunter

    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



    Code:
    <?

    $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);
    }
    ?>






    Code:
    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
     
Loading...