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

Discussion in 'B4J Libraries & Classes' started by Erel, Dec 29, 2015.

Thread Status:
Not open for further replies.
  1. Erel

    Erel Administrator Staff Member Licensed User



    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:
    Code:
    #AdditionalJar: mysql-connector-java-5.1.27-bin
    3. Edit config.properties file that is located in the Files tab.


    For example:

    [​IMG]

    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:
    Code:
    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:
    Code:
    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:
    Code:
    Sub GetRecord (id As Int)
       
    Dim req As DBRequestManager = CreateRequest
       
    Dim cmd As DBCommand = CreateCommand("select_animal"Array(id))
       
    Wait For (req.ExecuteQuery(cmd, 0Null)) 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:
    Code:
    sql.select_animal=SELECT name, imageid 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:
    Code:
    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:
    Code:
    sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
         
    id INTEGER PRIMARY KEY AUTO_INCREMENT,\
         name CHAR(
    30NOT 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.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.
     

    Attached Files:

    Last edited: Feb 7, 2019
  2. incendio

    incendio Well-Known Member Licensed User

    I have mix of use, some use jRDC and some still use RDC. Is this new DBRequestManager will also works for RDC?
     
    ocalle likes this.
  3. Erel

    Erel Administrator Staff Member Licensed User

    Good question. jRDC 2 is compatible with v1 and v2 clients.
    V2 clients (DBRequestManager) are only compatible with jRDC 2.
     
  4. Bladimir Carrillo

    Bladimir Carrillo Member Licensed User

    I am testing my application to change from RDC to jRDC2.
    Running first ExecuteQuery with MsSQL and MySQL works well, but with Oracle, I have an error in the DBRequestManager module, Sub ser_BytesToObject, on the following line:

    Code:
    res.Tag = m.Get("tag")
    Error:

    Error reading response: (EOFException) java.io.EOFException
    Error occurred on line: 83 (DBRequestManager)
    java.lang.NullPointerException
    at anywheresoftware.b4a.shell.Shell.setField(Shell.java:613)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:349)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:244)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:132)
    at anywheresoftware.b4a.BA$3.run(BA.java:334)
    at android.os.Handler.handleCallback(Handler.java:725)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:5099)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:803)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:570)
    at dalvik.system.NativeStart.main(Native Method)

    The error occurs several miliseconds after ending JobDone Sub with Job.Success = true.

    Regards
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Please try it in release mode. If it doesn't work then post the crash log again.
     
  6. Bladimir Carrillo

    Bladimir Carrillo Member Licensed User

    Trying in release mode on server and client, I have an error (attached image) on the device:
    Screenshot_2016-01-12-09-43-23.png
    Regards
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    Please start a new thread for this issue and also post the server logs.
     
  8. Bladimir Carrillo

    Bladimir Carrillo Member Licensed User

    Ok Erel. Where is the option to start a new thread please?
     
  9. Erel

    Erel Administrator Staff Member Licensed User

  10. Erel

    Erel Administrator Staff Member Licensed User

    jRDC v2.1 is released (first post). It fixes an issue with numeric types breaking the response.
     
  11. marcick

    marcick Well-Known Member Licensed User

    Thanks Erel.
    Is that possible to have a zip of the previous version ?
    I have much customized the code and I need to see where are the changes to implement them in my code.
    I forgot to keep a backup of the original code.
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    The older version is no longer available. However the change is simple. Only this loop was modified (under ExecuteQuery2):
    Code:
    Do While rs.NextRow And limit > 0
         
    Dim row(cols) As Object
         
    For i = 0 To cols - 1
           
    Dim ct As Int = rsmd.RunMethod("getColumnType"Array(i + 1))
           
    'check whether it is a blob field
           If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
             row(i) = rs.GetBlob2(i)
           
    Else if ct = 2 Or ct = 3 Then '<-------------------------
             row(i) = rs.GetDouble2(i)
           
    Else
             row(i) = jrs.RunMethod(
    "getObject"Array(i + 1))
           
    End If
         
    Next
         res.Rows.Add(row)
       
    Loop
    If you need to support version 1 clients then you should make a similar change in ExecuteQuery.
     
  13. marcick

    marcick Well-Known Member Licensed User

    Perfect, thanks
     
  14. PSEAD

    PSEAD Member Licensed User

    I have noticed the following message when running the jRDC (in debug or release mode) when the server receives it's first query:
    "Class not found: b4a.example.main$_dbcommand, trying: b4j.example.main$_dbcommand"

    DBresult and DBcommand are declared.

    It only appears once, and the server continues to work correctly. Do I just ignore the message?
     
  15. Erel

    Erel Administrator Staff Member Licensed User

    Yes. It happens because the full name of the custom type is different. It is expected.
     
  16. PSEAD

    PSEAD Member Licensed User

    Thanks
     
  17. Anser

    Anser Well-Known Member Licensed User

    The option to run in Debug mode is available only when you run the jRDC using B4J right ?

    OR

    Am I wrong here ? Can I compile jRDC in Debug mode and then put this version (Debug mode) of jRDC.jar on my VPS Server ?, so that whenever I modify the SQL statements in the Config.Properties file, the changes will be reflected without restarting the jRDC.jar application

    As I am using a fully managed VPS Server, every time I make some changes in the SQL statements in the Config.Properties file, I need to request the Support team to STOP and RESTART the jRDC.jar file. When I was using the previous version of the jRDC/RDC, I just had to upload the modified version of Config.Properties file via FTP to the folder where jRDC.jar/RDC.jar resides.

    Please note:- I don't include the Config.Properties file inside the assets folder, I keep it as an external file. The reason for that is that the changes in the Config.Properties file will be very frequent until I finish the testing and the project is matured and finished.

    I believe that the previous version of jRDC having the Debug option that can be adjusted as per the will of the user was very flexible and straight forward.

    Hope I said what I am trying to convey.

    Regards
    Anser
     
  18. Erel

    Erel Administrator Staff Member Licensed User

    See RDCConnector.Initialize code. You just need to set DebugQueries to True.
     
  19. Anser

    Anser Well-Known Member Licensed User

    I am getting the following error when I changed from jRDC 1 to jRDC ver 2.1
    The same code was working fine in jRDC 1

    It may be something that I have missed in my code.

    An error has occured in
    sub:dbrequestmanager_ser_bytestoobject (java line: 290)
    java.lang.NullPointerException

    The Full error log is pasted below


    Error reading response: (EOFException) java.io.EOFException
    dbrequestmanager_ser_bytestoobject (java line: 290)
    java.lang.NullPointerException
    at com.myappname.dbrequestmanager._ser_bytestoobject(dbrequestmanager.java:290)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:169)
    at anywheresoftware.b4a.BA$2.run(BA.java:328)
    at android.os.Handler.handleCallback(Handler.java:733)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:136)
    at android.app.ActivityThread.main(ActivityThread.java:5001)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
    at dalvik.system.NativeStart.main(Native Method)
    java.lang.NullPointerException


    I have included the new DbRequestManager in the project
    RandomAccessFile ver 2.20 is also included in my Project

    Edit:

    I believe that the following line on the Sub ser_BytesToObject inside DbRequestManager.bas is causing the error.

    res.Tag = m.Get("tag")

    Regards

    Anser
     
    Last edited: Feb 16, 2016
  20. Erel

    Erel Administrator Staff Member Licensed User

    Run it in debug mode to see which line raises the error.
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice