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

Cableguy

Expert
Licensed User
Longtime User
Insert is already correct. But a finished or true, return as soon, as the data has been written to the database.
The job done sub is the place where you will be given that info...
An insert command will not return a recorset
 

asawyer13

Member
Licensed User
Longtime User
I am pretty new to B4A and see a lot of posts on updating/inserting/deleting data from mysql which is what I want to use. I have a unique situation in that I want to be able to do this using B4A and possibly B4I eventually, but also using another product that is running javascript locally so it needs a secure way to update/insert/delete data on the server which is running Windows and mysql. Is there documentation or something that would tell my how I could communicate with jRDC2 when I'm not using a B4X product, but the other one?
 

Haris Hafeez

Active Member
Licensed User
Longtime User
I am pretty new to B4A and see a lot of posts on updating/inserting/deleting data from mysql which is what I want to use. I have a unique situation in that I want to be able to do this using B4A and possibly B4I eventually, but also using another product that is running javascript locally so it needs a secure way to update/insert/delete data on the server which is running Windows and mysql. Is there documentation or something that would tell my how I could communicate with jRDC2 when I'm not using a B4X product, but the other one?
You cannot use jRDC with non B4X code. If you have a mix of applications, with some developed with B4A/B4i and some other application (such as a PHP web application), you need to expose your business operations as RESTful services from your server and then consume those services from all of your applications.
 

EnriqueGonzalez

Well-Known Member
Licensed User
Longtime User
You cannot use jRDC with non B4X code. If you have a mix of applications, with some developed with B4A/B4i and some other application (such as a PHP web application), you need to expose your business operations as RESTful services from your server and then consume those services from all of your applications.

This is partially true. Indeed you cannot use the same JRDC code with non b4x apps. Yet it is superfluous to make it receive Json instructions and pass them to the "jrdc logic" and again send that info in Json too.

It is a matter of adding another handler to do this. And you can keep your b4x apps intact.
 

Haris Hafeez

Active Member
Licensed User
Longtime User
This is partially true. Indeed you cannot use the same JRDC code with non b4x apps. Yet it is superfluous to make it receive Json instructions and pass them to the "jrdc logic" and again send that info in Json too.

It is a matter of adding another handler to do this. And you can keep your b4x apps intact.
It is completely true. You cannot just use jrdc with other applications written in non b4x tools without working out the byte level protocol, which is pointless.
And yes, there's no point in using jrdc if you're writing a Restful server in b4j. My point was that your business operations server should be tool agnostic and expose rest operations so those operations can be consumed by Windows apps, b4x apps, php apps etc. It does not have to be B4J to implement such a server though it is a perfectly good choice.
 

Erel

B4X founder
Staff member
Licensed User
Longtime User

A Z M JANNAT UL KARIM

Member
Licensed User
Hi, I am running 3 ExecuteQuery sequentially with Tag to retrieve data from Database like follows ...
reqManager.ExecuteQuery(cmd, 0, "D")
reqManager.ExecuteQuery(cmd, 0, "G")
reqManager.ExecuteQuery(cmd, 0, "R")

Based on the tag it will retrieve data from three tables and insert data to the SQLLite DB for Offline Access. After that I populate the ListView from the SQLLite DB.
Now I want to know when the job process completed? I think I need to use CallSubDelayed but the problem is where should I use it to get the final job done response to Fill the ListBox. Please check the last line to FillList call event after Job.Release. It call three times whereas I want to call it once.

My codes are as follows ...
B4X:
Sub GetPartyInfo(PartyType As String)
    Dim cmd As DBCommand
    cmd.Initialize
    If PartyType = "D" Then
        cmd.Name = "select_dealer"
        cmd.Parameters = Array As Object(manager.GetString("mobPhone"))
        reqManager.ExecuteQuery(cmd, 0, "D")
    Else If PartyType = "G" Then
        cmd.Name = "select_golden"
        cmd.Parameters = Array As Object(manager.GetString("mobPhone"))
        reqManager.ExecuteQuery(cmd, 0, "G")
    Else If PartyType = "R" Then
        cmd.Name = "select_retailer"
        cmd.Parameters = Array As Object(manager.GetString("mobPhone"))
        reqManager.ExecuteQuery(cmd, 0, "R")
    End If
    CallSubDelayed(Me, "GetPartyInfo_Complete")
End Sub

Sub JobDone(Job As HttpJob)
    If Job.Success = False Then
        Log("Error: " & Job.ErrorMessage)
    Else
        If Job.JobName = "DBRequest" Then
            Dim result As DBResult = reqManager.HandleJob(Job)
            Starter.sqlLiteDB.BeginTransaction
            Try
                For Each records() As Object In result.Rows
    ' Do Some Database Stuff Here
                Next
                Starter.sqlLiteDB.TransactionSuccessful
            Catch
                Msgbox("Error Occured When Updating Data !!!", "Error")
            End Try
            Starter.sqlLiteDB.EndTransaction
        End If
    End If
    Job.Release
    FILL_LIST
End Sub
 

achtrade

Active Member
Licensed User
Longtime User
I have RDC running in my server successfully. Now I would like to change it for this jRDC2 but I can not find a tutorial to setup a linux server with this new jRDC2.

can someone please post the necessary steps to achieve this ?

thanks
 

DonManfred

Expert
Licensed User
Longtime User

Sapta

Member
Licensed User
Longtime User
I have error, like this. How to solve?
thank you

B4A Version: 7.30
Parsing code. Error
Error parsing program.
Error description: Undeclared variable 'rdclink' is used before it was assigned any value.
Error occurred on line: 68 (Main)
req.Initialize(Me, rdcLink)

From this code :
B4X:
Sub CreateRequest As DBRequestManager
   Dim req As DBRequestManager
   req.Initialize(Me, rdcLink)
   Return req
End Sub
 
Status
Not open for further replies.
Top