Android Tutorial Connect Android to MySQL Database Tutorial

Status
Not open for further replies.
A new more powerful framework is now available: jRDC2.

This tutorial explains the basic concepts required for creating a connection between Android device and a remote server. In this case it is a MySQL database.
A tutorial for connecting to SQL Server is available here.

Android cannot connect directly to the database server (search for JdbcSQL). Therefore we need to create a simple web service that will pass the requests to the database and will return the response.

For this example I've created a new database that lists the countries population. The data was derived from a UN database.

The database includes a single table named "countries" with three columns:
mysql_1.png

PHP Script

The web service is made of a single PHP script running on the same server.
You have several options for the web service implementation. You can create several prepared statements which will be filled with parameters passed in the request.
Or you can take the complete query from the request and pass it to the database server.
There are high security risks with issuing queries received from an unknown user. If your service is public then you will probably want to choose the first option.
You should also make sure to correctly escape the parameters. See this php method: PHP: mysql_real_escape_string - Manual

In our example I've chosen the second option which takes the query and passes it to the database directly.
I've restricted the database user to SELECT queries (in MySQL configuration).

The PHP code:
PHP:
<?php

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysqli_connect($databasehost,$databaseusername,$databasepassword, $databasename) or die(mysqli_error($con));
mysqli_set_charset ($con , "utf8");
$query = file_get_contents("php://input");
$sth = mysqli_query($con, $query);

if (mysqli_errno($con)) {
   header("HTTP/1.1 500 Internal Server Error");
   echo $query.'\n';
   echo mysqli_error($con);
}
else
{
   $rows = array();
   while($r = mysqli_fetch_assoc($sth)) {
     $rows[] = $r;
   }
   $res = json_encode($rows);
    echo $res;
    mysqli_free_result($sth);
}
mysqli_close($con);
?>
The script takes the query from the POST data and submits it to the database.
The result is then written in JSON format.

B4A code

Our application displays a list of countries. When the user presses on a country, its population is retrieved from the database and displayed.

upload_2016-8-3_13-4-25.png



The code sends the query and then when the result arrives the JSON is parsed and displayed.
Note that in this case the JSON object is made of an array of one or more maps.

Edit: Code updated to use OkHttpUtils2.
 

Attachments

  • MySQL.zip
    8 KB · Views: 17,464
Last edited:

cooperlegend

Active Member
Licensed User
Longtime User
Hi, I have been using this code to work with MySql and have found it very good when I have a strong connection like Wi-Fi

But when I have a weak connection over gprs I have found that I sometimes get some strange error messages appearing. Whilst I accept that comms over gprs will not always be present and the occasional out of range is not a big issue for me, I fear that sometimes I might be getting only half the data sent or received and that this is causing the error messages.

Are there any further refinements that I could apply to this code to make this more robust?

It would be better if this either passed fully successfully or failed fully (with no data processed) with no errors shown...
 

cooperlegend

Active Member
Licensed User
Longtime User
Thanks Erel, That was what I was hoping you would say :)

Is this quite straightforward?

Give me some quick pointers which bits to replace if you don't mind.




Also going to upgrade to 2.02 tonight thanks for your help also on the ScrollView, that now works great....

Hoping to do a big WM6.5 to Android application Conversion for a long term customer shortly, so going to stick with B4A for quite a while now, thanks for an excellent set of tools.
 

shekman

Member
Licensed User
Longtime User
Is it possible to get a bit more tips for the php end or possibly the entire script on this tut? I have a simple db created in php mysql on my server and I am trying to use the same concept with the app, only it will be wineries and countries instead of countries and population. The error I am getting here: http://greengigs.biz/testdb/connect.php is:

Warning: Cannot modify header information - headers already sent by (output started at /home/content/11/9148411/html/testdb/connect.php:1) in /home/content/11/9148411/html/testdb/connect.php on line 13
\nQuery was empty Wineries Listed:



Many Many Thanks in Advance :sign0013:
 

phast

Member
Licensed User
Longtime User
sorry this may be another stupid question. Currently here is my code

B4X:
Sub chkDB_tick
Dim rlat As String
Dim rlon As String
rlat="40.518"
rlon="-78.395"
ExecuteRemoteQuery("SELECT ((ACOS(SIN('" & rlat & "' * PI() / 180) * SIN(`rlat` * PI() / 180) + COS('" & rlat & "' * PI() / 180) * COS(`rlat` * PI() / 180) * COS(('" & rlon & "' - `rlon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM `app_trax2` HAVING distance<='400' ORDER BY distance ASC", 1)
'Msgbox("i got here","test")
End Sub
Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
    'Msgbox("i got here2","test")
   Dim req As HttpRequest
    req.InitializePost2("URL REMOVED", Query.GetBytes("UTF8"))
    hc.Execute(req, TaskId)
End Sub
Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    Msgbox("i got here3","test")
   Dim res As String
    res = Response.GetString("UTF8")
    Log("Response from server: " & res)
    Dim parser As JSONParser
    parser.Initialize(res)
    Select TaskId
        Case 1
            'add the countries to the ListView
            Dim countries As List
            countries = parser.NextArray 'returns a list with maps
            For i = 0 To countries.Size - 1
         Dim m As Map
                m = countries.Get(i)
                rname = m.Get("name")
            Next
            ProgressDialogHide
    End Select
    Response.Release
   Msgbox(rname,"test")
End Sub
Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Msgbox("i got here4","test")
   Msgbox("Error: " & Reason & ", StatusCode: " & StatusCode,"TEST")
   Log("Error: " & Reason & ", StatusCode: " & StatusCode)
    If Response <> Null Then
        Log(Response.GetString("UTF8"))
        Response.Release
    End If
    ProgressDialogHide
End Sub


I can get to "I got here" and "I got here 2" however i cannot get a response success or error I am very confused at this point.
 

phast

Member
Licensed User
Longtime User
Incase anyone else is having this issue, I was able to solve it by changing this:

B4X:
rlat="40.518"
rlon="-78.395"

to:

B4X:
rlat= 40.518
rlon= -78.395
 

Dogbonesix

Active Member
Licensed User
Longtime User
No Error, just does not work

I would like to connect to MySql and changed a few lines on the sample that would conform to my data.

I created a slightly different PHP script to connect to my database (I hope).

I just Remmed out Sample code in the Sub below and replaced my code just below the original code.
Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
Label3.Text = "At Query"
Dim req As HttpRequest
'req.InitializePost2("http://www.b4x.com/android/countries.php", Query.GetBytes("UTF8"))
req.InitializePost2("http://www.dogbone6.org/myphp/BillDesk.php", Query.GetBytes("UTF8"))
hc.Execute(req, TaskId)
Dim ufo As Char
ufo = "0"
End Sub

But req does not change - see below.
"req = anywheresoftware.b4a.http.HttpClientWrapper
$HttpUriRequestWrapper@408ff540"

Hey, I am having fun and probably way off base but any help would be appreciated.
Thanks
 

Dogbonesix

Active Member
Licensed User
Longtime User
Still Lost in MySql

In the MySql sample I do not know how to change the req variable.

I would guess that req should be initialized with the code below:
req.InitializePost2("http://www.b4x.com/android/countries.php", Query.GetBytes("UTF8"))

But what I get is: "anywheresoftware.b4a.http.HttpClientWrapper$HttpUriRequestWrapper@408ff540"

Or how do I change the HttpRequest object?

Still having fun with B4A...

Thanks
 

Dogbonesix

Active Member
Licensed User
Longtime User
Apache error

The MySql sample was working - I made no changes but now there is another error "org.apache.http.conn.HttpHostConnectException:Connection to http://127.0.01:1234refused, StatusCode: -1"

The error occurs in the: Sub hc_ResponseError

The word "apache" is nowhere in any code that I know of. The connection to Mysql is very difficult to understand and very critical to my application.

It appears that the php script (sorta) replaces INet in VB6 because that is where the database and login occurs. But, HttpClient and the variable and 'hc' and 'req' are a mystery to me.

hc.Initialize("hc")????

Dim req As HttpRequest
No matter how to change "req.InitializePost2"http://www.b4x.com it /android/countries.php..." it never changes!

Any suggestions, help, insight other samples would be appraciated.
 

Dogbonesix

Active Member
Licensed User
Longtime User
Same Error on Currency Converter

I still get the same error even using the Currency Converter:

"org.apache.http.conn.HttpHostConnectException:Con nection to http://127.0.01:1234refused, StatusCode: -1"

when I run the Currency Convert app.

Something is fundamentally in error - I have three app that were working without error and now all three have the same error!
 

manuraphy

New Member
can u plz provide the code in jsp . i"m using apache tomcat server in my local host
i want to connect to my MySql database in server from the android app .
i also like to know how we can pass a block of data like a table full from our server to android app.
 
Last edited:

Dogbonesix

Active Member
Licensed User
Longtime User
127.0.0.1

127.0.0.1 is Localhost...I guess on the local machine...

I get the 127.0.0.1 error when I am using my Tablet but not from my Cell phone - so I have some insight to the problem. I have searched and searched but I don't know where to change it - on either my code or on the Tablet. At the moment it is not critical as the target for my app was the Cell phone anyway. But, I will find a solution.
 
Status
Not open for further replies.
Top