Android Question B4A connection with Mysql database

[ciapw]

Hi all.. i am currently doing a project to make my own payment and top up system using rfid card, and rfid reader obviously. I have made a mysql database containing the data needed on my raspberry as the web server. Honestly, i didnt know much about database connection in B4A, since this is my first shot
I would like to know if is there a way to connect my android device to my pre-made database? Can we use android device to insert new data to our database, compare the data and read it?
Looking forward to the replies
And yeah, i have been searching tutorials about this but it makes me confuse, Remote Database connector,DbUtils, JDBc

 

[Erel]

You have two options:
1. Connect directly with JdbcSQL: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/
2. Connect with a middleware server that will also run on the RPi with jRDC2. In most cases this is the better option.

 

[ciapw]

You have two options:
1. Connect directly with JdbcSQL: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/
2. Connect with a middleware server that will also run on the RPi with jRDC2. In most cases this is the better option.

Thanks for the reply Erel, could you please tell me more about the middleware server? I am using a apache2 server in my Rpi. Is that correct?

 

[Erel]

See the tutorial: https://www.b4x.com/search?query=jRDC2

 

[ciapw]

Okay I will take a look at it. many thanks

 

[KMatle]

apache2 server in my Rpi

You can use PHP, too.

 

[ciapw]

You can use PHP, too.

thankyou for the option.. but i want to ask something.. by using php does it mean we need an http? can we do it using local ip address not an http???

 

[KMatle]

thankyou for the option.. but i want to ask something.. by using php does it mean we need an http? can we do it using local ip address not an http???

Both. It's easier than you might think:

In my test environment I do a call with the ip address like this

Private const ServerName As String = "http://192.168.178.23"
    Private const ScriptPath As String = "/folderunderHtDocs/scriptname.php"

In prod it's via my website's address:

Private const ServerName As String = "https://mywebsite.com"
    Private const ScriptPath As String = "/folderunderHtDocs/scriptname.php"

So I can switch very easy between test and prod.

The HttpJob (example):

Dim MyJob As HttpJob
    MyJob.Initialize(JobName, Me)
    MyJob.PostString(ServerName & ScriptPath, JSONstring)

As you see I use a JSON-string

- it's a list with maps converted to a JSON-string = on the php side just an array containing arrays

Why do I use JSON data?

- it's very flexible. Just create a list and then add as much maps as you like
- after you convert the list to a JSON string it's just a string
- even better: you can encrypt it (e.g. AES) and decrypt it on the php side

For a login procedure I do the following (it's an older example not using wait... for...)

JobName="Login"
    Dim Parms As Map
    Parms.Initialize
    Parms.put("Action", "Login")
    Parms.put("uname", UserTF.text)
    Parms.put("upw", SHA256Hash(PWTF.text)) 'Hash the pw
    Parms.put("apphash", AppHash) 'another security method
    Parms.put("imei", imei) 'paranoia mode
    StartJob(Parms)

in "StartJob"

Sub StartJob (p As Map)
    JsonList.Initialize
    JsonList.add(p)
 
    Dim JSONGenerator As JSONGenerator
    JSONGenerator.Initialize2(JsonList)
 
    Dim JSONstring As String
    JSONstring = JSONGenerator.ToString
    Log(JSONstring)
  
    Dim MyJob As HttpJob
    MyJob.Initialize(JobName, Me)
    MyJob.PostString(ServerName & ScriptPath, JSONstring)
   
    Log("Starting Job... " & DateTime.Time(DateTime.Now))
End Sub

$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
    mysqli_select_db($con,$db) or die(mysqli_error());
    mysqli_query($con,"SET CHARACTER SET utf8");
    mysqli_query($con,"SET NAMES 'utf8'");
   
    $ip=$_SERVER['REMOTE_ADDR'];

    $json = file_get_contents("php://input"); //Get the string = just get all the content

    $jsall = array();
    $jsone = array();
    $jsall=json_decode($json, true); //Get the list

    $jsone=$jsall[0]; //Get the first map (you can add more maps if you like, example: Inserting 100 rows in one transaction)
   
   
    $action = $jsone["Action"];

switch ($action)
    {
Case "Login":
        $upw=stripslashes(mysqli_real_escape_string($con,$jsone["upw"]));
        $uname=stripslashes(mysqli_real_escape_string($con,$jsone["uname"]));
        $imei=stripslashes(mysqli_real_escape_string($con,$jsone["imei"]));
         
        $stmt = $con->prepare("SELECT * FROM users where uname = ?");
        $rc=$stmt->bind_param("s", $uname);
        $rc=$stmt->execute();
        $q = $stmt->get_result();
        $count=mysqli_num_rows($q);
        $stmt->close();

 //do some checks (user known, pw, hash, whatever)

   }

PS: In my prod environment I do a lot of more security checks. This example is very simple.