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

Dogbonesix

Active Member
Licensed User
Longtime User
Getting Closer

I copied the sample PHP file and changed the login to point to my remote server.

I made a few other changes. I changed the very first line from <? to <?php, which helped.

Then I added a semicolon at the end of each login parameter as follows:
$databasehost = "212.1.208.36";
$databasename = "dogbone6_HX_2";
$databaseusername = "dogbone6_bone";
$databasepassword = "dogXXXX";

That also helped...

The following line apparently works fine as follows:
$con*=*mysql_connect($databasehost,$databaseusername,$databasepassword)*or*die(mysql_error());

BUT I get this error "syntax error, unexpected T_VARIBLE..." one the next line (Code below)
mysql_select_db($databasename)*or*die(mysql_error());

I believe it is exactly as the original sample.

What can I be doing wrong?
 

Dogbonesix

Active Member
Licensed User
Longtime User
Good Point

Good Point - I did not place any "*" in the code I just copied and pasted it. The error does occur at this line with the suspicious error.

I am resending the copied and pasted code as there maybe some invisible characters.

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

Dogbonesix

Active Member
Licensed User
Longtime User
Uhm...

I just pasted the code without the asterisks and they appeared after reviewing the sent post. So that is a clue, gotta figure out a way to remove them.
 

Dogbonesix

Active Member
Licensed User
Longtime User
* = Chr 160

I quickly wrote a program to to display the ASCII for the corresponding letter in the code that I had pasted from the original sample PHP script. It seems that 'Space' translates to Chr 160 for some odd reason.

I have not reviewed the complete PHP script and will advise.
 

Dogbonesix

Active Member
Licensed User
Longtime User
Almost there

Got past the login to MySql ...

Then I get the error: Syntax error, unexpected '{' in line > if (mysql_errno())*{

Code below
=========================
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;
}
=========================
 

Dogbonesix

Active Member
Licensed User
Longtime User
Success

I learned a lot about PHP scripts. The the sample code was correct in logic but there was a lot of invisible characters, formatting and along with copy/paste translation anomalies.

Long Live Android, Thanks

My working code below: (the text below has a few word wrapped lines)
=============================
<?php

$databasehost = "XXX.XXX.XXX.XXX";
$databaseusername = "XXXXXX";
$databasepassword = "XXXXX";
$databasename = "XXXXXXX";

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

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

$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);
}
?>
 

stubuck

New Member
Licensed User
Longtime User
mySQL myBrain --Thank You!

:sign0098:
Thank you Erel for creating and posting the mySQL example! Very Helpful!
 

lgoebels

Member
Licensed User
Longtime User
No way to get Result with Variable in Query

NO NEED TO ANSWERE IT WAS MY FAULT, WRONG ORDER OF DECLARATION IN CASTING MY FUNTION

I Tryed to fill a list view with my Stuff.
Everthing works finde if i use this query
B4X:
"SELECT HERO_WEAK.NAME AS Weak FROM STRONG AS matchups LEFT JOIN HEROS AS HERO_STRONG ON HERO_STRONG.ID = matchups.STRONG LEFT JOIN HEROS AS HERO_WEAK ON HERO_WEAK.ID = matchups.WEAK WHERE HERO_STRONG.NAME = 'Ahri'"
But If i Use This:
B4X:
"SELECT HERO_WEAK.NAME AS Weak FROM STRONG AS matchups LEFT JOIN HEROS AS HERO_STRONG ON HERO_STRONG.ID = matchups.STRONG LEFT JOIN HEROS AS HERO_WEAK ON HERO_WEAK.ID = matchups.WEAK WHERE HERO_STRONG.NAME = '" & Name_Hero.Trim & "'"

I get no Result to see that my Query look the same i pushed it into a MSGBOX as first. This is the Outcome, looks similar to the first Query, but Whey i dont get me results? Look the Attached Image to see what i mean!

Thanks for your Help!
 

Attachments

  • MSGBOX.PNG
    MSGBOX.PNG
    46.1 KB · Views: 287
Last edited:

radmk

New Member
Licensed User
Longtime User
No response

Hai,

I used the same code and replaced the table name with the one's on my server. but there is no response from the server.
i.e. i am able to get "Fetching list of countries" message and there after no error or list getting displayed.

your timely assistance would be grateful. :sign0144:

regards,
Murali.
 

karolaina27

Member
Licensed User
Longtime User
HI, Im in the same situation, My php version is 5.4.7, my server is localhost with xampp, mi script in php doesn't have any error, looks like "\nQuery was empty" but mi listview does'nt charge the table. :sign0163:

thanks
 

karolaina27

Member
Licensed User
Longtime User
I am check the logs but there's no error , runs perfect on the browser, and the b4acode is the same that the post. In the emulator doesn't works :BangHead: .
PHP:
<?php

$databasehost = "localhost";
$databasename = "datab";
$databaseusername = "xxx";
$databasepassword = "";



$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename,$con) or die("Problemas en la seleccion de la base de datos");
$query = file_get_contents("php://input"); 
//$query = "SELECT name, id FROM countries ORDER BY id";
$sth = mysql_query($query) or die("error en la consulta");



if (!$con) {
    die('<strong>No pudo conectarse:</strong> '.mysql_error());
}else{

echo 'Conectado  satisfactoriamente al servidor <br />';
}
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,true));
}

mysql_close($con);
?>]
I change nothing in the B4A code. Pleas someone??? thanks
 

Sbleck

Member
Licensed User
Longtime User
Is there any way to check this environment ?

Hello, Erel,

You should create the database in your server. Usually it can be done from the control panel. The php file should be hosted there as well.

I´ve done all the steps you mentioned, but wasn´t possible to see the same results when trying to do the same in a local network, because of errors like "java.lang.RuntimeException: JSON Array expected.". What I´m receiving back was mentioned below (and obtained from the log tab, through the B4A IDE):

Installing file.
PackageAdded: package:anywheresoftware.b4a.myownsqltest.mysql
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Resposta do servidor: databasehost = "192.168.1.95";
$databasename = "test_prod";
$databaseusername ="someone";
$databasepassword = "somepass";
$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$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_hc_responsesuccess (java line: 361)
java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
at anywheresoftware.b4a.eyebuzz.mysql.main._hc_responsesuccess(main.java:361)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)
at anywheresoftware.b4a.BA$3.run(BA.java:301)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:130)
at android.app.ActivityThread.main(ActivityThread.java:3701)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:624)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.


I could connect to the remote MySQL database through that station, where the PHP script resides. But felt some lack of more details to check where could be the problem and to be possible to reproduce the same result of the sample you offered, that worked without more problems (in the Android device).

When I changed the first line of the PHP script you offered from "<?" to "<?php" I received back the result "\nQuery was empty", too. Is there any way to test this script in the browser at the side where the scripts is, before trying to connect trhough the Android device ?

Any advice should be greatly appreciated, of course too...

Regards,
Sven

P.S.: I´ve installed MySQL-essential-5.1.56-win32 and PHP-5.3.170Win32-VC9-x86 and apparently, they are working as expected. For the HTTP Server, I used the BRS WebWeaver v1.32 (used for testing pourposes, in another project) and apparently, it is working as expected, too...
 
Last edited:

Sbleck

Member
Licensed User
Longtime User
Steps for reaching success using B4A with MySQL local database and Web Server, PHP...

Hi, Erel,

I think that I have found a way to check and test B4A with MySQL local database, using the following steps in one 32-Bit environment (Windows 2003 Server standard) with fixed IP address, in local network:

- Installed mysql-essential-5.1.56-win32.msi and checked if was working ok;
- Installed php-5.3.17-win32-vc9-x86.msi and paid attention for selecting to choose "Other CGI" in "Web Server Setup", during installation;
- Installed BRS WebWeaver 1.32 and configured PHP to "Enable PHP Processing", extensions to parse .php documents and the "Location of PHP.exe", pointed to the PHP-CGI.EXE file, for PHP 5;
- Used the php file of this tutorial, changing the first line from "<?" to "<?php" and checking of there was specified the correct values (database host, database name, database username and password);
- Made a change in PHP.INI to cgi.force_redirect=0;
- Rebooted the server, when needed too.

Than, was possible to upload (via B4A Bridge, using wireless) the modified sample (based on yours MySQL example) to obtain the results, in the Android device...:)

HTH,
Sven
 
Last edited:

karolaina27

Member
Licensed User
Longtime User
I solved my connection error changing the localhost or 127.0.0.1 to 10.0.2.2 and the connection works but now I have other error : java.lang.RuntimeException JSON ARRAY EXPECTED.

the debug is

** Activity (main) Create, isFirst = true **


** Activity (main) Resume **


Response from server: Conectado satisfactoriamente al servidor <br />[{"name":"betera","id":"Be"},{"name":"estrella","id":"es"},{"name":"paloma","id":"pa"},{"name":"valencia","id":"Va"}]



main_hc_responsesuccess (B4A line: 63)


countries = parser.NextArray 'returns a list with maps



java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
at anywheresoftware.b4a.samples.mysql.main._hc_responsesuccess(main.java:449)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:136)
at anywheresoftware.b4a.BA$2.run(BA.java:244)
at android.os.Handler.handleCallback(Handler.java:605)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4340)
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:784)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.

Any idea??

:)
 

karolaina27

Member
Licensed User
Longtime User
I solved that problem too, that was html code in my php ( echo...), I quit and now works fine.

thks a lot.:sign0060:
 

Sbleck

Member
Licensed User
Longtime User
I solved that problem too, that was html code in my php ( echo...), I quit and now works fine.

thks a lot.:sign0060:

Ok, Thanks for sending your comments about having encountered the problems and corrected them.

> I solved my connection error changing the localhost or 127.0.0.1 to 10.0.2.2 and the connection works but now...

I wanted to mention that you could also fix an IP address to your server/station doing an IP reservation in the router. Maybe this couldn´t so hard to do. What I´ve done too was to configure my own router (TP-Link WR740N) to login to NO-IP.com site, using a free account (after creating one). Than, could be possible to specify that URL in the B4A code and being possible to obtain the MySQL data, from outside of my own network...

HTH,
Sven
 

oscar cabrales

Member
Licensed User
Longtime User
insert and read blob in MySQL database

Hello,I have a running my program perfect, but I need save field blob in my MYSQL database, i can do that with basic4android?.
 
Status
Not open for further replies.
Top