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

bashka_abdyli

Member
Licensed User
Longtime 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.

Hi Xalion,

Can you share some code on Compressing/uncompressing MySQL results. Thanks in advance.
 

somed3v3loper

Well-Known Member
Licensed User
Longtime User
There are many online tutorials about PHP. Though I recommend you to use RDC instead. It will be easier.
You said in another thread that RDC requires VPS which I do not have .
Can you please advise me what is the best alternative ? I want it to be secure.
Thanks in advance
 

alienhunter

Active Member
Licensed User
Longtime User
Hi to all
i get this error but the php is still working
o_O
any suggestions ?

Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in....

thanks AH
 

alienhunter

Active Member
Licensed User
Longtime User
I guess that you are running PHP 5.4+. They deprecated this method. However it should still work. You can also suppress these messages in the php.ini configuration.

thanks Erel ,
i just started with mysql and xampp, i had my app in the company working with sqlite (10 tablets ) , now i have been told that it would be around 100+ users using the same database so i have to move the database to the next level .
I have it now working like charm with RDC ( i got the transactions times down to 22 -100 ms ;) uploading 8 pictures and text and sqllite settings ....all togehter about 4 mb with a trick ... in a 200+ computer network ) ,
question does the RDC connect to PHP ? i have not seen a example so far
 

zeuspower

Member
Licensed User
Longtime User
Well for me everything worked OK...
I changed the PHP script to connect to my DB,all OK !

Then I did an INSERT query,and also worked OK

BUT,now I want a little Help with 2 things...

1. Correct my INSERT statement so it can be used with values from the device UI (textboxes,spinner...)

B4X:
ExecuteRemoteQuery("INSERT INTO main_table (A_C_Name,Temp,Humid,Date,Time) VALUES ('testing',25,44,'2013-12-31','"&EDT_Time.Text &"'") ", AirCondition_data)

2. THE above statement will be repeated (with different values...)for 192 rows,how I do that easy ?
 
Last edited:

oscar cabrales

Member
Licensed User
Longtime User
hi, i need upload video clips of size greater than 1MB on mysql, how i can do it? because only the code me works with video clips of size less 1MB.


this is me code...

B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim hc As HttpClient
Dim  consultas, insert, foto As Int
    consultas = 2
    insert =3
    foto =4
    Dim Buffer() As Byte 'declares an empty array
    Dim a As StringUtils
    Dim consulfoto As String
    Dim nombrefoto As String
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.
    Dim s As String
   

    Dim ImageView1 As ImageView
   
End Sub

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
        hc.Initialize("hc")
    End If
    Activity.LoadLayout("2")
End Sub
Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
    Dim req As HttpRequest
    req.InitializePost2("http://192.168.0.17/android/codigo1.php", Query.GetBytes("UTF8"))
    hc.Execute(req, TaskId)
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub
Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Log("Error: " & Reason & ", StatusCode: " & StatusCode)
    If Response <> Null Then
        Log(Response.GetString("UTF8"))
        Response.Release
    End If
    ProgressDialogHide
End Sub
Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    Dim res As String
    res = Response.GetString("UTF8")
    Log("Response from server: " & res)
    Dim parser As JSONParser
    parser.Initialize(res)
    Select TaskId
        Case foto
            Dim l As List
            l = parser.NextArray
            If l.Size = 0 Then
                ToastMessageShow("no hay datos", False)
            Else
                  Dim m As Map
                m = l.Get(0)
                consulfoto = m.Get("IMAGE")
                nombrefoto = m.Get("NAME")
                lecturafoto
               
'               
            End If
    End Select
    Response.Release
End Sub

Sub insertofoto
    'convert the image file to a bytes array
    Dim InputStream1 As InputStream
  ' InputStream1 = File.OpenInput(File.DirRootExternal, "upc.3gp")
    InputStream1 = File.OpenInput("/mnt//extSdCard", "upc.3gp")
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(64000)
   
    File.Copy2(InputStream1, OutputStream1)
    Dim Buffer() As Byte 'declares an empty array
   
    Buffer = OutputStream1.ToBytesArray
    Dim sa As StringUtils
    s=sa.EncodeBase64(Buffer)
    End Sub
    Sub lecturafoto
    Dim In  As InputStream
    Dim su As StringUtils
    Dim out As OutputStream   
    Dim b() As Byte
    Dim bmp As Bitmap
    b =su.DecodeBase64(consulfoto)
   
   
    In.InitializeFromBytesArray(b,0,b.Length)
   
' ABRE EL ARCHIVO EN LA SDCAR Y LA ABRE EN EL IMAGEVIEW PERO NO SE PUEDE ABRIR DESDE LA SDCARD
'    bmp.Initialize2(In)
'    out = File.OpenOutput(File.DirRootExternal,"foto.jpg",False)
'      File.Copy2(In,out)       
'    out.close
'    In.Close

'
   
   

    'GUARDA EL ARCHIVO EN LA SDCARD PERO NO ABRE EN EL IMAGEVIEW
    out = File.OpenOutput("/mnt//extSdCard","New.3gp",False)
    'out = File.OpenOutput(File.DirRootExternal,"New.3gp",False)   
    File.Copy2(In,out)       
    out.close 
   
    'bmp.Initialize2(In)
    In.Close
 
   
   
End Sub


Sub cargar_Click
    Dim fotob As String
    'fotob="SELECT IMAGE FROM PICTURE WHERE ID= 10 AND NAME ='nello'" 
        fotob="SELECT foto FROM familia WHERE ID='ospino'"
    ExecuteRemoteQuery(fotob,foto)
End Sub

Sub guarda_Click
Dim imagen As String
insertofoto
'imagen="INSERT INTO picture (ID, IMAGE, NAME)  VALUES (NULL,'"&s&"' ,'nello')"
'imagen="INSERT INTO familia (id ,direccion, foto)  VALUES ('ospino','calle 20# 18c-116 barrio guatapuri','"&s&"' )"
'ExecuteRemoteQuery(imagen,insert)
Dim z As String
z= DateTime.Date(DateTime.Now)&"/" &DateTime.time(DateTime.Now) 
Log("data time: " & z)
imagen="INSERT INTO video (id, video,fecha)  VALUES ('plata','"&s&"', '"& z &"')"
ExecuteRemoteQuery(imagen,insert)
End Sub
 

oscar cabrales

Member
Licensed User
Longtime User
when the file to upload is larger than 1 MB the application closes and I get this error with the debugger.

upload_2013-11-26_20-53-32.png


i need upload video clips of size greater than 1MB on mysql, how i can do it?
 

srm_rm

New Member
I must write a android application for the search engine site, my question is for connecting my android app to the database of site what can i do? I have to write the php service or i can use the php files that there is used in site.
 

srm_rm

New Member
Thank you Erel for answering. But I can't understand your means. Maybe my question wasn't clear. In the site, user send query to Apache solr and Apache solr response to query with respect of the database. somebody else write that parts of our website. Now I just want to write application in android that could do the same operation as site in mobile. means could do the search engine. how should i connect my app to the site.
 

persianpowerman1

Active Member
Licensed User
Longtime User
i ran this ... but got this error

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
startService: class anywheresoftware.b4a.samples.mysql.httputils2service
** Service (httputils2service) Create **
** Service (httputils2service) Start **

Response from server:
main_jobdone (B4A line: 59)

COUNTRIES = parser.NextArray 'returns a list with maps
org.json.JSONException: End of input at character 0 of


at org.json.JSONTokener.syntaxError(JSONTokener.java:446)
at org.json.JSONTokener.nextValue(JSONTokener.java:93)
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:57)
at anywheresoftware.b4a.samples.mysql.main._jobdone(main.java:487)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
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:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:132)
at android.app.ActivityThread.main(ActivityThread.java:4123)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)


at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
at dalvik.system.NativeStart.main(Native Method)
org.json.JSONException: End of input at character 0 of

... whats wrong??
 
Status
Not open for further replies.
Top