Android Question how to connect to mysql database

rogel a. tolentino

Member
Licensed User
I already connected an android phone via vpn at the server.
The ip address of the server is 192.168.0.5
The schema name is casureco2 and i want to display the output of
"select * from area" where area is one of the table of casureco2
can you provide me sample code
 

rogel a. tolentino

Member
Licensed User
i tried this. my goul is to access the server with ip address 192.168.0.5
the mysql database with schema hrd
What is the correct syntax? for the line

Private ServerUrl As String = "http://192.168.0.5/"

B4X:
Sub Process_Globals
    'Private ServerUrl As String = "http://www.example.com/test1.aspx"
    Private ServerUrl As String = "http://192.168.0.5/"
End Sub
Sub Activity_Create(FirstTime As Boolean)
   Dim job As HttpJob
   job.Initialize("Job1", Me)
   Activity.LoadLayout("front")
   'job.PostString(ServerUrl, "SELECT col1, col2 FROM Table_1")
   job.PostString(ServerUrl, "SELECT * from hrd.zerobill")
End Sub
Sub JobDone (Job As HttpJob)
   If Job.Success Then
       Dim parser As JSONParser
       Dim response As String = Job.GetString
       parser.Initialize(response)
       Dim rows As List
       rows = parser.NextArray
      
       'work with result
       'rows is a List. Each item is a Map with the columns names as keys and the db values as the values.
       For i = 0 To rows.Size - 1
           Log("Rows #" & i)
           Dim m As Map
           m = rows.Get(i)
           Log("col1=" & m.Get("col1")) 'log the values of col1 and col2
           Log("col2=" & m.Get("col2"))
       Next
   Else
       Msgbox("aaa","sss")
   End If
   Job.Release
End Sub

the message is
ResponseError. Reason: java.net.ConnectException: Failed to connect to /192.168.0.5:80, Response:
 
Last edited:
Upvote 0

rogel a. tolentino

Member
Licensed User
I tried you sample mysql and executed properly

in this code
job.PostString("https://127.0.0.1:17178/casureco2", Query)

the error is this.
ResponseError. Reason: java.net.ConnectException: Failed to connect to /127.0.0.1:17178, Response:
java.net.ConnectException: Failed to connect to /127.0.0.1:17178

What is wrong in the code? my goal is to access the mysql database casureco2 of my local pc server
B4X:
#Region Module Attributes
    #FullScreen: False
    #IncludeTitle: True
    #ApplicationLabel: MySQL Example
    #VersionCode: 1
    #VersionName:
    #SupportedOrientations: portrait
#End Region
#AdditionalJar:mysql-connector-java-5.1.22-bin
'Activity module
Sub Process_Globals
    Private COUNTRIES_LIST = "countries_list", COUNTRY_POPULATION = "country_population" As String
End Sub

Sub Globals
    Type TwoLines (First As String, Second As String)
    Dim lblPopulation As Label
    Dim ListView1 As ListView
    Dim lblCountry As Label
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("1")
    FetchCountriesList
End Sub
Sub FetchCountriesList
    ProgressDialogShow("Fetching list of countries")
    'Gets all the available countries
    'ExecuteRemoteQuery("SELECT name, id FROM countries ORDER BY id", COUNTRIES_LIST)
    ExecuteRemoteQuery("SELECT * from zerobill", COUNTRIES_LIST)
End Sub


Sub ListView1_ItemClick (Position As Int, Value As Object)
    Dim tl As TwoLines
    tl = Value
    lblCountry.Text = tl.Second
    lblPopulation.Text = "Calling server..."
    ExecuteRemoteQuery("SELECT population FROM countries WHERE id='" & tl.First & "'", COUNTRY_POPULATION)
End Sub

Sub ExecuteRemoteQuery(Query As String, JobName As String)
    Dim job As HttpJob
    job.Initialize(JobName, Me)
    'job.PostString("https://www.b4x.com/android/countries.php", Query) óriginal sample
    'job.PostString("https://192.168.0.5/casureco2", Query)
    job.PostString("https://127.0.0.1:17178/casureco2", 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 COUNTRIES_LIST
                Dim COUNTRIES As List
                COUNTRIES = parser.NextArray 'returns a list with maps
                    Dim m As Map
                For i = 0 To COUNTRIES.Size - 1
                    m = COUNTRIES.Get(i)
                    'We are using a custom type named TwoLines (declared in Sub Globals).
                    'It allows us to later get the two values when the user presses on an item.
                    Dim tl As TwoLines
                    tl.First = m.Get("id")
                    tl.Second = m.Get("name")
                    ListView1.AddTwoLines2(tl.First, tl.Second, tl)
                Next
            Case COUNTRY_POPULATION
                Dim l As List
                l = parser.NextArray
                If l.Size = 0 Then
                    lblPopulation.Text = "N/A"
                Else
                    Dim m As Map
                    m = l.Get(0)
                    lblPopulation.Text = NumberFormat2(m.Get("population"),0, 0, 0, True) & " (K)"
                End If
        End Select
    Else
        Log(Job.ErrorMessage)
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub
 
Upvote 0

rogel a. tolentino

Member
Licensed User
in your example you are using
B4X:
#AdditionalJar: mysql-connector-java-5.1.27-bin
my available is
B4X:
#AdditionalJar: mysql-connector-java-5.1.22-bin
If 5.1.22 is not valid can you provide me mysql-connector-java-5.1.27-bin.jar
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

rogel a. tolentino

Member
Licensed User
i want to clarify if jRDC2 will work in B4A. In your example it is applied in B4J. Even your sample program for jrdc the filename is jrdc.b4j with RDCConnector.bas and RDCHandler.bas

Another query: in config.properties
what file being access by the statement below?
DriverClass=com.mysql.jdbc.Driver
 
Last edited:
Upvote 0

rogel a. tolentino

Member
Licensed User
In our case the mysql server has an ip address 192.168.0.5, So i was to install b4j in mysql server and in my local pc where there is also a mysql server?
If i am not mistaken the reason why 171.0.0.1:17178/test is error because i have no b4j in the server and in my local pc. Should i install b4j? if yes is b4j has no effect in existing b4a installed?
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
if yes is b4j has no effect in existing b4a installed?
Of course not.

jRDC2 can run on the same server as the database server. It can also run on a different server if that server is accessible.

The simplest way to start is with jRDC2 and the database server running on the same PC.
 
Upvote 0

josejad

Expert
Licensed User
Longtime User
Hi rogel:

Just to clarify about jRDC2.
- You have to install B4J in your PC, setup the config.properties, copy de jdbc driver to your pc, and config everything.
If the mysql server ip is 192.168.0.5, then you have to write in your config.properties:
B4X:
JdbcUrl=jdbc:mysql://192.168.0.5:3306/test?characterEncoding=utf8
with the user and password of you mysql server
- Run the B4J server, then test in the same pc "http://localhost:17178/test" (make sure 17178 is the ServerPort you have set up in config.properties file).
- If it works, then you have to use jRDC2 client in your android app and configure the IP of your PC (because you're running the B4J jRDC2 server in your PC, and this server will "talk" with the mysql server)
B4X:
Private const rdcLink As String = "http://yourPCip:17178/rdc"

Ok, this is the basis, but once everything is working, the real way of using it is this:
- B4J creates a file called jRDC2.jar (or the name of your B4J program) under the /Objects folder. This is the server program you have to run, it's not necesary to run B4J, just the Java program. (search about running a java program)
- Probably you will run the jRDC2 not in your pc, but in your server (192.168.0.5 in this case), so, before use the jRDC2.jar in your server, you have to set your config.properties to:
B4X:
JdbcUrl=jdbc:mysql://localhost:3306/test?characterEncoding=utf8
because now the jRDC2 server and your mysql server are in the same computer (anyway, 192.168.0.5 should work to). Generate the .jar server, and run it from you mysql server.

Now, from your pc it should work: "http://192.168.0.5:17178/test"

and in your B4A client app you have to set
B4X:
Private const rdcLink As String = "http://192.168.0.5:17178/rdc"
because now your jRDC2 server is there.

I hope it helps
 
Last edited:
Upvote 0

rogel a. tolentino

Member
Licensed User
To all who helped me in this thread:

I would like to inform you that after following all instructions, tips and advises, I successfully implemented the jrdc2 in my laptop as experimental server.

I made changes in this part considering that the available file in me is .22-bin (not.27-bin) but still works
'#AdditionalJar: mysql-connector-java-5.1.27-bin
#AdditionalJar: mysql-connector-java-5.1.22-bin

I was not able to test this after your advice due to my busy activity in other obligation.

Thank you and more power to all of you.

Best regards.
 
Last edited:
Upvote 0
Top