B4J Library jRDC-Multi DB with JSON handler

jRDC2-Multi Mod (B4J Server)​

1. Overview​

This post was written with the help of an AI, so if you notice too much formality, too much words, or too few words, I am 'totally innocent'.

This project is a modded version of the original jRDC2 Server, and the multi-database support is based on this modification, also by Erel.

This version builds upon those concepts by adding a new JSON-based handler for modern web clients, while maintaining full compatibility with B4X clients.

The primary motivation for creating the JSON handler was the need for my NodeJS-based WhatsApp Bot to securely access the same database as my other B4A applications.

This opens up connectivity to any modern technology stack that can handle HTTP requests and JSON (JavaScript, NodeJS, React, Vue, etc.).

By using the implicit security of the jRDC server architecture (the config.properties files), this modification avoids the need to expose database credentials in client-side code or other backend scripts (like PHP), keeping all SQL commands and connection details safely on the server.

---

2. Key Enhancements​

• Multi-Database Support: Natively manage connections to up to 4 different databases simultaneously, each with its own configuration file.
• Dual Request Handlers:
  • Classic Handler: Full compatibility with the B4X DBRequestManager.
  • JSON Handler: A new endpoint that allows web clients to interact with the server using JSON requests and responses.
• Dynamic Database Selection: Choose which database to query directly from the request URL, making it easy to switch between data sources.
• Remote Administration: Check the server status, reload all configuration files (without restarting the server), or restart the server via simple HTTP requests.
• Security Validations: Automatically validates command existence and parameter counts on all incoming requests to prevent common errors.

---

3. Configuration​

3.1. Configuration Files​

The server can load up to four database configurations. It will only load the ones it finds, so you don't need to create all four.

The file naming convention is crucial:

• For the primary database (DB1): config.properties
• For the second database (DB2): config.DB2.properties
• For the third database (DB3): config.DB3.properties
• For the fourth database (DB4): config.DB4.properties

Important Notes:

• The server port is taken only from the main config.properties file. Port settings in other files are ignored.
• Connection details (JdbcUrl, User, Password, etc.) are read from their corresponding configuration file.

3.2. Adding Additional Database Drivers​

To connect to other database types (e.g. Oracle), you must add the driver's .jar file to the project before compiling. In the Main module, add a line like this:

' The name of the .jar file, in this case located at "C:\Path\To\Libs\ojdbc11.jar"
#AdditionalJar: ojdbc11

When compiled, the driver will be included in the final server .jar, so there's no need to copy it separately to the production directory.

---

4. Security Validations​

The server performs two automatic checks on all incoming requests (both B4X and JSON handlers) before executing a database query:

1. Command Existence Check: The server verifies that the requested command name (e.g., "get_user") exists as a valid SQL command key in the corresponding .properties file. If not found, it returns an error.
2. Parameter Count Match: If the SQL command expects parameters (contains ? placeholders), the server compares the expected count with the number of parameters received in the request. If they do not match, it returns an error.

These validations provide immediate and clear feedback for malformed requests.

---

5. Using the Classic Handler (for B4X Clients)​

This handler maintains full compatibility with DBRequestManager. The database is selected dynamically by adding a path to the URL.

• To use config.properties => http://your-domain.com:8090
• To use config.DB2.properties => http://your-domain.com:8090/DB2
• To use config.DB3.properties => http://your-domain.com:8090/DB3
• To use config.DB4.properties => http://your-domain.com:8090/DB4

---

6. Using the JSON Handler (for Web Clients)​

This handler is designed for clients that communicate via JSON, such as JavaScript web applications.

6.1. Endpoint and Request Methods​

All requests are sent to the /DBJ endpoint. The handler is flexible and accepts data in two ways:

Recommended Method: POST with a JSON Body

This is the standard and cleanest way for modern APIs.

• HTTP Method: POST
• URL: http://your-domain.com:8090/DBJ
• Required Header: Content-Type: application/json
• Body (Payload): The JSON object is sent directly in the request body.

Example Body:

{
  "dbx": "DB2",
  "query": "get_user",
  "exec": "executeQuery",
  "params": [
    "CDAZA"
  ]
}

Legacy Method: GET with 'j' Parameter

This method is maintained for backward compatibility or for simple GET requests.

• HTTP Method: GET
• URL: The entire URL-encoded JSON is sent as the value of the j parameter.

Example with GET:

http://your-domain.com:8090/DBJ?j={"dbx":"DB2","query":"get_user","exec":"executeQuery","params":["CDAZA"]}

6.2. JSON Payload Format​

The structure of the JSON object is the same for both methods:

{
  "exec": "executeQuery",
  "query": "sql_command_name",
  "dbx": "DB1",
  "params": [
    "value1",
    123
  ]
}

• exec: Either executeQuery (for SELECT statements) or executeCommand (for INSERT, UPDATE, DELETE).
• query: The name of the SQL command as defined in the corresponding config file (e.g., select_user).
• dbx (optional): The database key (DB1, DB2, etc.). If omitted, DB1 is used by default.
• params (optional): An object containing the parameters for the SQL query.

6.3. JSON Responses​

The server's response is always in JSON format and includes a boolean success field.

• If success is true, the data will be in the result key.
• If success is false, the error message will be in the error key.

---

7. Server Administration​

You can run management commands directly from a browser.

Note: Access to the /manager endpoint is protected. The credentials are:

• Username: admin (this is hardcoded in the server).
• Default Password: 12345

It is strongly recommended to change the password after your first login.

7.1. Public Commands (No Authentication)

• Ping Server:http://your-domain.com:8090/ping
  • Returns a simple PONG to confirm the server is running.
• Check Detailed Status:http://your-domain.com:8090/test
  • Shows information about the database connections and general status.

7.2. Protected Commands (Authentication Required)

These commands require an admin username and password to be configured in the main config.properties file.

• Reload Configuration: http://your-domain.com:8090/manager?command=reload
  • Rereads all config.*.properties files without restarting the server.
• Restart Server (Standard):http://your-domain.com:8090/manager?command=rsx
  • Executes the start.bat, start2.bat, and stop.bat scripts.
• Restart Server (with PM2): http://your-domain.com:8090/manager?command=rpm2
  • Executes reiniciaProcesoPM2.bat and assumes the process name is RDC-Multi. The .bat file may need to be modified if your process name is different.