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,474
Last edited:

JMB

Active Member
Licensed User
Longtime User
Thanks

Thanks Erel - have been working on something very similar and was thinking of posting it for the benefit of other users, but this covers things in really useful detail.
 

ruy

Member
Licensed User
Longtime User
MySql example

Hi, I am new to Basic4Droid and while experimenting with this example I find that it works fine as is. But if I try to replicate the php web service in my server and using my database, it simply refuses to work. When it reads the first table (Names & Id's) it sends the following message:

"An error has occurredin sub:main_hc_responsesuccess (B4A line:63)
countries = parser.NextArray 'returns a list with maps
java.lang.RuntimeException: JSON Array expected.
continue?"


It is an exact replica of the files in the tutorial except for quotes missing in the php listing for the MySql server credentials (which I changed for the ones in my server/database) and the address of the php file.

The log file shows identical structures for the one that runs with the original http://www.b4x.com/android/countries.php and the one that runs at mt server, though, it refuses to continue.

:sign0085:What am I doing wrong?

Ruy
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
Looking at the logcat of your server response lead me to the solution:
mysql_bom.png


Instead of a square bracket there is a strange character.
Checking the actual bytes received showed that your server is sending the text as UTF8 with BOM marking. The BOM (byte order marking) are three bytes at the beginning of the stream. I recommend you to configure your server to not send it as it is not expected and may cause all kinds of strange problems.

You can workaround it by removing the first character:
B4X:
Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    Dim res As String
    res = Response.GetString("UTF8")
    res = res.SubString(1) 'removes first character
 

ruy

Member
Licensed User
Longtime User
Excellent, this solved the problem!

Thanks for the prompt response and congratulations for the great service.

Ruy
 

ruy

Member
Licensed User
Longtime User
My database uses Spanish words with accents and Ñ, the fields that contain any of these characters return "NULL" how may I solve this. I guess it is using a different encoding than UTF8 but I cant find the alternatives for this encoding value.

:sign0085:
 

ruy

Member
Licensed User
Longtime User
I tried both options: CONVERT(expr,utf8), CONVERT(expr USING utf8)
 

ruy

Member
Licensed User
Longtime User
The data is correct, I am testing with a working database. It has been in operation for years from a Vb.net application of mine without any problem. The problem appears only in the records that have accented letters (á, é, í, ó & ú) or "ñ", these send a null value, the ones that do not have them are read correctly
 
Last edited:

xalion

Member
Licensed User
Longtime User
Thanks Erel.I just do something like this.
but I have a suggest.
because json is text format. so when datasebase records is too many.
the json is very big.I use zip stream in web server. it decreases dramatic
10 times of original size. and speed the download time.
so we need unzip stream funciton in android client.

by the way. I alse use 3DES to encrypte data.it guarantee network transfers safety.
 

xalion

Member
Licensed User
Longtime User
you can use same url in your webbrowse to check if your web server work fine.
and firebug is a good tool.
 
Status
Not open for further replies.
Top