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

Gbenga Odumosu

Member
Licensed User
Longtime User
hi,
pls i got this error when i tried to run this program on my android tablet ("alcatel)

javax.net.ssl.SSLPeerUnverifiedException: No peer certificate

what i am not doing
gbenga
 

Shubhankar

Member
Licensed User
If I want to get more than 2 responses form the server do I still use twolines as a custom type?
I tried using five lines but it doesnt work
Is there a generic type i can use for multiple return values
 

Gbenga Odumosu

Member
Licensed User
Longtime User
thank
This means that your tablet doesn't recognize the certificate installed on the b4x.com server.

It is not really related to this example. You can install it on your own server and it will work.


thanks erel for your reply. please how do i register my tablet to be able to work with the b4a certificate or it means it can never work
so sorry if i sound too mediocre. i am just learning
 

Argonc

Member
Licensed User
Longtime User
Hello friends B4A.

I am trying to download from MySql image (Img = mediumblob around 100kB).
In PHP is file transformed into base64.
It's probably a mistake to work in B4A with res=Job.GetString2("UTF-8") as string?
It is a bug in PHP or B4A?


PHP Code:
PHP:
        $ID_POI = $_GET["ID_POI"];
        $q = mysql_query("select Img from POI where id_poi=$ID_POI");
        $rows = array();
        $r = mysql_fetch_assoc($q);
        while($r = mysql_fetch_assoc($q))
        {          
            $rows[] = base64_encode($r);       
        }
print json_encode($rows);

B4A Code:
B4X:
  Dim res As String
  res=Job.GetString2("UTF-8")
Log("Response from server:" & res&";")

Response: Response from server:[];


I do not know how to transfer answer from PHP so that it worked like ReadBlob from SQLite :(

B4X:
Sub ReadBlob
    Dim Cursor1 As Cursor
    'Using ExecQuery2 is safer as it escapes special characters automatically.
    'In this case it doesn't really matter.
    Cursor1 = SQL1.ExecQuery2("SELECT image FROM table2 WHERE name = ?", Array As String("smiley"))
    Cursor1.Position = 0
    Dim Buffer() As Byte 'declare an empty byte array
    Buffer = Cursor1.GetBlob("image")
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
   
    Dim Bitmap1 As Bitmap
    Bitmap1.Initialize2(InputStream1)
    InputStream1.Close
    Activity.SetBackgroundImage(Bitmap1)
End Sub

Any idea please?
 

Argonc

Member
Licensed User
Longtime User

DonManfred

Expert
Licensed User
Longtime User
B4A Code:
where is the code which do the request? I guess the code you posted is part of the JobDone sub....

Post a more complete (including logs which url is used really (add log commands to know what you are sending)
Do debug-output in php which id you really get.

Based on the php code you posted i guess the id (coming from b4a) is empty...

Without seeing a complete code we hardly can help
 

Argonc

Member
Licensed User
Longtime User
where is the code which do the request? I guess the code you posted is part of the JobDone sub....

Post a more complete (including logs which url is used really (add log commands to know what you are sending)
Do debug-output in php which id you really get.

Based on the php code you posted i guess the id (coming from b4a) is empty...

Without seeing a complete code we hardly can help

Yes you're right. I use JobDone sub.

Request is easy:
B4X:
    Dim id_poiD As Int
    id_poiD=34546
   
    Dim job666 As HttpJob
    job.Initialize("job", Me)
    job.download2("http://itanalytik.cz/Elem/ElemScript.php", _
                    Array As String("action", "getStamp","ID_POI",id_poiD))



and response:
B4X:
Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
        Dim res As String
   
        res=Job.GetString2("UTF-8")
        Log("*********************"  & Job.JobName)
        Log("Response from server:" & res&";")

and log:
B4X:
*********************job
Response from server:[];

Complete code is really long and everything else works well.
 

DonManfred

Expert
Licensed User
Longtime User
$q = mysql_query("select Img from POI where id_poi=$ID_POI");
$rows = array();
$r = mysql_fetch_assoc($q);
while(
$r = mysql_fetch_assoc($q))
{
$rows[] = base64_encode($r);
}
print
json_encode($rows);

Explanation:
Querying the database and getting an result-Cursor ($q)
PHP:
$q = mysql_query("select Img from POI where id_poi=$ID_POI");
Create a new Array
PHP:
 $rows = array();
attention!
Get the FIRST result from the cursor and store it in $r
PHP:
$r = mysql_fetch_assoc($q);
Iterate through the rest of the resultcursor-contents and fill the resultarray.
Note: it will return the results No 2,3,4, and so on.... If your query only have ONE result then this will not do anything as you already got the result (and you just discarded it)
PHP:
 while($r = mysql_fetch_assoc($q))
        {     
            $rows[] = base64_encode($r);  
        }

at the end you´ll get an empty result-array

Solution:

PHP:
         $ID_POI = $_GET["ID_POI"];
        $q = mysql_query("select Img from POI where id_poi=$ID_POI");
        $rows = array();
        #$r = mysql_fetch_assoc($q); # This line is not needed.... Just start with the while
        while($r = mysql_fetch_assoc($q))
        {         
            # Handle the results. Starting from 1st result till end of cursor-content.
            $rows[] = base64_encode($r);      
        }
print json_encode($rows);
 

Argonc

Member
Licensed User
Longtime User
Explanation:
Querying the database and getting an result-Cursor ($q)
PHP:
$q = mysql_query("select Img from POI where id_poi=$ID_POI");
Create a new Array
PHP:
 $rows = array();
attention!
Get the FIRST result from the cursor and store it in $r
PHP:
$r = mysql_fetch_assoc($q);
Iterate through the rest of the resultcursor-contents and fill the resultarray.
Note: it will return the results No 2,3,4, and so on.... If your query only have ONE result then this will not do anything as you already got the result (and you just discarded it)
PHP:
 while($r = mysql_fetch_assoc($q))
        {    
            $rows[] = base64_encode($r); 
        }

at the end you´ll get an empty result-array

Solution:

PHP:
         $ID_POI = $_GET["ID_POI"];
        $q = mysql_query("select Img from POI where id_poi=$ID_POI");
        $rows = array();
        #$r = mysql_fetch_assoc($q); # This line is not needed.... Just start with the while
        while($r = mysql_fetch_assoc($q))
        {        
            # Handle the results. Starting from 1st result till end of cursor-content.
            $rows[] = base64_encode($r);     
        }
print json_encode($rows);

Thx DonManfred. I try it but response is:

B4X:
Response from server:<br />
<b>Warning</b>:  base64_encode() expects parameter 1 to be string, array given in <b>/data/www/itanalytik.cz/www.itanalytik.cz/Elem/ElemScript.php</b> on line <b>68</b><br />
[null];

So now I must write correct type in B4A? Not string?
 

Argonc

Member
Licensed User
Longtime User
PHP:
$rows[] = base64_encode($r["Img"]);
You're a genius. It works! something it answer:)
B4X:
****************************************************Response from server:["iVBORw0KGgoAAAANSUhEUgAAAasAAAGACAMAAAAkpL2qAAACu1BMVEVHcEwHBgMODAcMCgUAAAACAQABAAAAAAAAAAAAAAANCwcPDQe7rogMCgWuoH0SDwgWE...

What type of parameter is Img in B4A?

B4X:
Dim Bitmap1 As Bitmap
?
 

DonManfred

Expert
Licensed User
Longtime User
What type of parameter is Img in B4A?
Decode it and save it to disc. If it is an jpg save it as img.jpg, it it is an png save it as img.png.
Load the image then into a bitmap or imageview
But this does NOT belong to this thread.

For ANY new question please create a new thread in the questionsforum. One thread for one question.
 

karmba_a

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.

http://madina.medu.sa/NotifWeb/Test/countries.php

where is wrong
 

MohammadNew

Active Member
Licensed User
Longtime User
You're a genius. It works! something it answer:)
B4X:
****************************************************Response from server:["iVBORw0KGgoAAAANSUhEUgAAAasAAAGACAMAAAAkpL2qAAACu1BMVEVHcEwHBgMODAcMCgUAAAACAQABAAAAAAAAAAAAAAANCwcPDQe7rogMCgWuoH0SDwgWE...

What type of parameter is Img in B4A?

B4X:
Dim Bitmap1 As Bitmap
?

you need library StringUtils

B4X:
Dim buffer() As Byte
Dim b As Bitmap
Dim su As StringUtils               
buffer = su.DecodeBase64(put here your source base64 image string)
Dim In As InputStream
In.InitializeFromBytesArray(buffer, 0, buffer.Length)
b.Initialize2(In)
ImageView1.Bitmap = b
 
Status
Not open for further replies.
Top