Android Tutorial Connect Android to MySQL Database Tutorial

Erel · Mar 21, 2011

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:

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

arturataide · Nov 22, 2011

PHP:
It has mistake so Erel forgot to corrected it.
It is here:

PHP:

<? $databasehost = "localhost"; $databasename = "xxxx"; $databaseusername ="xxxx"; $databasepassword = "xxxx;

It must be: $databasepassword = "xxxx";

Thank you very mutch..
I didn't check the code..
It solves my problem

Malark · Nov 24, 2011

Charset problem with MS-SQL database

Hello,

Firts of all, congratulation for this RAD tool. It's very very good and useful.

I have a big problem with the charsets. The example program (with few modifications for MSSQL connect instead MySQL) works perfectly with my MS-SQL database, but where there is a special character (e.g.: é á) in the database, the program cuts the remain of the string by these chars. I think it must be the json_encode the problem, because the charset of the database and the charset of the PHP server is the same (latin1 - ISO 8859-1) and when I use only the PHP script with a browser (without json_encode) it works perfectly, but when I use the print json_encode($rows); code, then it is wrong both my android phone and in browsers. Is there any other way to get the datas in the android side or I must use the JSON format? I am new in B4A and PHP programming so please help me, if somebody could. :sign0085:

The PHP code:

<?php
include("adodb5/adodb.inc.php");
$db = ADONewConnection('odbc_mssql');
$dsn = "Driver={SQL Server};Server=xxxx;Database=xxxx;";
$db->Connect($dsn,'xxxx','xxxx') or die('fail');
if(!$db)
{
die('Something went wrong while connecting to MSSQL');
}

$query = file_get_contents("php://input");
$db->SetFetchMode(ADODB_FETCH_ASSOC);
$rs = $db->Execute($query);

while ($array = $rs->FetchRow()) {
$rows[] = $array;
}
print json_encode($rows);
?>

pluton · Nov 25, 2011

Did you try to put your database and tables in it in Unicode-character ??

For you maybe in UTF8-Unicode. Maybe

Malark · Nov 26, 2011

Thanks for the reply. You were right. The json_encode wants utf8 instead ISO-8859-1. The right way:

$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$r['column_name'] = utf8_encode($r['column_name']);
$rows[] = $r;
}
print json_encode($rows);

buttons · Dec 10, 2011

Mmm, I have a field in my table which is a BLOB which I've loaded an image into. This solution doesn't seem to want to json encode this field returning an empty/null string instead. Is there a way round this, and then can I load it into a bitmap? thanks!

pluton · Dec 10, 2011

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

B4X:

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

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

buttons · Dec 11, 2011

Thanks for the reply but my database is a MySQL database not the SQLlite on the Android device. I have already inserted the image into the MySQL database (using PhpMyAdmin/PHP), the problem is that the json.php code given in the tutorial appears to discard it/not return it to the Android process.

buttons · Dec 11, 2011

buttons said:
Thanks for the reply but my database is a MySQL database not the SQLlite on the Android device. I have already inserted the image into the MySQL database (using PhpMyAdmin/PHP), the problem is that the json.php code given in the tutorial appears to discard it/not return it to the Android process.

I've modded my code so that the image data is JSONed after I base64_encoded it, now how can I use that, I will want to bitmap it so I can put it in a listview?

PHP:

for ($i=0; $i<sizeof($rows); $i++) {
// send the base64 encoding, see if b4a can do anything with that
   $rows[$i]['image']=base64_encode($rows[$i]['image']);
}


print json_encode($rows);

Erel · Dec 11, 2011

You can use StringUtils to decode the Base64 string. Once you get the bytes array you should follow the code in post #86.

Philipp · Dec 11, 2011

Just skimmed through the threads. For our Final School (Evening-School) -Project we too have to interact with a mysql-db. We decided to use CodeIgniter as small & secure framework. We use it with xmlrpc but you could also use json.

Both libraries are available for b4a. The xmlrpc-lib has been newly created by XverhelstX.

I think it's well worth taking a look at it.

buttons · Dec 11, 2011

many thanks

Thanks everyone, got it working now. Just in case anyone else needs similar...

B4X:

   Dim su As StringUtils 
   Dim ImageBytes() As Byte 
   Dim SQLImage As Bitmap

B4X:

Sub items
   itemid = RecordMap.Get("itemid")
   image = RecordMap.Get("image")
   If image=Null OR image="" Then
         ' no image in database so use the standard no image file defined in Globs
         SQLImage=ImAssets.I("noimage")
      Else
         ImageBytes=su.DecodeBase64(image)
         Dim InputStream1 As InputStream
         InputStream1.InitializeFromBytesArray(ImageBytes, 0, ImageBytes.Length)   
         SQLimage.Initialize2(InputStream1)
         InputStream1.Close 
   End If
   desc = RecordMap.Get("description")
   cat = RecordMap.Get("category")
   cals = RecordMap.Get("cals")
   official = RecordMap.Get("officialcambridge")
   approved = RecordMap.Get("approved")
End Sub

jegadk · Jan 23, 2012

PHP to ASP

Hi.

Can :sign0163: convert the PHP code to ASP ?

Erel · Jan 24, 2012

Maybe this ASP.Net code will be useful for you: Connect Android to MS SQL Server Tutorial

mystic7 · Jan 24, 2012

Erel said:
Maybe this ASP.Net code will be useful for you: Connect Android to MS SQL Server Tutorial

Stupid question but is it safe to assume that, if I were to duplicate your example exactly, hypothetically speaking, only using asp code instead of php, then all of your code would be exactly the same except that I'd be fetching countries.ASP rather than countries.PHP? In other words no other changes to your app code would be necessary?

Erel · Jan 24, 2012

That is correct.

jegadk · Jan 25, 2012

Help with sample

Hi all.

Need help to a little sample.

Having mysql with dbname android, table is person, columns is ID,name,address,mobile

startButton call asp webservice file like http://localhost:1004/ws1.asp?searchword=jesper

Return all match records in listview with id and name. Touch record in listview and show ID,name,address,mobile in respective labels

jegadk · Jan 25, 2012

json result from my asp page

[{"ID":1,"name":"Jesper Hossfeld Garb\u00F8l","address":"N\u00F8rrelunden 55","zipCode":"5260","city":"Odense S.","telephone":null,"mobile":"61104913"},{"ID":2,"name":"Jesper Garb\u00F8l","address":null,"zipCode":null,"city":null,"telephone":null,"mobile":null}]

Erel · Jan 26, 2012

You should start a new thread for this question.

mystic7 · Jan 27, 2012

Erel said:
That is correct.

Short, sweet, and easy to understand. Thanks!

alfalfa · Jan 29, 2012

I want to download the file but I can't:sign0104:

Android Tutorial Connect Android to MySQL Database Tutorial

Attachments

Member

Member

Active Member

Member

Member

Active Member

Member

Member

B4X founder

Member

Member

Member

B4X founder

Member

B4X founder

Member

Member

B4X founder

Member

New Member

Similar Threads

Privacy & Transparency

Privacy & Transparency