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.

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: 6,961
  • jRDC2.zip
    5.5 KB · Views: 2,476
Last edited:

Dey

Active Member
Licensed User
Longtime User
Hello to all
I have a big doubt o_O
I use DBRequestManager in most instances simultaneously?
therefore you may be running example of starter in a timer and other classes?
it does on
Type DBResult (Tag As Object, As Map Columns, Rows As List)
Type DbCommand (Name As String, Parameters () As Object)
which they are declared in one instance
You can modify an instance for data error?
I hope I was clear
Thank you
 

alan dodd

Member
Licensed User
Longtime User
Hi Erel, I am using successfully RDC on > 30 android devices to query (write/read) remote mysql server via wifi
I would like to be able to write/read to same mysql server also from several arduinos with wifi or ethernet shield. Do you think it would be possible / and easy to port?
 

achtrade

Active Member
Licensed User
Longtime User
It is up to you. For new projects it is recommended to use jRDC2.
I would like to use this new jRDC2, but I'm developing with B4A, I don't know if this is an issue.

I couldn't start my server with this:

B4X:
nohup path_to_java/bin/java -jar somejar.jar > nohup.out &

What I don't understand is the "somejar.jar" part
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
I would like to use this new jRDC2, but I'm developing with B4A, I don't know if this is an issue.
Go over the tutorial in the first post.

You need to compile jRDC2 with B4J (run it in release mode) and it will create a jar file under the Objects folder. The jar file is the compiled application.
 

Dey

Active Member
Licensed User
Longtime User
a greeting to all
I saw that there are updates to
sqlite-jdbc-3.7.2.jar
example
sqlite-jdbc-3.8.7.jar
it is advisable to switch to this?
Thank you
 

IlCasti

Active Member
Licensed User
Longtime User
Hi there
I worked with RDC "first version" in the past. All works perfectly.
Yesterday i started to use new version (jRDC2) Nothing worked until now. Not properly.
I can reach server status connection:
RemoteServer is running (09/06/2016 12:21:15)
Connection successful.
What does it mean?

HTTP ERROR: 500

Problem accessing /rdc. Reason:
unknown method
Powered by Jetty:// 9.3.z-SNAPSHOT

I can't connect to db
This is the b4a line error from log: Error: org.apache.http.conn.ConnectTimeoutException: Connect to /192.168.43.28:3307 timed out
I use the same file .properties "shared" from old and new RDC
How can understand where i'm wrong? :(
Thank you.

PS: i use my laptop as server and development with mysql and my lg4 with usb cable connection in debug mode
 

marcick

Well-Known Member
Licensed User
Longtime User
Did you modify the url sintax ? I remember there is a small difference and you need to add something after the port number
 

IlCasti

Active Member
Licensed User
Longtime User
Did you modify the url sintax ? I remember there is a small difference and you need to add something after the port number

You mean in b4a project?
Something like /RDC? If so, i tried, but same result..
 

marcick

Well-Known Member
Licensed User
Longtime User
Yes, /rdc
I don't remember if there are other differences but well reading the tutorial I didn't have any particular problem moving to jrdc2 ... Sorry I can't help more
 

IlCasti

Active Member
Licensed User
Longtime User
Yes, /rdc
I don't remember if there are other differences but well reading the tutorial I didn't have any particular problem moving to jrdc2 ... Sorry I can't help more

It works well now.. it's case sensitive.. stupid me.. thank you!
 

incendio

Well-Known Member
Licensed User
Longtime User
When running with DBRequestManager on a non ui app, got an error :

main._appstart (java line: 93)
java.lang.RuntimeException: java.lang.IllegalStateException: Toolkit not initial
ized
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:114)
at anywheresoftware.b4a.objects.Timer$TickTack$1.run(Timer.java:118)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(Simple
MessageLoop.java:30)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:131
)
at b4j.example.main._appstart(main.java:93)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.example.main.main(main.java:29)
Caused by: java.lang.IllegalStateException: Toolkit not initialized
at com.sun.javafx.application.PlatformImpl.runLater(PlatformImpl.java:27
3)
at com.sun.javafx.application.PlatformImpl.runLater(PlatformImpl.java:26
8)
at javafx.application.Platform.runLater(Platform.java:83)
at anywheresoftware.b4j.objects.FxBA.postRunnable(FxBA.java:17)
at anywheresoftware.b4a.keywords.Common.CallSubDelayed4(Common.java:500)

at anywheresoftware.b4a.keywords.Common.CallSubDelayed2(Common.java:484)

at b4j.example.httpjob._postbytes(httpjob.java:215)
at b4j.example.dbrequestmanager._vv6(dbrequestmanager.java:203)
at b4j.example.dbrequestmanager._vv1(dbrequestmanager.java:71)
at b4j.example.main._v6(main.java:234)
at b4j.example.main._tm_tick(main.java:246)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
... 12 more
 
Last edited:
Status
Not open for further replies.
Top