Android Question Seeking Guidance on Optimizing Database Access for Scaling B4X App

A

amarnath

Member
Licensed User
Longtime User
I'm currently using B4J with JdbcSQL to connect directly to a remote MySQL database:

B4X: 
Public mysql As JdbcSQL
Public driver As String = "com.mysql.jdbc.Driver"
Public jdbcUrl As String = "jdbc:mysql://207.1*.2*.2*:3306/amarnq4g_dairy?useSSL=False&characterEncoding=utf8"
Public Username As String = "amarg_amarnath"
Public Password As String = "Amarnath@@1234"

However, saving data takes over 30 seconds—too slow for my growing user base (projected >1000 clients).

Would switching to RDC (Remote Database Connector) or using a custom API (e.g., api.php) significantly improve performance and scalability?

Given your expertise, which approach would you recommend for a high-performance B4X mobile app serving thousands of users?

Thank you for your time and guidance.
 
Sort by date Sort by votes

Star-Dust

Expert
Licensed User
Longtime User
30 seconds is very slow. PHP is definitely faster and more secure. It's risky to grant access to the database directly.
 
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
Thank you for your quick response.

I agree—30 seconds is unacceptably slow, and I now realize that direct database access is both inefficient and insecure, especially with over 1000 expected users. I’d like to switch to a PHP-based API (api.php) as you suggested.

Could you please share a simple example of how to:

Send data from a B4X app (B4A/B4J) to a PHP script via HTTP,
Insert or fetch records securely using prepared statements in PHP,
Return a JSON response back to B4X?
This would help me transition safely and efficiently.
 
Upvote 0

Star-Dust

Expert
Licensed User
Longtime User
Database access is always slow, like starting a car. PHP creates a single login for all queries; we often create a login for each individual query. PHP server also creates connecting pools and caches that speed up the process. Here are some examples, but you can find many more on the forum.

Thread '(PHP/MySQL/API) User Login App' https://www.b4x.com/android/forum/threads/php-mysql-api-user-login-app.117826/
 
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
My B4X app is fully functional, but I’m now rewriting the entire data layer to use a secure PHP API (instead of direct JDBC) to improve performance, security, and scalability for 1000+ users.

I’d greatly appreciate your guidance on:
  1. Hosting: I have both Single Domain Linux Hosting (USA-TX) and a BigRock VPS (KVM, India). Which is better suited for this load?
  2. PHP Version: Is PHP 8.1 or 8.2 preferred for stability with MySQL 8 and B4X compatibility?
  3. API Structure: Should I use a single api.php with an action parameter, or separate files (insert.php, update.php, etc.)?
Also, could you please share (or point to) a minimal but complete CRUD example that includes:
  • INSERT, UPDATE, SELECT, DELETE
  • Prepared statements (to prevent SQL injection)
  • JSON input (from B4X) and JSON output (to B4X)
  • Basic error/exception handling
I’ve studied your excellent thread:
(PHP/MySQL/API) User Login App
— but couldn’t find a similarly clean full-CRUD version.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
The thread suggested by Star-Dust is created by me.
Since you already have VPS and familiar with B4J, try B4J server.
Here is Web API Server v3 you can use together with B4X client project template.

[Project Template] Web API Server v3

Web API Server is now Pakai Version: 3.51 GitHub: https://github.com/pyhoon/pakai-server-b4j/tree/v3.51 Description: Web API Server with JSON response Database: SQLite, MySQL Template: Web API Server (3.51).b4xtemplate B4X Client App (B4A, B4i, B4J): Web API Client (2.00).b4xtemplate...
www.b4x.com

The latest version is v6 but only web client app is available, B4X clients are not provided. So I suggest you start with v3.5
 
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
I’m coming from a .NET background and have no HTML/PHP experience. I just need the simplest possible working example that:


  • Runs on shared hosting (cPanel + PHPMyAdmin)
  • Handles INSERT, UPDATE, SELECT, DELETE from B4X via HTTP + JSON
  • Uses prepared statements and config.php for DB credentials

I don’t need UI or HTML—just B4X sending data → PHP → MySQL → JSON response back to B4X.


If your api.zip already does this, could you confirm it’s safe to use as-is?
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
amarnath said:
I’m coming from a .NET background
Click to expand...
So you have experience in ASP.Net ?
amarnath said:
no HTML/PHP experience
Click to expand...
amarnath said:
I don’t need UI or HTML—just B4X sending data → PHP → MySQL → JSON response back to B4X.
Click to expand...
So you only want the Web API.

I can say, either you go for PHP or B4J route, you will need further understanding on both environment.
May I ask why you don't build with .NET?

amarnath said:
If your api.zip already does this, could you confirm it’s safe to use as-is?
Click to expand...
Yes, the source already provided the basic security features.
You can harden the security if you want.

Just download the zip and test it to understand. It is very straight forward.
If it is not what you want, pass.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
If you think my example has no CRUD, then you don't see the picture.

Register a user -> Insert (Create)
Login/List online users -> Select (Read)
Update user location/activate a user -> Update (Update)

No delete but you should able to do it yourself if you understand the above 3.
 
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
I’ve already spent 6 months building and refining my B4X app—so switching to .NET now isn’t practical. My goal is to finish quickly and securely with the least detour.


Since I’m on shared hosting (no B4J server allowed) and need a simple Web API (no HTML/UI), I’ll go with your PHP route.


I’ll download and test your api.zip right away. Just to confirm:

  • It supports full CRUD (INSERT/UPDATE/SELECT/DELETE) via B4X HTTP + JSON?
  • And works as-is on standard cPanel shared hosting?

If yes, that’s the fastest, safest path to launch for 1000+ users.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
I have a production server that is based on this framework that power the B4A and B4i app available on Play and App store.
However, I have added AES encryption to encryprt the data which is supported by PHP, B4A and B4i.
 
Upvote 0

hibrid0

Active Member
Licensed User
Longtime User
Creo que Jrdc2 es una opción más rápida, mejor y más fácil.

Mira esto:
Todo con B4J y tienes un vps, no necesitas php.

TUTORIAL BÁSICO DE JRDC2

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

RDC is a middleware server that makes it simple to safely connect clients and remote SQL database servers. jRDC2 is the latest version. All new projects should use this version. jRDC2 is made of two components: - B4J server. The server receives the requests from the clients, issues the SQL...
www.b4x.com
 
Last edited:
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
hibrid0 said:
I think Jrdc2 is more faster and better option and more easy.
All with B4J and you have a vps, don't need php.
Click to expand...

Thanks for the suggestion!

I’m a bit confused—which is truly the best option for me:

  • PHP API (works on shared hosting, already tested), or
  • JRDC2 (B4J-based, needs VPS)?

I do have a VPS (BigRock, KVM, Linux) with root/cPanel access, but I also have shared hosting.
My priority is:
Fast performance (1000+ users)
Simple maintenance
Secure data transfer (already using AES in B4A/B4i)


You mentioned JRDC2 is faster, simpler, and avoids PHP entirely—that sounds ideal if I can run it on my VPS.


Could you please give me a short step-by-step guide to set up JRDC2 on my Linux VPS? Specifically:

  1. What to install (Java, B4J, etc.)
  2. How to deploy the JRDC2 server (as a service?)
  3. How to configure MySQL connection securely
  4. How B4A/B4i connect to it (IP/port, encryption)

If JRDC2 really is easier, faster, and more B4X-native, I’d prefer it—especially since I’m not a PHP developer.
 
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
mcqueccu said:
Do what you can afford. For me, I dont have VPs so all my apps with Database runs on PHP API (on Shared Hosting) and it works pretty fast
Click to expand...
Thanks for your advice—I really appreciate it.


Since I do have a VPS, I’ll go with JRDC2 to keep everything in B4X. But just in case, could you please share a minimal PHP CRUD example (INSERT/UPDATE/SELECT/DELETE) that works with B4X over HTTP + JSON?


Even a very basic api.php with config and one table (like “users”) would help me compare or fall back if needed.
 
Upvote 0

hibrid0

Active Member
Licensed User
Longtime User

Jrdc2 is easy.
On server app.
Add the library, connect the db, Config sql commands.

In the client add modules and start to using jrdc2 commands (based in your jrdc2 Config file).

This is secure, because the db connection is marked on b4j server side, and is more faster.
And your will execute many jrdc2 commands in one request.

And other advantage is you will change the db(Sqlite, mysql, mssql etc) without change the code on the client or server. Maybe update some sql sentences if is not sql standard.

And if you need api rest for other apps non b4x, in the forum found a jrdc2 mod have features to mount many dbs, generate json and more.

To run the B4J Server app.
Install Java, example install it on /opt/Java.
Create a folder for you app, example /opt/mysuperfasterapp/myapp
Chmod+x myapp.
Create a service to execute myapp and check if is running or crash to restart.
Open the firewall port for your app, in my case:
ufw allow 5700
Enjoy, now you will connect to your b4j server app.
 
Last edited:
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
I’ve tested a basic PHP API call from B4A, and it works—but I’m not sure if my code is optimal or secure.


B4X: 
Sub btn_submit_Click
    If EditText1.Text = "" Then
        ToastMessageShow("Enter Name", False)
        Return
    End If
   
    Dim j As HttpJob
    j.Initialize("insert", Me)
   
    Dim url As String = "https://dairychairman.com/api.php?token=123456&action=insert&name=" & EditText1.Text
    j.Download(url)
End Sub



Sub JobDone(j As HttpJob)
    If j.Success = False Then
        Log("Error: " & j.ErrorMessage)
        ToastMessageShow("Error: " & j.ErrorMessage, False)
        j.Release
        Return
    End If
   
    Dim res As String = j.GetString
    Log("Response: " & res)
   
    Try
        Dim parser As JSONParser
        parser.Initialize(res)
        Dim rootMap As Map = parser.NextObject
       
        Dim status As String = rootMap.Get("status")
        Dim msg As String = rootMap.Get("msg")
       
        ToastMessageShow(msg, False)
       
        If status = "success" Then
            EditText1.Text = ""
        End If
       
    Catch
        Log("Error parsing response")
        ToastMessageShow("Error: Invalid response", False)
    End Try
   
    j.Release
End Sub


Private Sub btn_update_Click
    If SelectedID = 0 Then
        ToastMessageShow("Select a record first", False)
        Return
    End If

    Dim j As HttpJob
    j.Initialize("update", Me)

    Dim data As Map
    data.Initialize
    data.Put("token", TOKEN)
    data.Put("action", "update")
    data.Put("id", SelectedID)
    data.Put("name", txt_name.Text)
    data.Put("mobile", txt_mobile.Text)
    data.Put("amount", txt_amount.Text)

    j.PostMultipart(API_URL, data, Null)
End Sub
If you have a minimal, secure api.php + B4A example that matches this flow (insert/update/select/delete), it would help me lock this down correctly.

PHP: 
<?php
header("Content-Type: application/json");

// SECURITY TOKEN
$API_TOKEN = "123456";  

// Validate Token
if (!isset($_GET['token']) || $_GET['token'] != $API_TOKEN) {
    echo json_encode(["status" => "error", "msg" => "Invalid Token"]);
    exit;
}

// MySQL Connection
$host = "localhost";
$user = "amarnat2_Chairman";
$pass = "Chairman&&0815";
$db   = "amarnat2_chairmandb";

$conn = new mysqli($host, $user, $pass, $db);

if ($conn->connect_error) {
    echo json_encode(["status" => "error", "msg" => "DB Connection Failed"]);
    exit;
}

$action = $_GET['action'];

// ------------------------------
// INSERT
// ------------------------------
if ($action == "insert") {
    $name = $_GET['name'];
    $mobile = $_GET['mobile'];
    $amount = $_GET['amount'];

    $stmt = $conn->prepare("INSERT INTO customers (name, mobile, amount) VALUES (?,?,?)");
    $stmt->bind_param("ssd", $name, $mobile, $amount);

    if ($stmt->execute()) {
        echo json_encode(["status" => "success", "msg" => "Inserted"]);
    } else {
        echo json_encode(["status" => "error", "msg" => "Insert Failed"]);
    }
}

// ------------------------------
// UPDATE
// ------------------------------
if ($action == "update") {
    $id = $_GET['id'];
    $name = $_GET['name'];
    $mobile = $_GET['mobile'];
    $amount = $_GET['amount'];

    $stmt = $conn->prepare("UPDATE customers SET name=?, mobile=?, amount=? WHERE id=?");
    $stmt->bind_param("ssdi", $name, $mobile, $amount, $id);

    if ($stmt->execute()) {
        echo json_encode(["status" => "success", "msg" => "Updated"]);
    } else {
        echo json_encode(["status" => "error", "msg" => "Update Failed"]);
    }
}

// ------------------------------
// DELETE
// ------------------------------
if ($action == "delete") {
    $id = $_GET['id'];

    $stmt = $conn->prepare("DELETE FROM customers WHERE id=?");
    $stmt->bind_param("i", $id);

    if ($stmt->execute()) {
        echo json_encode(["status" => "success", "msg" => "Deleted"]);
    } else {
        echo json_encode(["status" => "error", "msg" => "Delete Failed"]);
    }
}

// ------------------------------
// SELECT ALL
// ------------------------------
if ($action == "select") {
    $result = $conn->query("SELECT * FROM customers ORDER BY id DESC");
    $rows = [];

    while ($r = $result->fetch_assoc()) {
        $rows[] = $r;
    }

    echo json_encode(["status" => "success", "data" => $rows]);
}

$conn->close();
?>
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
Just decide which server you want.
For PHP, you already have a good example.
I also have many jRDC2 and B4J server examples if you decide not to use PHP.
If you keep asking but no action taken then it will not lead you anywhere.
 
Upvote 0
A

amarnath

Member
Licensed User
Longtime User
aeric said:
Just decide which server you want.
For PHP, you already have a good example.
I also have many jRDC2 and B4J server examples if you decide not to use PHP.
If you keep asking but no action taken then it will not lead you anywhere.
Click to expand...


You're absolutely right—I need to decide and act.

Since I have a VPS and prefer staying fully in B4X, I’ve decided to go with jRDC2.

As someone with no HTML/PHP experience, I’d really appreciate the simplest possible jRDC2 + B4J server example that includes
  • Connecting B4A/B4i to MySQL via jRDC2
  • Basic INSERT, UPDATE, SELECT, and DELETE operations
  • Instructions to run it on a Linux VPS
If you could point me to one clear, ready-to-deploy jRDC2 example—similar to your PHP login demo, but minimal and complete—I’ll deploy it on my VPS immediately and move forward without delay.

Just a bare-bones, working version—no extra features needed.

Thank you for your patience and guidance!
 
Upvote 0
You must log in or register to reply here.

Similar Threads

S
  • Question
Android Question New user learning - any guidance please?
Replies
8
Views
893
SparkOut
S
  • Article
B4A Library [B4X] ComboBoxPlus - ComboBox with individual color configurable items
Replies
9
Views
11K
Jerryk
J
  • Article
B4A Library [B4X] PreoptimizedCLV - Lazy loading extension for xCustomListView
2 3
Replies
53
Views
40K
Erel
  • Locked
  • Article
B4J Library [B4X] [XUI] BitmapCreator - Pixels, Drawings and More
Replies
10
Views
21K
Erel
  • Article
B4A Library VideoView optimized for orientation-change
Replies
3
Views
7K
analizer3816
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…