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

Status
Not open for further replies.

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 commands against the database and returns the results.
In many cases the server will be hosted on the same computer that hosts the database server.
- Client module. The client which is compatible with B4A, B4J and B4i is responsible for sending the requests and handling the responses.

jRDC2 can work with any database that provides a JDBC driver. All popular databases are supported.
It is much more powerful than the PHP based solution and it has excellent performance.
It is also safer as the SQL commands are set in the server side.

Server configuration

1. Add the relevant JDBC jar file to the additional libraries folder.
2. Add a reference to this jar with:
B4X:
#AdditionalJar: mysql-connector-java-5.1.27-bin
3. Edit config.properties file that is located in the Files tab.


For example:

SS-2013-08-04_16.10.20.png


Note that the configuration fields are case sensitive.

Updates

- v2.21 - Date and time fields are automatically converted to ticks (long numbers) in SELECT queries.

DriverClass / JdbcUrl - The values of these two fields are specific to each database platform. You will usually find the specification together with the required driver jar file.

User / Password - Database user and password.

ServerPort - The Java server will listen to this provided port.

Debug - Option not used any more. It is automatically enabled when you run the server in debug mode (from the IDE).

SQL Commands - A list of commands. Each command starts with 'sql.'. The commands can include question marks (parameterised queries). Question marks will be replaced with the values provided by the Android clients. Note that the command name is case sensitive and it doesn't include the 'sql.' prefix.

4. Run the program and test it locally by putting this link in the local browser: http://localhost:17178/test

Note that the server port must be open for incoming connections in the firewall.

Client configuration

1. Add to Main module (must be in Main module):
B4X:
Sub Process_Globals
   Type DBResult (Tag As Object, Columns As Map, Rows As List)
   Type DBCommand (Name As String, Parameters() As Object)
   Private const rdcLink As String = "http://192.168.0.6:17178/rdc"
End Sub
Change the link with the ip address or host name of the server hosting jRDC2. It must end with /rdc.

2. Add DBRequestManager class to your project.
It depends on RandomAccessFile and OkHttpUtils2 libraries (or the matching libraries in B4J or B4i).

3. Add these two subs:
B4X:
Sub CreateRequest As DBRequestManager
   Dim req As DBRequestManager
   req.Initialize(Me, rdcLink)
   Return req
End Sub

Sub CreateCommand(Name As String, Parameters() As Object) As DBCommand
   Dim cmd As DBCommand
   cmd.Initialize
   cmd.Name = Name
   If Parameters <> Null Then cmd.Parameters = Parameters
   Return cmd
End Sub

A new DBRequestManager is created for each request.

Example of sending a SELECT request:
B4X:
Sub GetRecord (id As Int)
   Dim req As DBRequestManager = CreateRequest
   Dim cmd As DBCommand = CreateCommand("select_animal", Array(id))
   Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
   If j.Success Then
       req.HandleJobAsync(j, "req")
       Wait For (req) req_Result(res As DBResult)
       'work with result
       req.PrintTable(res)
   Else
       Log("ERROR: " & j.ErrorMessage)
   End If
   j.Release
End Sub
The select_animal command is defined in the config file:
B4X:
sql.select_animal=SELECT name, image, id FROM animals WHERE id = ?

Note that you can make multiple requests at the same time.
The result is a DBResult object.

Example of sending an INSERT request:
B4X:
Sub InsertRecord (Name As String)
   Dim cmd As DBCommand = CreateCommand("insert_animal", Array(Name, Null))
   Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
   Wait For(j) JobDone(j As HttpJob)
   If j.Success Then
       Log("Inserted successfully!")
   End If
   j.Release
End Sub
In this case a single command was sent. You can send multiple commands at once.
The insert_animal command is defined in the config file:
B4X:
sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
     id INTEGER PRIMARY KEY AUTO_INCREMENT,\
     name CHAR(30) NOT NULL,\
     image BLOB)
sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)


Notes

- Running a B4J server program on your hosted server: [server] Run a Server on a VPS
- There are three utility methods in DBRequestManager: FileToBytes, ImageToBytes and BytesToImage. You can use them when working with blobs.
- Extending jRDC2 to support B4R: https://www.b4x.com/android/forum/threads/rdc-based-on-mqtt.72416/#content

Updates

v2.23 - Adds support for CLOB fields (large strings).
v2.22 - Fixes an issue with null time values.
v2.21 - Date and time fields are converted to ticks (long numbers) automatically in select queries.
Note that the VERSION1 code is excluded by default. It is only relevant if there are clients running the old RDC code.


Please start a new thread for any question you have.
 

Attachments

  • DBRequestManager.bas
    4.4 KB · Views: 4,902
  • jRDC2.zip
    5.5 KB · Views: 183
Last edited:

Angel Garcia

Member
Licensed User
Hi All,
Maybe this is a silly question.
I'm using RDC on my project and works terrific on LAN (Same Wifi for PC/server and Android device) :).
Now I want to make it work on the internet.
I tried with port forwarding from the internet of my home with no success, i don't understand how to configure it correctly, been trying by trial and error. :(
I want to know if somebody already did this successfully?, and can put me in the right path, before moving to rent a VPS on the internet
Please help!
 

Angel Garcia

Member
Licensed User
You have specified a port in the configuration (not the one for the database) you have to share in the firewall.

Greetings

I set in the config file:
JdbcUrl=jdbc:mysql://localhost:3306/mydb
User=AndroEstilo
Password=**********
ServerPort=17174

In the Windows Firewall i created 2 rules:
Inbound for TCP port 17174
Outbound for TCP port 17174

Now in the router i added some port forwarding to 17174 but no connection triggers =(
 

Angel Garcia

Member
Licensed User
hmm, then it is not a problem of the RDC. Any other Firewall? Can you call it from the test link?
Yes, actually it in the browser i test:
http://127.0.0.1:17174/?method=test

And i got the correct answer:
RemoteServer is running (Wed Jan 03 20:05:54 CST 2018)
Connection successful.

I think is my port forwarding rule somehow, in the B4A Code i pass my public IP with the port: 187.XXX.XXX.XX:17174
I have in my port forwarding rule:

upload_2018-1-3_20-11-14.png


What am i doing wrong?
 

A Z M JANNAT UL KARIM

Member
Licensed User
Hi Angel, I had successfully forwarded port through Mikrotik Router with no issues :). You need to re-configure two steps if your apps run successfully in LAN
-> Configure in router - Forward the port. You need a Real IP. Test from the PC with URL Also try to avoid port 80 and 8080 as it generally use for router configuration page. You can use other port if you like ...
--> Configure in Apps - Change the connection string to Real IP from Local IP.

Have a nice day :)
 

Angel Garcia

Member
Licensed User
Configure in router - Forward the port. You need a Real IP. Test from the PC with URL Also try to avoid port 80 and 8080 as it generally use for router configuration page. You can use other port if you like ...
I think this is where im failing, :( how i get a real IP?, i just know my public IP: 187.XXX.XXX.XX, and my LAN IP: 192.168.100.12

-> Configure in Apps - Change the connection string to Read IP from Local IP.
How i change mi connection string?, on my B4A code the connection string is : http://187.XXX.XXX.XX:17174, It is my public IP with the open port


Hi Angel, which port you are forwarding here in router. In External Port I see that you selected 80. Then your URL should be
http://187.XXX.XXX.XX:80/?method=test instead of 187.XXX.XXX.XX:17174. It's the matter for forwarding port on router, not from your pc if you try to access from outside world. Thanks
Ok let me try it
 

Angel Garcia

Member
Licensed User
After some reading i found this:
upload_2018-1-3_21-51-21.png


Is the port restricted involved?
upload_2018-1-3_21-53-37.png


This is what i need to change?
Is someone facing the same issue?
@karim, you have this configuration on your router?
To everyone thanks for your help.
Regards
 

A Z M JANNAT UL KARIM

Member
Licensed User
Hi Angel, seems to be you are not using Real IP. So what I think you can't access the server from outside without Real IP. You can talk with your ISP to give you a Real IP or they can forward a port for your router through which you can connect :)
 

Angel Garcia

Member
Licensed User
Hi Angel, seems to be you are not using Real IP. So what I think you can't access the server from outside without Real IP. You can talk with your ISP to give you a Real IP or they can forward a port for your router through which you can connect
Ok, i'll make a few phone calls. I'll keep you guys updated.
Thanks all for your help
 

Angel Garcia

Member
Licensed User
Hi All,
Finally you guys were right, my IPS was blocking ports.
After i rented a VPS, installed everything and it worked great with the public IP the provided me.
My IPS su%$&
Thanks all for your help!
 
Status
Not open for further replies.
Top