The State of the MySQL

[jflaplante]

I've spent most of the day reading and testing various ways to connect to a MySQL database. I my tests were made with a Nexus 7 table running Android 4.2.1. Here are my findings and questions...

There mostly seem to be 3 different ways to connect:

1- The web service way:

Connect Android to MySQL Database Tutorial

This way involves data transfers in JSON format and the use of an intermediary server. I have been able to implement it and use it but only with small datasets. When I limit the result to 30 or less records, it always work fine but when I go over that limit, I often get errors of incomplete process.

2- The JDBC way:

MySQL Library with jdbc

This method talks directly with the MySQL server so the port must be open. I've never been able to use it. I get no error but also no results so far.

3- The ADO way:

This method also uses the Oracle JDBC driver but implements different ways of interactions than method #2. As with method #2, I haven't been able to get any results even when getting no errors.




Remote server connectivity is a hot topic for me and I'm still relatively new to b4a. I got the hang of b4a very quickly but I still have a lot to learn. Almost every time I needed something, I've found the answer reletively quickly in the forum or the various docs. I'm impressed! I've been doing web/sql programming since 1996 so app programming is a very different world. That being said, the remote sql connectivity so far seems like an inconsistent process.

What are the database junkies doing for a good time around here? Is there a prettier and/or more consistent way to do this? I will continue testing but I was just inquiring to see if I can save some time.

Thanks in advance.

JF.

 

[Erel]

I think that the web service based solution will be the most robust as it should be able to handle connections problems.

I plan to update it to use HttpUtils2 and I will also add support for compression. Compression should be very efficient in this case.

 

[warwound]

If you're using a PHP script as a web service then you can easily make that script return compressed output by adding the following line to it:

ob_start('ob_gzhandler'); //   comment/uncomment to enable compression

That needs to be placed in the PHP script before the script has output any data otherwise a 'headers already sent' error will occur.

The script output will now be compressed using gzip compression.
I always use this in my scripts and B4A handles the compressed response automatically.

Text output compresses particularly well, size reductions of over 70% are typical.

Take a look here: WhatsMyIP.org | HTTP Compression Test

Point that page to your (uncompressed) script and it'll report the current script output size and if the output is not already compressed it will estimate the size of the script output if it was compressed.

Martin.

 

[jflaplante]

I think that the web service based solution will be the most robust as it should be able to handle connections problems.

I plan to update it to use HttpUtils2 and I will also add support for compression. Compression should be very efficient in this case.

Thanks!, I figured that out today while testing.

My Initial tests were based on the SQL example which is almost 2 years old. While reading the doc I read that the 'GetString' method should not be used with Android 4.x.

So I re-wrote the whole thing with httputils2 and used my web programming language which is...don't laugh... Coldfusion. It's actually alot better than most people think! Since coldfusion code is database independent, I was able to use older clipper .dbf files to fetch my results from. I can now use any database supported by ColdFusion to get results.

I need to work a little more on the JSON data because right now, I can't directly transfer the JSON encoded Coldfusion Query object, I have to convert it to an array before sending it in JSON format and transferring it to b4a.

On a local lan, it take less than 5 seconds to fetch 1200 records from a server and insert them in a local sqlite database. I'm satisfied with that result but the compression sure will help on a remote connection!

The b4a ecosystem is evolving so rapidly that sometimes, important pieces of documentation are isolated somewhere in a single post.

Thanks for the replies and keep on the good work!

JF.

 

[mangojack]

My Initial tests were based on the SQL example which is almost 2 years old. While reading the doc I read that the 'GetString' method should not be used with Android 4.x.

Having just started with SQLLite is this also the case or only applicable to MySQL or remote db's ? If so what other method is to be used ?

Cheers mj

 

[jflaplante]

Having just started with SQLLite is this also the case or only applicable to MySQL or remote db's ? If so what other method is to be used ?

Cheers mj

I'm speaking about remote dbs, the current sql library and the dbutils module are perfectly able to do wonderfull stuff to your sqlite databases.

Both have nice tutorials here:

http://www.b4x.com/forum/basic4android-getting-started-tutorials/6736-sql-tutorial.html#post39108

http://www.b4x.com/forum/basic4andr...475-dbutils-android-databases-now-simple.html


Good luck with your projects.

JF.