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

arturataide

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

Malark

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

Active Member
Licensed User
Longtime User
Did you try to put your database and tables in it in Unicode-character ??

For you maybe in UTF8-Unicode. Maybe
 

Malark

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

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

Active Member
Licensed User
Longtime User
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
 
Last edited:

buttons

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

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

Philipp

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

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

mystic7

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

jegadk

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

Member
Licensed User
Longtime User
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}]
 
Status
Not open for further replies.
Top