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

Erel · Aug 4, 2013

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:

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:

B4X:

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:

B4X:

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:

B4X:

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:

B4X:

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:

B4X:

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

Unpack the server zip file.
You will need to download the driver for your database platform and copy the jar file to jdbc_driver folder.
Edit config.properties as discussed above.
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 ':'.
Run the server
You should see something like:

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

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

http://www.mchange.com/projects/c3p0/index.html

Pravin Shah · Oct 14, 2014

Yes, the 'test' database is in localhost. I am testing this connection on my local machine. I tried both urls
127.0.0.1:17178/?method=test and localhost:17178/?method=test
but no success, same error message.

I have attached the error log from JDBC server, it may be of some help to resolve the issue.

Appreciate your help, thanks.

Pravin Shah · Oct 14, 2014

Just to update, I am able to resolve the issue. There were issues with user privileges, I have created new user and assign all the privileges. After that I have tested connection with new user credentials and the connection was successful.

I will try with MS SQL server connection now.

timbald · Oct 28, 2014

Extremely helpful, thank you. Got this working on Raspberry Pi.

warwound said:
Here's a short tutorial for anyone that wants to run the RDC server on a Ubuntu server and:

Auto start the RDC server when the (Ubuntu) server boots.

Be able to start / restart / stop the RDC server, without the need to reboot the Ubuntu server.

...
...

Brookalino · Nov 26, 2014

Hi Erel,

I am not able to connect to my localhost database using RDC server.

I have tried everything from the previous posts and its beginning to drive me a little crazy!

I have extracted the contents of RDC-Client.zip and RDC-Server.zip to the appropriate directory

I copied the driver to the correct jdbc_driver folder. (sqljdbc41.jar)

I have changed the config.properties file : (creating a ‘atest’ database in my local SQL)

I have made sure the ALLIP in the SQL Server Configuration Manager are set to 1433.

4. After that I run the RunRLC.bat file and each time I get that the ‘Address is already in use’ error.
Edit: I have checked that nothing is running in the background

I have changed the 127.0.0.1 to localhost with the same outcome and have tried to change the port number but also get the above?

5. After that I tried running the following url 127.0.0.1:1433/?method=test to test database connection

But it gave the following error.

Note : I am using SQL Server 2012

This is driving me crazy so if you have any ideas I would appreciate the help.

keirS · Nov 27, 2014

You are telling RDC to listen on the same port as your SQL server is listening on! Your connection string ip should be 127.0.0.1:1443
Default server port for jetty is 8080.

Erel · Nov 27, 2014

As keirS wrote you have set the ServerPort value incorrectly. This is the port that RDC uses not the database.

Brookalino · Nov 28, 2014

Erel said:
As keirS wrote you have set the ServerPort value incorrectly. This is the port that RDC uses not the database.

Ah ha... ok thank you.

GuerillaProgrammer · Dec 9, 2014

I'm a little confused. Why can B4A not directly use the jdbc to connect to a remote MS SQL db installation.

I don't want to use configuration files for a web server that passes my requests via http rather than making a direct connection.

This is EXACTLY the kind of unnecessary unix/java mentality hyper-complexity that I'm looking to B4A as an escape from. What is the basis for the requirement for this kind of kludgish workaround?

Do you have any intention of implementing the jdbc directly any time soon?

Please tell me that I've misunderstood what this article says, or that B4A has implemented direct usage of the jdbc to SQL connection since August.

Erel said: ↑
Android cannot directly connect to the database server.

Having included the "net.sourceforge.jtds.jdbc.Driver" The following code connects Android to an MS SQL DB directly.
'===================================================================
String connString = "jdbc:jtds:sqlserver://ecp-clientmgr.cycyg6emdsi3.us-east-1.rds.amazonaws.com;" +
"Initial Catalog=DB_NAME;" +
"useSSL=true;" +
"encrypt=true;" +
"TrustServerCertificate=true;" +
"Integrated Security=False;" +
"user=DB_USER;" +
"password=YOUR_PASSWORD_HERE;";

conn = DriverManager.getConnection(connString);

Return conn;
'===================================================================

There is some work that one must do with an SSL certificate which I will be glad to research and share if it will help, but I have already used this code to connect an android emulator to the Amazon hosted DB and it works perfectly.

My question is: Is the limitation in the B4A application, or has it simply been too hard to make the above approach work? If the former, then I can't help and cannot use B4A(makes me very sad), but if the latter please let me know and I'll be glad to share the methodology.

Humbly, GP

Erel · Dec 9, 2014

Anything that you can do in Java can be done with B4A. If the jdbc driver is supported by Android then you can use it in B4A (with a wrapper).

In most cases it is not recommended to connect directly to the database from the mobile app for various reasons including security.

Use the forum search to find relevant libraries: https://www.b4x.com/android/forum/pages/results/?query=mssql&page=1&prefix=1

GuerillaProgrammer · Dec 9, 2014

Erel said:
Anything that you can do in Java can be done with B4A. If the jdbc driver is supported by Android then you can use it in B4A (with a wrapper).

What kind of wrapper?

Erel said:
In most cases it is not recommended to connect directly to the database from the mobile app for various reasons including security.

I understand the risks. We've already architected the security, and I'd just like to be able to make the connection so that I can get back to developing.

Erel said:
Use the forum search to find relevant libraries: https://www.b4x.com/android/forum/pages/results/?query=mssql&page=1&prefix=1

I'm a little confused about how to include libraries in B4A.
1). Is there an include or imports statement?
2). The following code (Dim conn As Connection) fails because B4A doesn't see the java.sql package which contains the definition of Connection. How do I include java.sql in my B4A app?
3). Can you briefly explain how to create the xml files for the libraries to be included and the purpose for them.(On Edit: I watched your video "Basic4android Tutorial - Creating a library" and I think I get this, although I haven't yet tried it.)

I want to thank you for such prompt replies. Most support I've experienced has been on the order of days rather than hours.

Erel · Dec 9, 2014

You should post these questions in a new thread. However you will not be able to use these libraries with the trial version.

NMiguel · Dec 19, 2014

Hello.
Sorry the newby question:

How can I copy the results in the sub JobDone to an array so i can access it in other subs.
I need to do several different select queries and handle the results in different subs.

I hope it's not a duplicate question. I searched this thread and didn't find anything similar.

Thanks in advance.

Erel · Dec 21, 2014

Create a process global variable:

B4X:

Private gr As DBResult

Then in JobDone:

B4X:

gr = result

NMiguel · Dec 22, 2014

Thanks for the fast reply Erel.

I have tried and doesn't work.

Here is the Code:

B4X:

Sub Process_Globals
    Dim reqManager As DBRequestManager
    Private gr As DBResult
End Sub

Sub Globals
    Dim Ver As Button

End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        reqManager.Initialize(Me, "http://192.168.0.199:17178")
    End If

    Activity.LoadLayout("Main")
End Sub

Sub Ver_Click
    GetDescricao("5602482013384")

        For Each Records() As Object In gr.Rows
            Dim CodP As String = Records(gr.Columns.Get("Cod"))
              Log(CodP)
              Dim Descr As String = Records(gr.Columns.Get("Desc"))
              Log(Descr)
        Next
End Sub

Sub GetDescricao(Name As String)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "select_produto"
    cmd.Parameters = Array As Object(Name)
    reqManager.ExecuteQuery(cmd, 0, Null)
End Sub

Sub JobDone(Job As HttpJob)
    Dim Result As DBResult
    If Job.Success = False Then
        Log("Error: " & Job.ErrorMessage)
    Else
        If Job.JobName = "DBRequest" Then
            Result = reqManager.HandleJob(Job)
            'reqManager.PrintTable(Result)
            gr = Result
        End If
    End If
    Job.Release
End Sub

Ir guives a java error:

B4X:

LogCat connected to: 37303AF8D17600EC
--------- beginning of /dev/log/system
--------- beginning of /dev/log/main
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Error occurred on line: 36 (main)
java.lang.NullPointerException
    at b4a.example.main._ver_click(main.java:427)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:163)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:159)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77)
    at android.view.View.performClick(View.java:4247)
    at android.view.View$PerformClick.run(View.java:17728)
    at android.os.Handler.handleCallback(Handler.java:730)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:5289)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:525)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:739)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:555)
    at dalvik.system.NativeStart.main(Native Method)
** Service (httputils2service) Create **
** Service (httputils2service) Start **
** Activity (main) Pause, UserClosed = true **

Erel · Dec 22, 2014

Your code is wrong. The response (or DBResult) is only available after JobDone was fired.

NMiguel · Dec 23, 2014

Sorry Erel. I got stuck.

Do you have a sugestion of using it with only a button?

Erel · Dec 24, 2014

You need to understand how http requests work. The request is managed by a background thread. When the response is ready the JobDone event is fired.

You cannot block the main thread while waiting for this event.

MrKim · Jan 22, 2015

Out of curiosity, what is the purpose of Job.JobName and the code:

B4X:

If Job.JobName = "DBRequest" Then

IF the JobName is ALWAYS DBRequest? This seems spurious to me since Job.Success determines if we were successful.

Thanks

DonManfred · Jan 22, 2015

The jobdone sub is part of httputils. You may use it in your activity too. The same sub will be called and you have to decide whether the data coming from a normal httputils call or from dbrequestmanager

MrKim · Jan 22, 2015

DonManfred said:
The jobdone sub is part of httputils. You may use it in your activity too. The same sub will be called and you have to decide whether the data coming from a normal httputils call or from dbrequestmanager

Got it, thanks. have not used httputils. I am assuming JobName is the Name parameter when you initialize an HttpJob?

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

Member

Attachments

Member

Member

Member

Well-Known Member

B4X founder

Member

Member

B4X founder

Member

B4X founder

Member

B4X founder

Member

B4X founder

Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency