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:

Erel

B4X founder
Staff member
Licensed User
Longtime User
ResponseError: The resource could not be loaded because the App Transport Security policy requires the use of a secure connection., status code: 0
The resource could not be loaded because the App Transport Security policy requires the use of a secure connection.
Please start a new thread for this in B4i forum.
 

DonManfred

Expert
Licensed User
Longtime User
Hi Erel,

How to modify the code in the tutorial to send an update to the database?
1. this is a comunity forum. Don´t limit your question to a single member.
2. Always create a new thread in the questions forum for any question you have.
 

Walter Scafati

Active Member
Licensed User
Longtime User
Hi, I have a question about the connection to remote DB mySQL: I realized a App able to read and write a table on remote mySQL DB located on my server.
In the table there is also a BLOB field for the image. I can send the Data fields completed of blob field to DB on the server converting the field to string data

Buffer2 = cursoreTmp.GetBlob("image")
If Buffer2 <> Null Then encoded = su.EncodeBase64(Buffer2) 'data is a bytes array
ExecuteRemoteQuery2(ADati, encoded)


Sub ExecuteRemoteQuery2(par() As String, Buffer2 As String)
Dim job As HttpJob
Dim myInd As Int

job.Initialize("q000", Me)
job.PostString("http://" & ip & "/" & page & ".php?par1=" & par(1) & "&par2=4&par3=" & par(2) & "&par4=" & par(2).length, Buffer2)
End Sub


The problem is when I try to request the data from the server, only when the BLOB field in not empty.

In the JobDone sub I have the error in the follow instruction (parser.NextArray)

Dim MYDATA As List
MYDATA = parser.NextArray 'returns a list with maps


I think I'm not correct conversion of the BLOB field in the string in the php files on the server.

What do you think? Where can be the problem?

Thanks.
 

RichB

New Member
Licensed User
Longtime User
Hi, my sql queries and php work fine over the local network and if I specify the actual IP address of the server. But they won't work when I use a http://www.mysite.com address?

Any ideas why? I see in the example it works fine using "https://www.b4x.com/android/countries.php" so why does mine only work when I use http://82.x.x.x.x/myphpfile.php.

Thanks in advance for any help.

EDIT: A little more info, it doesn't give me an error. It is just that the response from the server appears to be in HTML
 

Oke

Member
Licensed User
Longtime User
When trying to access there is an error message: unauthorized, how do I fix it ?, thx
 

Myr0n

Active Member
Licensed User
Longtime User
Can you pos the error as appears in the log in a new thread please
 
Status
Not open for further replies.
Top