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

Mahares

Expert
Licensed User
Longtime User
Suppose my date format is yyyy-MM-dd and the date field (SQLiteDate) in the SQLite database table has the same
format yyyy-MM-dd. But I want the query to display dd-mm-yyyy in the date field on device. Here is the code using strftime:
B4X:
  MyQuery="SELECT  strftime('%d-%m-%Y', strftime('%s',SQLiteDate), 'unixepoch') AS DT FROM results "
or you can use the substr function:
B4X:
MyQuery="SELECT  substr(SQLiteDate,9,2) || '-' || substr(SQLiteDate,6,2) || '-' || substr(SQLiteDate,1,4) AS DT FROM results "

for example, if the date stored in table is: 2016-03-10, it will be displayed as: 10-03-2016
 

dfrutos

Member
Licensed User
Longtime User
Hello, i updated B4a to last version 5.80

When i try to compile app developemented in b4a old version now i have an error:


src\b4a\visitas\httputils2service.java:208: cannot access org.apache.http.client.ClientProtocolException
class file for org.apache.http.client.ClientProtocolException not found
_hc.Initialize("hc");

I use HTTP, JSON, StringUtils libs, and i have HttpUtils2Service service and HttpJob Class...

Also, i have an error in next line of HttpUtils2Service:


B4A version: 5.80
Parsing code. (0.15s)
Compiling code. (0.55s)
Compiling layouts code. (0.14s)
Generating R file. (0.05s)
Compiling generated Java code. Error
B4A line: 55
Log(Response.GetString(\
javac 1.6.0_25
src\b4a\visitas\httputils2service.java:158: cannot access org.apache.http.ParseException
class file for org.apache.http.ParseException not found
anywheresoftware.b4a.keywords.Common.Log(_response.GetString("UTF8"));

Thank you
Damián.
 

vbmundo

Well-Known Member
Licensed User
Hi,

I have this error when the App is compiled

B4X:
Log(Response.GetString(\
javac 1.8.0_91
src\b4a\example\httputils2service.java:176: error: cannot access ParseException
anywheresoftware.b4a.keywords.Common.Log(_response.GetString("UTF8"));
                                                            ^
  class file for org.apache.http.ParseException not found
 

Technofresh

New Member
Licensed User
Hi , please can you see my attached error. I copied the HttpJob class and HttpUtils2Service service to my project. Its first B4A project/android project im working on. Can anyone please explain to what the problem is and/or how to fix it .... See attached img. Thank You
 

Attachments

  • error.png
    error.png
    84 KB · Views: 366

rscheel

Well-Known Member
Licensed User
Longtime User
The tutorial in the first post was updated. The B4A code now uses OkHttpUtils2. The PHP code is based on mysqli instead of the deprecated mysql extension.

Interestingly the upgrade post, that of sending the Query ready for the app is very good and lot of time is saved, but after having used similar to this in time method, I realized that it has serious security problems and if someone wants to get into your app they're giving the name of the table to which you want to access the remote bd bone you have everything to make an insert, update, delete or select, so it is better to have these direct data in php so a higher level of safety is achieved.

Leave the Spanish text like if you do not understand my English.

Es interesante la actualización del post, esto de enviar los Query listos de la app es muy bueno y se ahorra gran cantidad de tiempo, pero después de haber usado un método parecido a este durante tiempo, me di cuenta que tiene graves problemas de seguridad ya que si alguien quiere entrar a tu app les estas dando el nombre de la tabla a la cual deseas acceder a la bd remota osea tienes todo para hacer una insert, update, delete o select, por eso es mejor tener esos datos directos en el php así se obtiene un nivel de seguridad mayor.
 

Walter Scafati

Active Member
Licensed User
Longtime User
Good morning,
I tried your example and this works good, but when I change with my MySQL Database parameters I receive a empty data.
This is the debug log:

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Response from server: []

Where can be the error? How can I test my query directly on the server using the php page?
Thanks.
 

Walter Scafati

Active Member
Licensed User
Longtime User
Hi Herel,
looking in the server I found that the server is MySQL, in this server already run a www Site that using this database with php pages.

I tried the query on phpMyAdmin interface and this works. I wanted to know if is there a way to test the php page + query using a address field on the Internet browser.
 

Walter Scafati

Active Member
Licensed User
Longtime User
I tried to replace in the php page
$query = file_get_contents("php://input");
with
$query = "SELECT Name, Id FROM MyTable ORDER BY Name";

and running the page on the browser I have the correct result of query.
Is possible that the php page can't receive the query field?
 

Walter Scafati

Active Member
Licensed User
Longtime User
These the message in the error log:

[Tue Sep 20 10:47:44 2016] [warn] [client 5.90.166.73] mod_fcgid: stderr: PHP Warning: mysqli_query(): Empty query in /var/www/vhosts/mioserver.com/httpdocs/android.php on line 16
[Tue Sep 20 10:47:44 2016] [warn] [client 5.90.166.73] mod_fcgid: stderr: PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /var/www/vhosts/mioserver.com/httpdocs/android.php on line 26
[Tue Sep 20 10:47:44 2016] [warn] [client 5.90.166.73] mod_fcgid: stderr: PHP Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /var/www/vhosts/mioserver.com/httpdocs/android.php on line 31
 
Status
Not open for further replies.
Top