Android Tutorial Connect Android to MySQL Database Tutorial

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:

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

$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.

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.

 

[dunski]

If it prints the PHP script then your server is misconfigured. Instead of executing the PHP it treats it as a text file.

Hi Erel,
Thanks for getting back to me. Do you mean php is configured incorrectly on the server?
would you mind looking at the phpinfo.php read out from my server please and let me know what you think.

SystemLinux nutmeg.hosts.co.uk 2.6.18-348.12.1.el5 #1 SMP Wed Jul 10 05:28:41 EDT 2013 x86_64
Build DateJan 16 2014 12:47:47
Server APICGI/FastCGI
Virtual Directory Supportdisabled
Configuration File (php.ini) Path/etc/php55
Loaded Configuration File/etc/php55/php.ini
Scan this dir for additional .ini files/etc/php55/conf.d
Additional .ini files parsed/etc/php55/conf.d/00-site.ini, /etc/php55/conf.d/01-ioncube.ini
PHP API20121113
PHP Extension20121212
Zend Extension220121212
Zend Extension BuildAPI220121212,NTS
PHP Extension BuildAPI20121212,NTS
Debug Buildno
Thread Safetydisabled
Zend Signal Handlingdisabled
Zend Memory Managerenabled
Zend Multibyte Supportprovided by mbstring
IPv6 Supportenabled
DTrace Supportenabled
Registered PHP Streamshttps, ftps, compress.zlib, compress.bzip2, php, file, glob, data, http, ftp, phar, zip
Registered Stream Socket Transportstcp, udp, unix, udg, ssl, sslv3, sslv2, tls
Registered Stream Filterszlib.*, bzip2.*, convert.iconv.*, mcrypt.*, mdecrypt.*, string.rot13, string.toupper, string.tolower, string.strip_tags, convert.*, consumed, dechunk

Thank you so much in advance

 

[dunski]

Hi Erel,
Thanks for getting back to me. Do you mean php is configured incorrectly on the server?
would you mind looking at the phpinfo.php read out from my server please and let me know what you think.



Thank you so much in advance

There is a lot more to the read out than this. I just didn't want to clog up the thread with rubbish.
I suppose I just need to know what would be stopping it from executing a script.

 

[Erel]

There error is not in the php configuration. It is in the server itself. Instead of executing the PHP script it returns it as a text file.

 

[dunski]

Hi Erel,
It is on a shared hosting platform, where I don't have much control of the server. What do I have to ask the host to do?

Thanks in advance

 

[Erel]

What is the error message again?

 

[dunski]

It just spits this back into the log...



startService: class com.repbuddy.httputils2service
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Response from server: <?



$databasehost = "*********";

$databasename = "LogCat connected to: B4A-Bridge: samsung GT-I9205-357426051908784
--------- beginning of /dev/log/main
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (service1) Create **
** Service (service1) Start **
Connected to B4A-Bridge (Wifi)
** Activity (main) Pause, UserClosed = true **
libcore.io.ErrnoException: recvfrom failed: ECONNRESET (Connection reset by peer)
sending message to waiting queue (CallSubDelayed - UpdateStatus)
Connected to B4A-Bridge (Wifi)
sending message to waiting queue (CallSubDelayed - UpdateStatus)
Installing file.
PackageAdded: package:com.repbuddy
** Activity (main) Create, isFirst = true **
(Main, 75) FullScreen or IncludeTitle properties in layout file do not match the activity attributes settings. (warning #1004)
** Activity (main) Resume **
(Main, 105) FullScreen or IncludeTitle properties in layout file do not match the activity attributes settings. (warning #1004)
startService: class com.repbuddy.httputils2service
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Response from server: <?



$databasehost = "*********";

$databasename = "*********";

$databaseusername ="*********";

$databasepassword = "*********";



$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());

mysql_select_db($databasename) or die(mysql_error());

mysql_query("SET CHARACTER SET utf8");

$query = file_get_contents("php://input");

$sth = mysql_query($query);



if (mysql_errno()) {

header("HTTP/1.1 500 Internal Server Error");

echo $query.'\n';

echo mysql_error();

}

else

{

$rows = array();

while($r = mysql_fetch_assoc($sth)) {

$rows[] = $r;

}

print json_encode($rows);

}

?>
main_jobdone (B4A line: 252)
elist = parser.NextArray 'returns a list with maps
java.lang.RuntimeException: Object should first be initialized (JSONParser).
at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:46)
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:57)
at com.repbuddy.main._jobdone(main.java:1085)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at anywheresoftware.b4a.keywords.Common$5.run(Common.java:958)
at android.os.Handler.handleCallback(Handler.java:725)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:5296)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1102)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:869)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: Object should first be initialized (JSONParser).
";

$databaseusername ="*********";

$databasepassword = "*********";



$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());

mysql_select_db($databasename) or die(mysql_error());

mysql_query("SET CHARACTER SET utf8");

$query = file_get_contents("php://input");

$sth = mysql_query($query);



if (mysql_errno()) {

header("HTTP/1.1 500 Internal Server Error");

echo $query.'\n';

echo mysql_error();

}

else

{

$rows = array();

while($r = mysql_fetch_assoc($sth)) {

$rows[] = $r;

}

print json_encode($rows);

}

?>
main_jobdone (B4A line: 252)
elist = parser.NextArray 'returns a list with maps
java.lang.RuntimeException: Object should first be initialized (JSONParser).
at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:46)
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:57)
at com.repbuddy.main._jobdone(main.java:1085)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at anywheresoftware.b4a.keywords.Common$5.run(Common.java:958)
at android.os.Handler.handleCallback(Handler.java:725)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:5296)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1102)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:869)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: Object should first be initialized (JSONParser).

 

[andre.astafieff]

Hello, I'm trying an insert, but nothing happens, it does not get the error message.

instruction:ExecuteCommandQuery("INSERT INTO cidades_br.teste2(foto) VALUES ('teste');",EXECUTA_COMANDO)

 

[Erel]

@dunski this means that your server is not running the PHP script and instead returns its text. The hosting company should know how to handle it.

@andre.astafieff have you checked whether the data was added in the database? An INSERT command will not return any value.

 

[andre.astafieff]

@dunski this means that your server is not running the PHP script and instead returns its text. The hosting company should know how to handle it.

@andre.astafieff have you checked whether the data was added in the database? An INSERT command will not return any value.

yes, I checked, but nothing is inserted ...
I will review all the code again observe if not forgotten something.

 

[dunski]

@dunski this means that your server is not running the PHP script and instead returns its text. The hosting company should know how to handle it.

@andre.astafieff have you checked whether the data was added in the database? An INSERT command will not return any value.

Hi Erel,
Thanks again for getting back to me.
The hosting company said the only thing that would have changed since I had the script working before on their server would be the php version which is now at 5.5.6. I tried downgrading it to 5.4.22 but I got the same result.
Any other Ideas?
Thanks for your help on this.

 

[Erel]

Try to run the script from the browser. Do you see the script itself?

 

[dunski]

Try to run the script from the browser. Do you see the script itself?

Hi Erel
I got it working.
I just played around with the script. Striped it down to its most basic until it worked.

So this is what I got to work, Although it is missing the errno from mySql it works and to be honest I dont know why this works over the previous one. Would you know?

<?php
mysql_connect("***","***","***");
mysql_select_db("***");

mysql_query("SET CHARACTER SET utf8");
$query = file_get_contents("php://input");

$sql=mysql_query($query);
while($row=mysql_fetch_assoc($sql))
$output[]=$row;
print(json_encode($output));
mysql_close();
?>

Thanks for all you help
Dunski

 

[dunski]

This solution is not really any good for me actually because I need the error handling from the database.
Erel do you you have any Idea why I can't include the error handling in the script.
When I run it in the browser it just returns nothing, a white screen.
Please help

 

[dunski]

Ok It seems I need to use mySqli as I am using version 5.5 MySql database.

I'm not too hot on PHP but this script needs to be updated for people using MySql 5.5

If anyone knows how to do that It would be helping me and anyone else wanting to use this script on mySql 5.5.

Thanks in advance

 

[Erel]

It should work with MySQL 5.5. Starting from PHP 5.5. You might see warnings about usage of deprecated messages. You can configure PHP to ignore these warnings.

 

[dunski]

It should work with MySQL 5.5. Starting from PHP 5.5. You might see warnings about usage of deprecated messages. You can configure PHP to ignore these warnings.

Thank you so much Erel!
I finally got it working perfect.
I found this line of php on stack overflow that ignores warnings about usage of deprecated messages

error_reporting(E_ALL ^ E_NOTICE ^ E_DEPRECATED);

Did the trick

So This is exactly how it works if you are using mysql 5.5 and php 5.5 also...

<?php
error_reporting(E_ALL ^ E_NOTICE ^ E_DEPRECATED);

$con = mysql_connect("Host","UserName","Password") or die(mysql_error());
mysql_select_db("DBName") or die(mysql_error());

mysql_query("SET CHARACTER SET utf8");

$query = file_get_contents("php://input");

$sth = mysql_query($query);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

 

[asawyer13]

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.

Can you tell me how you are zipping and encrypting in the php code and then unzipping and decrypting on the b4a side?

 

[jalle007]

For BLOB fields I recomend to you to see this:

http://www.b4x.com/forum/basic4android-getting-started-tutorials/6736-sql-tutorial.html#post39108

There you have:

Insert BLOB

Sub InsertBlob
    'convert the image file to a bytes array
    Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(File.DirAssets, "smiley.gif")
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
    File.Copy2(InputStream1, OutputStream1)
    Dim Buffer() As Byte 'declares an empty array
    Buffer = OutputStream1.ToBytesArray
  
    'write the image to the database
    SQL1.ExecNonQuery2("INSERT INTO table2 VALUES('smiley', ?)", Array As Object(Buffer))
End Sub

Read BLOB

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

Isn't this related to SQLlite ?

I need to read images stored in BLOB field in MySQL db (not SQLite) located remotely.
Surprisingly could not find any samples , and the one I tried did not get me any results

Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
    Dim res As String

        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
      
        Select Job.jobname
            Case jobname
                Dim articles As List
                articles = parser.NextArray
                For i = 0 To articles.Size - 1
                    Dim m As Map
                    m = articles.Get(i)
                    m.
                    Dim art As Article
                    art.image= m.Get("image")
                    art.id= m.Get("id")
                    art.title= m.Get("title")
                    art.date1= m.Get("date")
                    art.categoryid= m.Get("categoryid")
                    art.imageid= m.Get("imageid")

                    art.content=m.Get("content")
                  
                    'ListView1.AddTwoLines2(tl.First, tl.Second, tl)
                Next
          
        End Select
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub

The problem is that for 'image' field I always get result null in res var,
but I am 100% sure that the image is already stored in the database.

So how do I retrieve image from db?

 

[jalle007]

Hi

do you have sample of that code ?

thanks!!!

thanks EREl my program now load and download images to and from mysql
greetings from colombia
:sign0098:

 

[Erel]

The PHP script will not handle blobs correctly. You will need to modify it and encode the blob with base64 string before it is sent. Then in the Android code you can decode it with StringUtils.