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,919
  • jRDC2.zip
    5.5 KB · Views: 200
Last edited:

Anser

Well-Known Member
Licensed User
Run it in debug mode to see which line raises the error.
Line 83 in DbRequestManager.bas

Line 83 is
res.Tag = m.Get("tag")
in Sub ser_BytesToObject

I tried updating the driver to the latest stable mysql-connector-java-5.1.38 unfortunately the result is same. Earlier it was 5.1.37

In my Activity, there are two QUERIES, surprisingly the first query is working and only the second query is failing.

I wonder why it is failing at the TAG
 

shashkiranr

Active Member
Licensed User
Hi All,

I am getting the below message in the log when I connect my phone to retrieve information from mysql db. I am getting the data which i want but need to know if this error is not a problem. I am running my server in release mode.

B4X:
Class not found: anywheresoftware.b4a.samples.push.main$_dbcommand, trying: b4j.example.main$_dbcommand

Best,
SK
 

billzhan

Active Member
Licensed User
I can use this in B4A?

No. It (The jRDC server) is based on full version of jetty(jServer lib) and JDBC(jSQL lib) , which are not available on android.

It is designed as middle ware between clients(Android, iOS,B4J apps) and databases. This middle ware can make your databases safer and easier to access.
 
Last edited:

shashkiranr

Active Member
Licensed User
Hi All,

I am trying to connect the jrdc to the server mysql db. The URL I am using to access is
B4X:
jdbc:mysql://http://serverip/dbname
Also I have given access in th cpanel of the sever to allow my ip address to access the db But It fails to create connection. Everytime I get this error.
B4X:
java.sql.SQLException: No suitable driver
    at java.sql.DriverManager.getDriver(DriverManager.java:315)
    at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:240)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)

Is it possible to access server db by running the jar in local computer. Has anyone tried it? Kindly let me know.

Best,
Shashi Kiran
 

Anser

Well-Known Member
Licensed User
Hi All,

I am trying to connect the jrdc to the server mysql db. The URL I am using to access is
B4X:
jdbc:mysql://http://serverip/dbname
Also I have given access in th cpanel of the sever to allow my ip address to access the db But It fails to create connection. Everytime I get this error.

Is it possible to access server db by running the jar in local computer. Has anyone tried it? Kindly let me know.

Best,
Shashi Kiran

This is how the config.properties entry looks like when I connect to a local MySQL DB
B4X:
JdbcUrl=jdbc:mysql://192.168.0.170/YourDbName?characterEncoding=utf8

If you want to connect to a remote MySQL DB then substitute the Local IP with your Public server's IP.

Is it possible to access server db by running the jar in local computer. Has anyone tried it? Kindly let me know.

The jar file can be run locally on your PC too.

Regards
Anser
 

shashkiranr

Active Member
Licensed User
I am using
B4X:
#AdditionalJar: mysql-connector-java-5.1.38-bin
. Its working with mysql db in local host, But not with a mysqldb in server.
 

Anser

Well-Known Member
Licensed User
I am using
B4X:
#AdditionalJar: mysql-connector-java-5.1.38-bin
. Its working with mysql db in local host, But not with a mysqldb in server.
Hope you are using a VPS server. If it is working fine from your local host, then please check the firewall port on your VPS Server
 

shashkiranr

Active Member
Licensed User
I still didnt get it. :( Dont know where I am going wrong. I copied the sql connector jar in www folder of my server. I set the url to shared ip address of my server still not able to connect to Mysql db on server by running jar in local computer. tried both the ports in the URL but it didnt work. any suggestion is appreciated.
 

Anser

Well-Known Member
Licensed User
I copied the sql connector jar in www folder of my server.
Did you mean jRDC.jar ? You don't have to copy any other jar or connectors to the server. You just need the jRDC.jar file. The connector jar etc are included in the jRDC.jar itself

Actually I am confused and don't know what you are trying to achieve.

You are successful to use jRDC on local PC to connect to a MySQL server on your local PC. Right ?

I assume that you are trying to run the jRDC on your local PC to connect to the MySQL server residing on a Remote Server. You fail at this point. Right ?
OR
Are you trying to run the jRDC.jar on the remote server itself ?
 

shashkiranr

Active Member
Licensed User
I assume that you are trying to run the jRDC on your local PC to connect to the MySQL server residing on a Remote Server. You fail at this point. Right ?

Yes, It fail at this point.

If i use the same jRDC and Mysql in local computer it works But jRDC in local and Mysql in server it does not. :(
 

Anser

Well-Known Member
Licensed User
If i use the same jRDC and Mysql in local computer it works But jRDC in local and Mysql in server it does not. :(
Try temporarily disabling the firewall on your PC and then test

Are you using a VPS Server ?
Is MySQL running on the default port (3306) on your Remote Server ?
Are you connecting as root ?

Regards
Anser
 

shashkiranr

Active Member
Licensed User
Yes it is running on port 3306 and it is a VPS server. I have already given access to the port in my firewall settings in my local machine. I have created a user for mysql db and I am using it.
 
Status
Not open for further replies.
Top