Android Tutorial Remote Database Connector (RDC) - Connect to any remote DB

Discussion in 'Tutorials & Examples' started by Erel, Aug 4, 2013.

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

    Erel Administrator Staff Member Licensed User

    jRDC version 2 is available here: https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/

    This tutorial covers a new framework named Remote Database Connector (RDC). The purpose of RDC is to make it simple to develop Android applications that interact with remote database servers.

    There are two components in this framework: a lightweight Java web server (the middleware) and a B4A class named DBRequestManager that interacts with this web server.

    The Java web server can connect to any database platform that provides a JDBC driver.

    This includes: MySQL, SQL Server, Oracle, Sybase, DB2, postgreSQL, Firebird and many others.

    You can also use the web server without a database server by connecting to a local SQLite database file.

    The Java web-server is a simple server that connects to the database server and to the Android clients.
    As this is a Java app you can run it on Linux or Windows computers. It requires Java JRE 6 or 7.

    This solution is much more powerful than the PHP (MySQL) and ASP.Net (MS SQL) solutions that are already available.

    Main advantages over previous solutions:
    • Support for many types of database platforms.
    • Significantly better performance.
    • SQL statements are configured in the server (safer).
    • Support for all types of statements, including batch statements.
    • Support for BLOBs.

    Server Configuration

    JDBC is a Java API that provides a standard method to access any database. A database driver (jar file) is required for each type of database. You will need to download the driver for your database and put it in the jdbc_driver folder.

    The Java server configuration is saved in a file named config.properties.
    This file includes two sections: general configuration and a list of SQL commands.

    For example:

    [​IMG]

    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 - If Debug is set to true then the SQL commands list will be loaded on every request. This is useful during development as it allows you to modify the commands without restarting the server process.

    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.

    Client Code
    The client sends requests to the server. There are two types of requests: query requests (usually SELECT statements) and batch requests (any other statement).

    Note that both the client and the server can manage multiple requests in parallel.
    Usually you will only need a single DBRequestManager object.

    Each request is made of a command (or a list of commands) and a tag. The tag can be any object you like. You can use it later when the result is ready. The tag is not sent to the server.

    A command is an object of type DBCommand:
    Code:
    Type DBCommand (Name As String, Parameters() As Object)
    Name - The case sensitive command name as configured in the server configuration (without sql.).
    Parameters - An array of objects that will be sent to the server and will replace the question marks in the command.

    For example to send a SELECT request:
    Code:
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = 
    "select_animal"
    cmd.Parameters = 
    Array As Object("cat 1")
    reqManager.ExecuteQuery(cmd, 
    0"cat 1")
    ExecuteQuery expects three parameters: the command, maximum number of rows to return or 0 if there is no limit and the tag value.

    Under the hood DBRequestManager creates a HttpJob for each request. The job name is always "DBRequest".

    You should handle the JobDone event in your activity or service:
    Code:
    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)
           
    If result.Tag = "cat 1" Then 'query tag
             For Each records() As Object In result.Rows
               
    Dim name As String = records(0'or records(result.Columns.Get("name"))
               Log(name)
             
    Next
           
    End If
         
    End If
       
    End If
       Job.Release
    End Sub
    As you can see in the above code, DBRequestManager.HandleJob method takes the Job and returns a DBResult object:
    Code:
    Type DBResult (Tag As Object, Columns As Map, Rows As List)
    Tag - The request tag.
    Columns - A Map with the columns names as keys and the columns ordinals as values.
    Rows - A list that holds an array of objects for each row in the result set.

    Non-select commands are sent with ExecuteCommand (single command) or ExecuteBatch (any number of commands). It is significantly faster to send one request with multiple commands over multiple requests. Batch statements are executed in a single transaction. If one of the commands fail all the batch commands will be cancelled (roll-backed).

    Note that for non-select requests, Rows field in the results will hold an array with a single int for each command. The int value is the number of affected rows (for each command separately).

    Initializing DBRequestManager:
    Code:
    Sub Activity_Create(FirstTime As Boolean)
       
    If FirstTime Then
         reqManager.Initialize(Me, 
    "http://192.168.0.100:17178")
       
    End If
    End Sub
    The first parameter is the module that will handle the JobDone event.
    The second parameter is the link to the Java server (with the port number).

    DBRequestManager provides the following helper methods for common tasks related to BLOB fields: FileToBytes, ImageToBytes and BytesToImage.It also includes a method named PrintTable that prints DBTable objects to the logs.

    Framework Setup
    1. Unpack the server zip file.
    2. You will need to download the driver for your database platform and copy the jar file to jdbc_driver folder.
    3. Edit config.properties as discussed above.
    4. Edit RunRLC.bat and set the path to java.exe. If you are running it on Linux then you need to create a similar script with the path to java. On Linux you should change the ';' separator to ':'.
    5. Run the server :)
      You should see something like:

      [​IMG]

      Note that the path to config.properties is printed in the second line.
    6. Add an exception for the server port in your firewall.
    7. Try to access the server from the browser:

      [​IMG]

      You will probably not see the above message on the first run :(. Instead you will need to check the server output and read the error message.
      If you are able to call the server from the local computer and not from other devices then it is a firewall issue.

    Tips

    • MySQL driver is available here: http://dev.mysql.com/downloads/connector/j/
    • Google for <your database> JDBC Driver to find the required driver. For SQL Server you can use this open source project: http://jtds.sourceforge.net/
    • The server uses an open source project named c3p0 to manage the database connection pool. It can be configured if needed by modifying the c3p0.properties file.
    • Use a text editor such as Notepad++ to edit the properties file.
    • If you are running the server on Linux then you can run it with nohup to prevent the OS from killing the process when you log out.
    • Java doesn't need to be installed. You can just unpack the files.
    The server is based on the two following open source projects:
    Jetty - http://www.eclipse.org/jetty/
    c3p0 - http://www.mchange.com/projects/c3p0/index.html
     
    Last edited: Dec 29, 2015
    sundas, MarcoRome, GudEvil and 12 others like this.
  2. Erel

    Erel Administrator Staff Member Licensed User

    Last edited: Aug 4, 2013
    green2000 likes this.
  3. yckhor

    yckhor Member Licensed User

    :) Something that I have been waiting for... being working with MS SQL using the ASP.net approach. This will be much more straightforward. Thanks! Erel.
     
  4. leonardoffsilva

    leonardoffsilva Member Licensed User

    That´s great. I will need this to work with postgresql.
    Testing now...
    Thanks a lot.
     
    Last edited: Aug 5, 2013
  5. vampirbcn

    vampirbcn Active Member Licensed User

    Thanks Erel, very interesting and very useful.
     
  6. mc73

    mc73 Well-Known Member Licensed User

    Great, will get to try it the soonest!
    I guess simultaneous connections are handled by the jdbc, correct?
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    Simultaneous connections are handled by HttpUtils2, Jetty server and c3p0 (the connection pool management library).
     
    jimmyF and mc73 like this.
  8. leonardoffsilva

    leonardoffsilva Member Licensed User

    First test with Postgresql and working ok. On Windows 7 64.

    Using Java jdk1.7.0_21

    JDBC Driver : postgresql-9.2-1003.jdbc4

    config.proprieties :

    Code:
    DriverClass=org.postgresql.Driver

    JdbcUrl=jdbc:postgresql://localhost:port/database

    User=username
    Password=password
    ServerPort=
    17178
    Response :

    RemoteServer is running (Mon Aug 05 23:03:07 BRT 2013)
    Connection successful.
     
    patrick14384 and Erel like this.
  9. leonardoffsilva

    leonardoffsilva Member Licensed User

    But I have one question !

    When we need to make an remote database insert on a structure that´s have two tables : master and datail. Like an invoice.
    And when I need to make a master insert and return a serial number "Id" on remote server. And than make an sequence of detais insert´s based on this "id" captured on remote master insert.
    Why can I do this if one operation is one job ?
    And detail inserts are serveral operations !
    And I can consider to have one invoice or several invoices to sync.

    Thanks !
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    Let me see that I understand. Do you want to send a batch request with both INSERT and SELECT statements?

    Can you provide more information about the request you want to send?
     
  11. derez

    derez Expert Licensed User

    In the original and also in postgresql I get the same problem in the dos window (with many other error msgs):
    The browser shows:
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    It is most probably a configuration issue. Where is the database server running?
     
  13. derez

    derez Expert Licensed User

    In my home pc, in D:\b4a\rdc-server.
    I added port 17178 to the router's port forwarding.
    The properties file is like this, or like in your files:
    java.exe path is : C:\Program Files (x86)\Java\jdk1.7.0_13\bin\java

    edit: I don't understand what is so funny in the qoute...
     
  14. Erel

    Erel Administrator Staff Member Licensed User

    This is the Java server. Where is the postegreSQL server is running?
     
  15. derez

    derez Expert Licensed User

    I don't know. I probably don't understand something in the tutorial...
    I changed back to the files as in the tutorial.
    The driver I use is mysql-connector-java-5.1.26-bin.jar
    The server actions are attached.
    The report on the browser is:
    edit:
    I don't understand - from the config file I see that the database is created by the server.
    If I set there the user and password, will it appear at the beginning of comunication with the server ? how are they provided ? Can I use an existing mysql db instead of buliding it by the server ?
     

    Attached Files:

    Last edited: Aug 6, 2013
  16. leonardoffsilva

    leonardoffsilva Member Licensed User

    Erel, I have an Invoice database on some Androids devices. The invoices has two principal tables : Master and Detail.

    On remote database server I have the Master and Datail database too, but the remote database server has an Unique auto increment ID on master table.

    I need to sync them.

    I think its simple to do this, because postgresql has a command that return this auto increment ID. I will try it tonight. INSERT with RETURNING. But I will execute this like an SELECT.

    But the command on android is a JOB and i have to do many insert´s on remote server based on Andoird details table for each Master table. I have to create a repeat rotine that create this detais insert, and check if each insert if not ocurr any arror.

    Today I do this trasfering Sqlite files over ftp protocol and running an bat rotine on server based on DATASNAP REST SERVER.

    But the FTP protocol has many promblems on mobile networks.

    Thank´s
     
  17. Erel

    Erel Administrator Staff Member Licensed User

    @derez the Java server is just a middleware between the database server and the Android devices. You should first run a database server.

    @leonardoffsilva I understand. Can you start a new thread (in the questions forum) and post examples of the SQL statements you need to send/
     
    luke2012 likes this.
  18. coldtech

    coldtech Member Licensed User

    Trying to test it out and compile the client (server was very easy to get going) but DBRequestManager is failing to compile in Sub HandleJob (line 190) it does not recognize TAG as a property of HttpJob where it is attemptingto assign the Job.Tag to the Table.Tag.

    What is returned by the server, JSON, XML, something else?

    What is the procedure to add additional commands to the server after they are defined in the config file? Will it read the file on every request or do you need to restart it?
     
  19. Erel

    Erel Administrator Staff Member Licensed User

    You should use HttpUtils2 v2.01+. It was included in B4A v2.71. If you are using a previous version of B4A then you can download the source here: http://www.basic4ppc.com/android/fo...vices-are-now-even-simpler.18992/#post-109068
    Use the two modules instead of the library.

    Binary format. You can see how it is implemented in the DBRequestManager class.

    See the explanation of the Debug property in the tutorial.
     
  20. derez

    derez Expert Licensed User

    OK, got this fact.
    Now, if I have a mysql database (created by b4a), how can I put it in my pc and have the RDC work with it ?
    I guess it has to be defined in
    DriverClass= ....
    JdbcUrl= ...
     
Thread Status:
Not open for further replies.
Loading...