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

persianpowerman1

Active Member
Licensed User
Longtime User
ok.. buddy... this is what i did..
i got the RDC ready the way you told me.. in the image i have put the Server/config.properties...

HOWEVER, in the browser i get this error
RemoteServer is running (Fri Dec 06 15:34:30 IST 2013)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out

then i ran your country population example.. but this is what i 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:


Error occurred on line: 59 (httpjob)


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 java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:485)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:174)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:491)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
at anywheresoftware.b4a.BA$3.run(BA.java:312)
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)

please help me... im very confused.. what do i do with a PHP script in your example??
where is you DB in the example.. is it on one of your servers?!?!
 

Attachments

  • ref Img.jpg
    ref Img.jpg
    127.9 KB · Views: 429

guidoarfini

Member
Licensed User
Longtime User
hello Guys, i have problem whit connection mysql.... this is log...

main_hc_responsesuccess (B4A line: 116)


res = Response.GetString("UTF8")
android.os.NetworkOnMainThreadException


at android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode.java:1118)
at libcore.io.BlockGuardOs.recvfrom(BlockGuardOs.java:163)
at libcore.io.IoBridge.recvfrom(IoBridge.java:513)
at java.net.PlainSocketImpl.read(PlainSocketImpl.java:488)
at java.net.PlainSocketImpl.access$000(PlainSocketImpl.java:46)
at java.net.PlainSocketImpl$PlainSocketInputStream.read(PlainSocketImpl.java:240)
at org.apache.http.impl.io.AbstractSessionInputBuffer.fillBuffer(AbstractSessionInputBuffer.java:103)
at org.apache.http.impl.io.AbstractSessionInputBuffer.read(AbstractSessionInputBuffer.java:134)
at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:161)
at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:175)
at org.apache.http.impl.io.ChunkedInputStream.exhaustInputStream(ChunkedInputStream.java:289)
at org.apache.http.impl.io.ChunkedInputStream.close(ChunkedInputStream.java:262)
at org.apache.http.conn.BasicManagedEntity.streamClosed(BasicManagedEntity.java:179)
at org.apache.http.conn.EofSensorInputStream.checkClose(EofSensorInputStream.java:266)
at org.apache.http.conn.EofSensorInputStream.close(EofSensorInputStream.java:213)
at java.io.InputStreamReader.close(InputStreamReader.java:145)
at org.apache.http.util.EntityUtils.toString(EntityUtils.java:139)
at anywheresoftware.b4a.http.HttpClientWrapper$HttpResponeWrapper.GetString(HttpClientWrapper.java:453)
at b4a.example.main._hc_responsesuccess(main.java:509)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:169)
at anywheresoftware.b4a.BA$3.run(BA.java:307)
at android.os.Handler.handleCallback(Handler.java:615)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4898)
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:1006)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:773)
at dalvik.system.NativeStart.main(Native Method)
[/CODE]
 

CSP

Member
Licensed User
Longtime User
I've got a problem connecting 2 or more devices to the database.

I've looked all over for a solution, but it seems I can only access my database from one device and not two or more at the same time. The database is set up just as a forums database, but I can only make one connection. Any ideas?

Other than that, the php script works great and the app code, I just unable read a short list every 5 seconds from 2 or more devices. Is there a variable here that needs to be set, or is there something I need to add to this code example?


My database variables:

auto increment increment 1
auto increment offset 1
automatic sp privileges ON
back log 50
basedir /usr/local/mysql-5.0.96/
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client utf8
character set connection utf8
character set database utf8
character set filesystem binary
character set results utf8
character set server utf8
character set system utf8
character sets dir /usr/local/mysql-5.0.96/share/mysql/charsets/
collation connection utf8_unicode_ci
(Global value) utf8_general_ci
collation database utf8_general_ci
collation server utf8_general_ci
completion type 0
concurrent insert 1
connect timeout 60
datadir /var/lib/mysql_data/7/
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
div precision increment 4
keep files on create OFF
engine condition pushdown OFF
expire logs days 0
flush OFF
flush time 0
ft boolean syntax + -><()~*:""&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive YES
have bdb NO
have blackhole engine YES
have compress YES
have community features YES
have profiling YES
have crypt YES
have csv YES
have dynamic loading YES
have example engine NO
have federated engine YES
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster NO
have openssl NO
have ssl NO
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
hostname p3nlhdb5039-04.shr.prod.phx3.secureserver.net
init connect
init file
init slave
innodb additional mem pool size 10,485,760
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 134,217,728
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir /var/lib/mysql_data/7
innodb adaptive hash index ON
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table ON
innodb flush log at trx commit 0
innodb show locks held 10
innodb show verbose locks 0
innodb flush method O_DIRECT
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 8,388,608
innodb log file size 67,108,864
innodb log files in group 2
innodb log group home dir /var/lib/mysql_data/7
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb support xa OFF
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 8
innodb thread sleep delay 10,000
innodb io capacity 200
innodb ibuf max size 67,108,864
innodb ibuf active contract 0
innodb ibuf accel rate 100
innodb flush neighbor pages 1
innodb read ahead both
innodb enable unsafe group commit 0
innodb adaptive checkpoint none
innodb read io threads 8
innodb write io threads 8
innodb use sys malloc ON
innodb fast recovery OFF
innodb thread concurrency timer based OFF
innodb extra rsegments 0
innodb dict size limit 0
innodb io pattern trace 0
innodb io pattern trace running 0
innodb io pattern size limit 0
innodb use legacy cardinality algorithm ON
interactive timeout 60
join buffer size 4,194,304
key buffer size 536,870,912
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language /usr/local/mysql-5.0.96/share/mysql/english/
large files support ON
large page size 0
large pages OFF
lc time names en_US
license GPL
local infile ON
locked in memory OFF
log OFF
log bin ON
log bin trust function creators OFF
log error /var/lib/mysql_logs/7/error.log
log queries not using indexes OFF
log slave updates OFF
log slow queries ON
log slow filter
log slow rate limit 1
log slow verbosity microtime
log warnings 1
long query time 3,600
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 33,554,432
max binlog cache size 18446744073709547520
max binlog size 1,073,741,824
max connect errors 10,000
max connections 1,000
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 18446744073709551615
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 18446744073709551615
max sort length 1,024
max sp recursion depth 0
max tmp tables 32
max user connections 200
max write lock count 18446744073709551615
min examined row limit 0
multi range count 256
myisam data pointer size 6
myisam max sort file size 9223372036853727232
myisam mmap size 18446744073709551615
myisam recover options FORCE
myisam repair threads 1
myisam sort buffer size 134,217,728
myisam stats method nulls_unequal
net buffer length 8,192
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords OFF
open files limit 65,535
optimizer prune level 1
optimizer search depth 62
pid file /var/run/mysql/7.pid
plugin dir
port 3,306
preload buffer size 32,768
profiling OFF
profiling history size 15
profiling server OFF
profiling use getrusage OFF
protocol version 10
query alloc block size 8,192
query cache limit 1,048,576
query cache min res unit 4,096
query cache size 67,108,864
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 4,096
read buffer size 4,194,304
read only OFF
read rnd buffer size 4,194,304
relay log /var/lib/mysql_logs/7/p3nlhdb5039-04-relay-bin
relay log index /var/lib/mysql_logs/7/p3nlhdb5039-04-relay-bin.index
relay log info file /var/lib/mysql_data/7/relay-log.info
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
secure file priv
server id 1
skip external locking ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave load tmpdir /tmp/mysqltmp/
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
slow query log ON
slow query log file /var/lib/mysql_logs/7/slow.log
socket /var/run/mysql/7.sock
sort buffer size 4,194,304
sql big selects ON
sql mode
sql notes ON
sql warnings OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine MyISAM
sync binlog 0
sync frm ON
system time zone MST
table cache 10,000
table lock wait timeout 50
table type MyISAM
thread cache size 100
thread stack 262,144
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
tmp table size 8,388,608
tmpdir /tmp/mysqltmp/
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation READ-COMMITTED
updatable views with limit YES
userstat running ON
use global long query time OFF
version 5.0.96-log
version comment MySQL Community Server (GPL)
version compile machine x86_64
version compile os unknown-linux-gnu
wait timeout 60
 

CSP

Member
Licensed User
Longtime User
The connections only time out except to one device at a time, no error messages...
 

CSP

Member
Licensed User
Longtime User
Here is the timeout error log, I still can't figure it out or find anything about this kind of problem.

java.net.SocketTimeoutException
at org.apache.harmony.luni.net.PlainSocketImpl.read(PlainSocketImpl.java:461)
at org.apache.harmony.luni.net.SocketInputStream.read(SocketInputStream.java:85)
at org.apache.http.impl.io.AbstractSessionInputBuffer.fillBuffer(AbstractSessionInputBuffer.java:103)
at org.apache.http.impl.io.AbstractSessionInputBuffer.read(AbstractSessionInputBuffer.java:134)
at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:161)
at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:175)
at org.apache.http.impl.io.ChunkedInputStream.exhaustInputStream(ChunkedInputStream.java:289)
at org.apache.http.impl.io.ChunkedInputStream.close(ChunkedInputStream.java:262)
at org.apache.http.entity.BasicHttpEntity.consumeContent(BasicHttpEntity.java:142)
at org.apache.http.conn.BasicManagedEntity.consumeContent(BasicManagedEntity.java:114)
at anywheresoftware.b4a.http.HttpClientWrapper$HttpResponeWrapper$1.run(HttpClientWrapper.java:564)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:444)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:306)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1088)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:581)
at java.lang.Thread.run(Thread.java:1019)
 

CSP

Member
Licensed User
Longtime User
UPDATE*

I contacted my ISP, GoDaddy (Barf), and after about an hour of trying to solve the problem on the phone with tech support, even they couldn't figure out the problem. SO... I found what seems to be a really awesome hosting site ServersFree.com and I set up a mysql database just as I had done before and it works perfectly. I guess long story short, stay away from GoDaddy.

Thank You everyone for all your efforts and time! (Have a US Provisional Patent filed for this project, couldn't have done it without all of you!)

P.S. - You can remove my posts here Erel as they are only relevant to some GoDaddy users it seems.
 

dunski

Member
Licensed User
Longtime User
I use to use this way to connect to mySql database without a problem before when I used it with another app about a year ago.
But now it just times out and does nothing except I get this error in the log and sometimes it just spits out the content of the php file on the server....
startService: class com.repbuddy.httputils2service
sending message to waiting queue of uninitialized activity (submitjob)
Any help would be great guys:)
 

air cover

Member
Licensed User
Longtime User
Just unzipping and running the MySQL.zip in post #1 generates an error "org.json.JSONException: End of input at character 0
 

dioliew

New Member
Great tutorial.
But instead of ip 10.0.0.2 to access local pc from emulator, i've been using 10.0.2.2 and it solve the time out issue.
 

air cover

Member
Licensed User
Longtime User
Just unzipping and running the MySQL.zip in post #1 generates an error "org.json.JSONException: End of input at character 0

Is there a way to get the example app running?
 

air cover

Member
Licensed User
Longtime User
It was a server problem. It should now work.
It works great now, thank you!

Thank you! Thank you! Thank you!
Thank you! Thank you! Thank you!
Thank you! Thank you! Thank you!
Thank you! Thank you! Thank you!
 

air cover

Member
Licensed User
Longtime User
I have an app using MySQL, and I hope that one of you experts has seen this error/behavior before.

It only works every other time that you run the app! Weird.

It is fully working every other time. First, the app will work. Good. Full db access for the app. Then Exit the app normally. Run the app again...no db access.

This triggers an error in the app for a List object not being initialized, "Continue?" Say No. App shuts down hard. Run the app again. Works fully.
*saying Yes to that error message and then exiting app "normally" means that it still won't work the next time that you run it...must say "No" to the Continue question for the app to have db access the next time the app is run.

Works every other time. Am I missing some sort of db shutdown command that needs to be performed? Why does there have to be a hard shutdown from the Android OS for the next time that you run the app for it to be able to work with MySQL?







**the actual Android error message is:
"An error occurred in sub: java.lang.RuntimeException: Object should first be initialized (List).
Continue?
 
Last edited:
Status
Not open for further replies.
Top