Android Tutorial Connect Android to MySQL Database Tutorial

Discussion in 'Tutorials & Examples' started by Erel, Mar 21, 2011.

  1. Erel

    Erel Administrator Staff Member Licensed User

    A new more powerful framework is now available: Remote Database Connector.

    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. 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:
    [​IMG]
    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.
     

    Attached Files:

    Last edited: Aug 5, 2016
  2. alwaysbusy

    alwaysbusy Well-Known Member Licensed User

    Thanks Erel! Great tutorial
     
  3. JMB

    JMB Active Member Licensed 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.
     
  4. ruy

    ruy Member Licensed 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.basic4ppc.com/android/countries.php and the one that runs at mt server, though, it refuses to continue.

    :sign0085:What am I doing wrong?

    Ruy
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Looking at the logcat of your server response lead me to the solution:
    [​IMG]

    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:
    Code:
    Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
        
    Dim res As String
        res = Response.GetString(
    "UTF8")
        res = res.SubString(
    1'removes first character
     
    Massimo Linossi, shirin and juhardi like this.
  6. ruy

    ruy Member Licensed User

    Excellent, this solved the problem!

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

    Ruy
     
  7. ruy

    ruy Member Licensed 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:
     
  8. Erel

    Erel Administrator Staff Member Licensed User

  9. ruy

    ruy Member Licensed User

    I had already tried that already to no avail :-(
     
  10. ruy

    ruy Member Licensed User

    I tried both options: CONVERT(expr,utf8), CONVERT(expr USING utf8)
     
  11. Erel

    Erel Administrator Staff Member Licensed User

    As I wrote in my previous post you should first make sure that the data was inserted correctly and can be extracted from the database.
     
  12. ruy

    ruy Member Licensed 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: Apr 3, 2011
  13. Erel

    Erel Administrator Staff Member Licensed User

    Instead of parsing the JSON text, log it and see if the data arrived correctly. If not then it is probably failing in the PHP side.
     
  14. xalion

    xalion Member Licensed 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.
     
  15. Erel

    Erel Administrator Staff Member Licensed User

    I will add support for GZipped streams.
     
  16. Hans Scholten

    Hans Scholten Member Licensed User

    How can I log this

    Hans
     
  17. xalion

    xalion Member Licensed User

    json is text format.you just can log the web result
     
  18. Hans Scholten

    Hans Scholten Member Licensed User

    Xalion,

    That is just the part I do not understand, if I use log(res) I see nothing

    Regards,
    Hans
     
  19. Erel

    Erel Administrator Staff Member Licensed User

    If you see nothing then you probably don't get any response from the server.
     
  20. xalion

    xalion Member Licensed User

    you can use same url in your webbrowse to check if your web server work fine.
    and firebug is a good tool.
     
Loading...