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

Status
Not open for further replies.
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:

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

    SS-2013-08-04_17.05.16.png


    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:

    SS-2013-08-04_17.06.17.png


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

coldtech

Member
Licensed User
Longtime User
OK got it working, you may want to point out that the client example only shows the results in the log.

It took me a little while staring at the emulators black screen to realize :)

Thanks Erel
 

Sytek

Active Member
Licensed User
Longtime User
OS: Windows 8. (64-Bits)
Tested on MySQL connection on Wampserver...Works just fine....Ok!

The only problem that I face is that if I Put the Server Online and execute the RDC Batch it throws the following error
2013-08-07 21:45:18.738:WARN::FAILED org.eclipse.jetty.server.Server@5f7e8f: jav
a.net.BindException: Address already in use: bind
Exception in thread "main" java.net.BindException: Address already in use: bind

..And I need to put the Server Online 'cause there's my data information system (inventory)
If I don't put the Server online I cannot Access the php system(inventory).

Please Help; How can I solve this one.
Thanks in advance.
Best Regards!
 

Sytek

Active Member
Licensed User
Longtime User
This error means that there is already another app that listens to the port. You can change RDC port in the config file.

Thank's Erel; I will change it arriving home.

By the way THIS IS THE BEST CONNECTOR 'TIL TODAY, Nice one young man!...nice one !!! Appreciate. :)

Best Regards!

EDIT:
This error means that there is already another app that listens to the port. You can change RDC port in the config file.
This solution solves my problem; As I said Before...
THIS IS THE BEST CONNECTOR 'TIL TODAY!
;) Cheers Erel...Thank's

....and Remove the 'TIL TODAY.....THIS IS THE BEST CONNECTOR.
 
Last edited:

Sytek

Active Member
Licensed User
Longtime User
Hello Erel!
How Do I send the Parameters for the Insert command ?
config command
sql.insert_serial=insert into serials (serial,status,name,endaccess) values (?,?,?,?)


Thank's for your Help
Best Regards!
 

Sytek

Active Member
Licensed User
Longtime User
B4X:
Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "insert_serial"
cmd.Parameters = Array As Object(2, 3, "name 22", 1)
reqManager.ExecuteCommand(cmd, "tag")
Thank's That was quick ..Appreciate!
 

yckhor

Member
Licensed User
Longtime User
B4X:
Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "insert_serial"
cmd.Parameters = Array As Object(2, 3, "name 22", 1)
reqManager.ExecuteCommand(cmd, "tag")
Hi Erel, any example of inserting a BLOB object as one of the parameters, taking your example? A BLOB like a photograph taken using the android device camera?
Thanks in anticipation!
 

Sytek

Active Member
Licensed User
Longtime User
Hello Erel!
When I Insert a datetime field value
PHP:
cmd.Initialize
cmd.Name = "insert_serial"
cmd.Parameters = ArrayAsObject(md5imei,ima.Input,"adddate(current_timestamp,30)","current_timestamp","current_timestamp")
reqManager.ExecuteCommand(cmd, "tag")
...The RDC throws me this error.
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '
current_timestamp' for column 'endaccess' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4094)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)


I can insert in the phpmyadmin sql command without problem.
PHP:
insert into serials(serial,name,endaccess,lastaccess,firstaccess) values ('111111','serial name',adddate(current_timestamp,30),current_timestamp,current_timestamp)

I'm I Missing Something?

Thank's in advance for your help
Best Regards!

SOLUTION: see this thread
http://www.basic4ppc.com/android/forum/threads/rdc-connector-insert-a-datetime-field-value.31797/
 
Last edited:

derez

Expert
Licensed User
Longtime User
Working with SQLITE on PC and devices.
I get a complete table (=all the records) from the pc almost immediately, since the response for "SELECT * ..." comes in one answer. When I send the records from the device to the pc I use "INSERT" to fill in the records, but this is done one by one and takes about 14 seconds (because the program waits for each job to be done, otherwise they somtimes get lost...).
Is there a way to send all the records in one reqmanager command ?
 

georgelbs

Member
Licensed User
Longtime User
"/usr/lib/jvm/java-1.7.0-openjdk-amd64/bin/java" -Xmx256m -cp .:libs\*:jdbc_driver\* anywheresoftware.b4a.remotedatabase.RemoteServer
pause


Run in a terminal, but closes quickly without a error
 

kanaida

Active Member
Licensed User
Longtime User
I'm trying to use the following driver, found one for oldschool foxpro. I'm not exactly sure how to modify the properties etc... never really used JDBC.
http://www.caigen.com/dbf/doc.html#url
http://www.caigen.com/dbf/

I tried this and changed my java path in the Batch file:
B4X:
#Foxpro Server
DriverClass=com.caigen.sql.dbf.DBFDriver
JdbcUrl=jdbc:dbf://\\\\scoserver\\livedata\\1103
#If Debug is true then this file will be reloaded on every query.
#This is useful if you need to modify the queries.
versionNumber=02

Got this:
B4X:
C:\Users\user\Google Drive\android_projects\RDC-Server>"C:\Program Files (x86)\Java\jre7\bin\java" -Xmx256m -cp .;libs\*
;jdbc_driver\* anywheresoftware.b4a.remotedatabase.RemoteServer
B4A Remote Database Connecter (version 0.9)
loading: C:\Users\user\Google Drive\android_projects\RDC-Server\config.properties
Exception in thread "main" java.lang.NumberFormatException: null
        at java.lang.Integer.parseInt(Unknown Source)
        at java.lang.Integer.parseInt(Unknown Source)
        at anywheresoftware.b4a.remotedatabase.RemoteServer.main(RemoteServer.java:33)

C:\Users\user\Google Drive\android_projects\RDC-Server>pause
Press any key to continue . . .

It's an interesting find indeed, perhaps this means I can finally overcome my samba+foxpro performance issues in ubuntu 8.04 + ASP.net back end if I switch other stuff to use this new provider possibly. This b4a app i'm making will be a good trial.
 

slydog43

Member
Licensed User
Longtime User
Love this new interface to a remote database, but I have some trouble. When I try and update many tables quickly the sqllite db gets corrupted. Here is a short example of 1 table getting updated. It works if it only updates 4 times, but if I increase the loop to 5 or more it fails. Any ideas?



Sub JobDone(Job As HttpJob)

Dim lNumRecs As Long
Dim iCounter As Int
Dim dIncValue As Double
Dim dCurrValue As Double
Dim iTempID As Int
Dim iTempSEQ As Int
Dim sTempDesc As String
Dim sTempAbbrev As String

Dim iTemp2 As Int
Dim iTemp3 As Int
Dim sTemp1 As String
Dim sTemp2 As String

Dim cmd As DBCommand


cmd.Initialize

If Job.Success = False Then
Log("Error: " & Job.ErrorMessage)
Else
If Job.JobName = "DBRequest" Then
Dim result As DBResult = reqManager.HandleJob(Job)
Select Case result.Tag
Case "SelectBody"
Label3.Text = "Downloading Body"
ProgressBar1.Progress = 0
Dim result As DBResult = reqManager.HandleJob(Job)
lNumRecs = result.Rows.size
Label5.Text = lNumRecs
iCounter = 0
Label4.Text = iCounter
dIncValue = 100/lNumRecs
dCurrValue = 0
ProgressBar1.Progress = dCurrValue
DoEvents
For Each records() As Object In result.Rows
iCounter = iCounter + 1
Label4.Text = iCounter
dCurrValue = dCurrValue + dIncValue
ProgressBar1.Progress = dCurrValue
iTempID = records(result.Columns.Get("BodyID"))
iTempSEQ = records(result.Columns.Get("BodySEQ"))
sTempDesc = records(result.Columns.Get("BodyDesc"))
sTempAbbrev = records(result.Columns.Get("BodyAbbrev"))
mySQL1.BeginTransaction
Try
mySQL1.ExecNonQuery("INSERT INTO Body (BodyID, BodySEQ, BodyDesc, BodyAbbrev) VALUES (" & iTempID & ", " & iTempSEQ & ", '" & sTempDesc & "', '" & sTempAbbrev & "'" & ")")
mySQL1.TransactionSuccessful
Catch
Msgbox("Error writing to Body Table","")
Log(LastException.Message)
End Try
mySQL1.EndTransaction
DoEvents
Next

myTesting = myTesting + 1
If myTesting < 4 Then '1,2,3 works 4 stops working
cmd.Name = "select_body"
reqManager.ExecuteQuery(cmd, 0, "SelectBody")
End If
End Select
End If
End If
Job.Release
 
Status
Not open for further replies.
Top