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

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

Spoiler: Old and shouldn't be open

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:

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:

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:

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:

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:

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:
   
   
   
   
   
   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:
   
   
   
   
   
   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

 

[dibesw]

HI,
I should write a JPEG file into my RDC access database:

This is the creation of the image (that works):

Sub FIRMA
    Bitmap1.Initialize3(Canvas.Bitmap)
    Out = File.OpenOutput(File.DirRootExternal, "image.jpeg", False)
    Bitmap1.WriteToStream(Out, 100, "JPEG")
End Sub

This is code that I thing but does not work:

Sub repl_image
    Dim cmd As DBCommand
    cmd.Name = "repl_pazient"
    Dim buffer() As Byte
    Out.WriteBytes(buffer, 0, buffer.length)
    cmd.Parameters=Array As Object(Out, 44)
    Out.Close
    reqManager.ExecuteCommand(cmd, "esci")
End Sub

I should write JPEG image into a OLE OBJECT field (sign) and config.properties is:

sql.repl_pazient=update paziente set sign = ? where code_pazient = ?

When I call repl_image sub, I have and error in the server side:

I don't know how do to write a OLE into field.
THANKS!

 

[dibesw]

OK, I SOLVED IT!

The correct code is:

Sub repl_immagine
    Dim In As InputStream
    Dim cmd As DBCommand
    cmd.Name = "repl_pazient"
    In = File.OpenInput(File.DirRootExternal, "sign.jpeg")
    Dim Out As OutputStream
    Out.InitializeToBytesArray(1000)
    File.Copy2(In, Out) '<---- This does the actual copying
    Dim data() As Byte
    data = Out.ToBytesArray
    cmd.Parameters=Array As Object(data, 44)
    Out.Close
    reqManager.ExecuteCommand(cmd, "esci")
End Sub

 

[dibesw]

HI,
my problem is:

Sub Activity_Create(FirstTime As Boolean)
    CreatePreferenceScreen
    reqManager.Initialize(Me, "http://" & manager.GetString("server") & ":17178")
......
......
End Sub
Sub Button2a_Click
    GetAnamnesi
End Sub
Sub GetAnamnesi
    Dim cmd As DBCommand
    ProgressDialogShow("Attendere...")
    cmd.Initialize
    cmd.Name = "select_anamnesi"
    reqManager.ExecuteQuery(cmd, 0, "selana")
End Sub

First time (and only fist time) that I run the program, the server message is:

after all works well.
Why?
Since the message is ConnectionTimeout perhaps maybe I should increase Timeout?
Thanks.

 

[Erel]

It seems like some network issue. You can try to increase CheckoutTimeout value in c3p0.properties file.

 

[dibesw]

first time that I run the program, time query is too long

3p0.maxStatements=150
c3p0.maxIdleTime=1800
c3p0.idleConnectionTestPeriod=600
c3p0.checkoutTimeout=40000

I set c3p0.checkoutTimeout=40000 and now works well
Thanks Erel!

 

[dibesw]

Again for timeout problem:

Every time I run program, system write a directory into my database directory:

every directory is about 244MB.
after I have used it many times, these directory are many
Perhaps every time system write these directory takes time and so this is the cause of the timeout?

My table are composed of a few records.
there is a way to not write these directories?

Thanks

 

[Erel]

It isn't RDC that creates these folders. Which database are you connecting to? Which JDBC library are you using?

 

[DonManfred]

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.

DriverClass=com.mysql.jdbc.Driver
JdbcUrl=mysql://127.0.0.1:3306/wbf2
User=xxxx
Password=xxxx
ServerPort=17178
#If Debug is true then this file will be reloaded on every query.
#This is useful if you need to modify the queries.
Debug=true

results in

RemoteServer is running (Mon Feb 03 18:00:59 CET 2014)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

when i try to open the url http://127.0.0.1:17178/?method=test in a browser

Can someone help to get the right direction to the solution to get the test running please?

 

[derez]

Try to put user=root and no password
Also this line in the example is

JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8

, maybe your :3306 is a problem (I'm guessing...).

 

[DonManfred]

On MySQL-Admin the user Root HAS an password.
3306 is the default port of MySQL.

But i tried your suggestion. root with no pw and jdbcurl like in your post. Always the same error. In the commandwindow i read something about "No suiteable driver"...
In folder jdbc_driver is one file: mysql-connector-java-5.1.29-bin.jar

Is that not the right file for MySQL?

 

[dibesw]

Thanks Erel,
My database is an access database.
RDC server I have download from here
http://www.b4x.com/android/files/RDC-Server.zip
and driver are in http://sourceforge.net/projects/ucanaccess/files/
Then I have put

commons-lang-2.6.jar
commons-logging-1.0.4.jar
hsqldb.jar
jackcess-2.0.1.jar
ucanaccess-2.0.1.jar

in the jdbc_driver folder

 

[Erel]

@dibesw the issues you see with the files and slowdown are caused by the jdbc driver. Can't you switch to a more powerful database (SQLite for example)?

@DonManfred I've just tested it with the latest driver from oracle and it works properly. I've downloaded the server zip file and didn't modify anything. Just copied mysql-connector-java-5.1.29-bin.jar to the jdbc_driver folder.

 

[dibesw]

This is bad news.
I'm working with access database for many years and my customers have all access database.
I have no other options?

 

[Erel]

Try to look for a different jdbc driver. Another option is to use Jackcess library with B4J and write your own server instead of RDC.

 

[DonManfred]

@dibesw the issues you see with the files and slowdown are caused by the jdbc driver. Can't you switch to a more powerful database (SQLite for example)?

@DonManfred I've just tested it with the latest driver from oracle and it works properly. I've downloaded the server zip file and didn't modify anything. Just copied mysql-connector-java-5.1.29-bin.jar to the jdbc_driver folder.

Ok, i´ll do a new test with a fresh installation of server-files... Will tell the results later

 

[hardo]

Hello,
I'm playing with the RDC and access to MySQL. It works fine.
My question:
Is there a way to work with a SELECT statement, which is total built in my front-end application?
The file config.properties could only contains a line like sql.select_xxx=*
Thanks.

 

[Erel]

No. RDC requires you to define the queries in the server. This is safer as it protects you from SQL injection attacks.

You can create a similar solution with B4J and then allow the clients to send the queries.

 

[hardo]

I tried the following command in the config.properties of the RDC-server:

SELECT ?,? FROM, <mytable>​

As parameters I wrote

ARRAY as Object ("Name", "City")​

Name and City are field names from <mytable>. The program processes the command
without an error message, but the output lines all show only the text "Name City."
It will not output the contents of the fields.

It would increase the flexibility of the application if the above SELECT statement would work "normal".
By the way: SELECT and injection attack?

Regards Hardo.

 

[Erel]

This is not related to RDC. This is how parameterized queries (or prepared queries) work. The columns names and the table name cannot be parameterized.

By the way: SELECT and injection attack?

http://stackoverflow.com/questions/1099391/can-i-get-sql-injection-attack-from-select-statement

 

[jalle007]

My database is not stored locally but on the server.
Host: Siteground, DB type MySQL

What exactly I need to upload to server in order to get this thing working.