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

Last edited:
Click to expand...
starttransacion

please, what is the correct procedure to start transaction using mysql, whether through the use of mysql consoll vorrettamente the code is executed and if I do it through the application gives me error ...

Sub AggiungiArtPresenti
Dim querystart,QueryBox1,QueryBox2,queryfine,queryfinale As String
If ArtistePresenti=0 Then
'Msgbox("inserito artiste Presenti","ESEGUITO")
Else
ArtistePresenti=ArtistePresenti-1
ElencoPresenti=ListArtPresenti.GetItem(ArtistePresenti)
IDPresente=ListIDArtPresenti.GetItem(ArtistePresenti)
querystart="START TRANSACTION;"
QueryBox1 ="INSERT INTO Presenze (IDSerata,DataSerata,IDArtista,Pseudo,ArtistaPresente) VALUES ('" & IDSerata & "','" & DataSerata & "','" & IDPresente & "','" & ElencoPresenti & "','P');"
QueryBox2 ="INSERT INTO Presenze (IDSerata,DataSerata,IDArtista,Pseudo,ArtistaPresente) VALUES ('" & IDSerata & "','" & DataSerata & "','" & IDPresente & "','" & ElencoPresenti & "','C');"
'QueryBox2 = "INSERT INTO night2012.Consumazioni(IDSerata,DataSerata,IDCLiente,NomeCliente,StatoCliente,IDArtista,Pseudo,StatoArtista,IDServizio,DescrizioneServizio,IniServizio,FineServizio,CostoServizio,StatoConsumazione,TipoServizio) VALUES('" & IDSerata & "','" & GiornoAttuale & "','00',' ',' ','" & IDPresente & "','" & ElencoPresenti & "','L','00',' ','" & OraAttuale & "',' ','0','1','INATTESA');"
queryfine = "COMMIT;"
queryfinale=querystart & QueryBox1 & QueryBox2 & queryfine
Msgbox(queryfinale,"")
ExecuteRemoteQuery(queryfinale,AggiungiPresenti)

End If

End Sub

thank you
 
no error response

Erel Hello, I added two tables in the database of convenience, I use a php file as you put online, and tried again ... There are three ways that I put below ... ps if I put the same code of "TotalQuery" directly into the mysql console works perfectly if I pass through my app does not work but I did not return any error ... I do not understand where the mistake .. could you help me please?

Sub BtnServEff_Click
QueryStart="START TRANSACTION;"
QueryBox1="INSERT INTO tab1(campo1) values('1');"
QueryBox2="INSERT INTO tab2(campo2) Values('1');"
QueryEnd="COMMIT;"
TotalQuery=QueryStart & QueryBox1 & QueryBox2 & QueryEnd

ExecuteRemoteQuery(TotalQuery,TSKTest) 'NO RUN
'ExecuteRemoteQuery(QueryBox1,TSKTest) 'RUN OK
'ExecuteRemoteQuery(QueryBox2,TSKTest) 'RUN OK

End Sub

Sub BtnServEff_Click
'QueryStart="START TRANSACTION;"
QueryBox1="INSERT INTO tab1(campo1) values('1');"
'QueryBox2="INSERT INTO tab2(campo2) Values('1');"
'QueryEnd="COMMIT;"
'TotalQuery=QueryStart & QueryBox1 & QueryBox2 & QueryEnd

'ExecuteRemoteQuery(TotalQuery,TSKTest) 'NO RUN
ExecuteRemoteQuery(QueryBox1,TSKTest) 'RUN OK
'ExecuteRemoteQuery(QueryBox2,TSKTest) 'RUN OK

End Sub
Sub BtnServEff_Click
'QueryStart="START TRANSACTION;"
'QueryBox1="INSERT INTO tab1(campo1) values('1');"
QueryBox2="INSERT INTO tab2(campo2) Values('1');"
'QueryEnd="COMMIT;"
'TotalQuery=QueryStart & QueryBox1 & QueryBox2 & QueryEnd

'ExecuteRemoteQuery(TotalQuery,TSKTest) 'NO RUN
'ExecuteRemoteQuery(QueryBox1,TSKTest) 'RUN OK
ExecuteRemoteQuery(QueryBox2,TSKTest) 'RUN OK

End Sub
 
i forgot

I forgot, the tables are simple, are:
Tab1 with 2 fields IDTab1​​primary key and autoincrement and campo1 INT
Tab2 with 2 field IDTab2 primary key autoincrement and campo2 INT
thanks
 
Hello, I've copied-pasted the example, then changed to my db settings and executed the code, leading to 'success' but I receive an empty response, thus leading parser to error:
jsonException: end of input at character 0.
My query is as follows:
SELECT startTime,endTime FROM activities WHERE actDate=20120419

actdate is set to integer, and I don't recieve an error in sql syntax, neither when I run this sql with phpMyAdmin.

Any ideas?
 
Hello Erel ,I chek my log , in log this

Error: Internal Server Error, StatusCode: 500

START TRANSACTION;UPDATE Consumazioni SET StatoArtista = 'O', StatoConsumazione= '0',FinServGiorno = '04/20/2012 ',Cassa='0',CostoServizio = 0 WHERE IDConsumazioni = '83';INSERT INTO Consumazioni(IDSerata,DataSerata,IDCLiente,NomeCliente,StatoCliente,IDArtista,Pseudo,StatoArtista,IDServizio,DescrizioneServizio,IniServizio,FineServizio,CostoServizio,StatoConsumazione,TipoServizio,IniServGiorno,finServGiorno,OraAvviso,GiornoAvviso,nArtiste)VALUES('5','04/20/2012','0','GENERICO','0','2','PSEUDO2','O','1','20 MIN','20:59:13','21:19:13','20','1','CountDown','04/20/2012',' ','21:17:43','04/20/2012','1');COMMIT;\nYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE Consumazioni SET StatoArtista = 'O', StatoConsumazione= '0',FinServGiorno' at line 1
 
guidoarfini said:
Hello Erel ,I chek my log , in log this

Error: Internal Server Error, StatusCode: 500

START TRANSACTION;UPDATE Consumazioni SET StatoArtista = 'O', StatoConsumazione= '0',FinServGiorno = '04/20/2012 ',Cassa='0',CostoServizio = 0 WHERE IDConsumazioni = '83';INSERT INTO Consumazioni(IDSerata,DataSerata,IDCLiente,NomeCliente,StatoCliente,IDArtista,Pseudo,StatoArtista,IDServizio,DescrizioneServizio,IniServizio,FineServizio,CostoServizio,StatoConsumazione,TipoServizio,IniServGiorno,finServGiorno,OraAvviso,GiornoAvviso,nArtiste)VALUES('5','04/20/2012','0','GENERICO','0','2','PSEUDO2','O','1','20 MIN','20:59:13','21:19:13','20','1','CountDown','04/20/2012',' ','21:17:43','04/20/2012','1');COMMIT;\nYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE Consumazioni SET StatoArtista = 'O', StatoConsumazione= '0',FinServGiorno' at line 1
Click to expand...


'NomeCli ente' field shouldnt' t be NomeCliente (with no space) ?
Space is telling sql to perform an action named 'ente' which of course does not exist. At least, I think so, I am not an SQL wiz.
 
I've noticed other spaces in other field names too. Perhaps you could try this:

START TRANSACTION;UPDATE Consumazioni SET StatoArtista = 'O', StatoConsumazione= '0',FinServGiorno = '04/20/2012 ',Cassa='0',CostoServizio = 0 WHERE IDConsumazioni = '83';INSERT INTO Consumazioni(IDSerata,DataSerata,IDCLiente,NomeCliente,StatoCliente,IDArtista,Pseudo,StatoArtista,IDServizio,DescrizioneServizio,IniServizio,FineServizio,CostoServizio,StatoConsumazione,TipoServizio,IniServGiorno,finServGiorno,OraAvviso,GiornoAvviso, nArtiste)VALUES('5','04/20/2012','0','GENERICO','0','2','PSEUDO2','O','1','20 MIN','20:59:13','21:19:13','20','1','CountDown','0 4/20/2012',' ','21:17:43','04/20/2012','1');COMMIT;

A final notice: I've seen in a date field this: '04/20/2012 '. There is a space in the end, but I don't know how mySql will behave in case you set field length to 10 (this string's length is 11).

An alternative, in case you wish to use space character in a table name, is to put it inside ``. This would be:

START TRANSACTION;UPDATE Consumazioni SET StatoArtista = 'O', StatoConsumazione= '0',FinServGiorno = '04/20/2012 ',Cassa='0',CostoServizio = 0 WHERE IDConsumazioni = '83';INSERT INTO Consumazioni(IDSerata,DataSerata,IDCLiente,`NomeCli ente`,StatoCliente,IDArtista,Pseudo,StatoArtista,`ID Servizio`,DescrizioneServizio,IniServizio,`FineServi zio`,CostoServizio,StatoConsumazione,TipoServizio,`I niServGiorno`,finServGiorno,OraAvviso,GiornoAvviso, nArtiste)VALUES('5','04/20/2012','0','GENERICO','0','2','PSEUDO2','O','1','20 MIN','20:59:13','21:19:13','20','1','CountDown','0 4/20/2012',' ','21:17:43','04/20/2012','1');COMMIT;

Perhaps it works.
 
Last edited:
hello guys, thank you for the time I have been paying, but the error is not in any space errors in the log file gives me space but if I analyze the query output by the APP is correct ... However, for security I tried again with two tables and simple test, they contain entrembe 2 columns, the first in the primary key, the second a TEXT field, if I run the code log show

START TRANSACTION;INSERT INTO TAB1 (campo1) VALUES ('10');INSERT INTO TAB1 (campo1) VALUES ('16'); INSERT INTO TAB2 (campo2) VALUES ('32'); COMMIT; \ NYOU have an error in your SQL syntax; check the manual That corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO TAB1 (field1) VALUES ('10'); INSERT INTO TAB1 (campo1) VALUES ('16 '); IN' at line 1


If I run a query at a time without using START TRANSACTION tuttte queries work fine even the real ones in my application ... I repeat that aihme do not know php at all, if I run all the SQL code that I posted directly to mysql all work correctly .. repetitive but I use the php code published by Erel, maybe I should not adapt to use the start transaction?

Sub BtnTest_Click
QueryStart="START TRANSACTION;"
QueryBox1="INSERT INTO TAB1 (campo1)values ('10');"
QueryBox2="INSERT INTO TAB1 (campo1)values ('16');"
QueryBox3="INSERT INTO TAB2 (campo2)values ('32');"
QueryEnd="COMMIT;"
QueryTotal=QueryStart & QueryBox1 & QueryBox2 & QueryBox3 & QueryEnd
ExecuteRemoteQuery(QueryTotal,1000)


End Sub
 
guidoarfini said:
hello guys, thank you for the time I have been paying, but the error is not in any space errors in the log file gives me space but if I analyze the query output by the APP is correct ... However, for security I tried again with two tables and simple test, they contain entrembe 2 columns, the first in the primary key, the second a TEXT field, if I run the code log show

START TRANSACTION;INSERT INTO TAB1 (campo1) VALUES ('10');INSERT INTO TAB1 (campo1) VALUES ('16'); INSERT INTO TAB2 (campo2) VALUES ('32'); COMMIT; \ NYOU have an error in your SQL syntax; check the manual That corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO TAB1 (field1) VALUES ('10'); INSERT INTO TAB1 (campo1) VALUES ('16 '); IN' at line 1


If I run a query at a time without using START TRANSACTION tuttte queries work fine even the real ones in my application ... I repeat that aihme do not know php at all, if I run all the SQL code that I posted directly to mysql all work correctly .. repetitive but I use the php code published by Erel, maybe I should not adapt to use the start transaction?

Sub BtnTest_Click
QueryStart="START TRANSACTION;"
QueryBox1="INSERT INTO TAB1 (campo1)values ('10');"
QueryBox2="INSERT INTO TAB1 (campo1)values ('16');"
QueryBox3="INSERT INTO TAB2 (campo2)values ('32');"
QueryEnd="COMMIT;"
QueryTotal=QueryStart & QueryBox1 & QueryBox2 & QueryBox3 & QueryEnd
ExecuteRemoteQuery(QueryTotal,1000)


End Sub
Click to expand...

I've tested myself, and yes, you are right. Putting 'START TRANCACTION' leads to an error. I am no expert on such things, just wanted to tell you that you're not the only one receiving this error. I am sure there is an explanation, in the mean time, why not simply remove 'start tranc' and 'commit'? :)
 
This is what I found somewhere:

"If you need to execute several SQL commands in a row (usually called batcg SQL) using PHP you canot use mysql_query() since it can execute single command only".

It's from an old post, I don't know if this holds true nowadays, but I guess it's an explanation.
 
This looks like what I do with web services, the only part that I'd change id definitely *NOT* sending a query as a parameter in plain text. The problem is SQL injection attacks, they're getting a little too common. Sony got hit so many times in the last year or two it's embarassing.

What I suggest is encrypting the parameters being passed with a Private key prior, or at least GZip->Base64->URL Encode, and do the reverse on the server to confuse people trying to abuse it. Also call the parameter something not so obvious like Token, or Image data...

Later if I do some android stuff for my work, i'll post an asp.net site package that you can deploy anywhere. I wanted to make one anyways that returns xml tables, comma separated text, as well as JSON (because I haven't tried it yet) all thru a web service so we can pick and choose as we see fit.

As for your multiple commands problem, i'd pass an array of strings (queries), then let your web server return an array back, or a single merged table back.
 
Is there any way to communicate with the PHP script through a web browser. I'am trying to see how the data returned looks in the web browser. If anyone could tell me how to pass parameters to it through a web browser it would be greatly appreciated. Thanks!
 
Ok, so I have read through this thread and I am still confused. Where do I place the PHP script file, and I guess more importantly, how do I make the php script, in notepad?

Just to note, the server I am connecting to is not mine, I just have a spot on it. I have a database on the server, but I don't have access to the server configuration.
 
McJaegs said:
Ok, so I have read through this thread and I am still confused. Where do I place the PHP script file, and I guess more importantly, how do I make the php script, in notepad?

Just to note, the server I am connecting to is not mine, I just have a spot on it. I have a database on the server, but I don't have access to the server configuration.
Click to expand...

I upload the PHP script in a folder on the server, by using ftp. Usually I write scripts in jEdit, but certainly you may use whichever editor you wish to, such as notePad etc.
 
mc73 said:
I upload the PHP script in a folder on the server, by using ftp. Usually I write scripts in jEdit, but certainly you may use whichever editor you wish to, such as notePad etc.
Click to expand...

Does it matter where on the server? I connect to the server using SQL Server Management Studio.
 
McJaegs said:
Does it matter where on the server? I connect to the server using SQL Server Management Studio.
Click to expand...
I suppose SSMS is for connecting to an SQL server, thus t is not the same as uploading via FTP to a website. But I am not an expert on such things. What I do know, is that the php script should be uploaded to a folder somewhere in the website's directories, or at least this is how I do it.
 
Status
Not open for further replies.

Similar Threads

aeric
  • Article Article
Share My Creation [B4J] [MySQL] [API] Server (Key + Token) and [B4X] User Login Apps
2 3
Replies
45
Views
50K
aeric
aeric
aeric
  • Article Article
Share My Creation (PHP/MySQL/API) User Login App
4 5 6
Replies
108
Views
101K
aeric
aeric
Erel
  • Locked
  • Article Article
B4A Library JdbcSQL - Directly connect to remote databases
2
Replies
23
Views
78K
Erel
Erel
Erel
  • Locked
  • Article Article
B4J Library [B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)
5 6 7
Replies
139
Views
206K
Erel
Erel
Erel
  • Locked
  • Article Article
Android Tutorial SQL tutorial
7 8 9
Replies
161
Views
336K
luciano deri
luciano deri
Back
Top