Android Example RDC - Simple way to create your own back-end database

Remote Database Connector (RDC) is a middleware web server that allows you to easily connect your Android app to any type of remote database server.
Usually you will use it with a database server (MySQL, DB2, etc...).

However you can also use RDC without a database server. Instead you can use a SQLite database file. In that case the database server is not needed.

This is done with sqlite-jdbc driver. This is an open source project that provides a JDBC driver for SQLite databases.

You can download the driver here (sqlite-jdbc-3.7.2.jar): https://bitbucket.org/xerial/sqlite-jdbc/downloads

Configuration:

1. Copy the jar file to the jdbc_driver folder.
2. Set the following lines in config.properties (change the path to your database file):
   
   DriverClass=org.sqlite.JDBC
   JdbcUrl=jdbc:sqlite:C:/temp/test.db
3. SQLite doesn't support concurrent writings. So it is better to limit the connection pool to a single connection. This means that if there are multiple requests at parallel they will wait for the previous transaction to complete.
   To limit the connection pool you need to edit c3p0.properties and add:
   
   c3p0.minPoolSize=1
   c3p0.maxPoolSize=1

You can use a tool such as SQLite Expert to open the database and administrate it: http://www.sqliteexpert.com/download.html

 

[luiswagnersantos]

Hi, LucasMS
Codigo is a field in Clientes

 

[LucaMs]

That is in:
C:\RDC Server\db\Database.fdb

Your config.properties:
JdbcUrl=jdbc:firebirdsql:192.168.0.51:C:/Guarani Local/Guarani ERP/Banco/17_04_2014_120000.fdb

 

[luiswagnersantos]

sorry, sorry
my path database is wrong!

JdbcUrl=jdbc:firebirdsql:192.168.0.51:C:/RDC Server/db/database.fdb

testing again,
tank you

 

[luiswagnersantos]

That is in:
C:\RDC Server\db\Database.fdb

Your config.properties:
JdbcUrl=jdbc:firebirdsql:192.168.0.51:C:/Guarani Local/Guarani ERP/Banco/17_04_2014_120000.fdb

Luca, I resolved problem with conexion firebird.
sql select is work!
but, insert is problem.
Could you post a code insertion in sql basic4android ?

sql.insert_clientes=INSERT INTO clientes (codigo,nome,ativo,hardreset)VALUES(?,?,?,?)

Tank You

 

[LucaMs]

Private Query As String
Query = "INSERT INTO clientes (codigo,nome,ativo,hardreset) VALUES (?,?,?,?)"
SQL.ExecNonQuery2(Query, Array As Object(10, "Luis", 1, 0))

 

[luiswagnersantos]

My problem is in the code Basic4ppc write data via rdc

My code basic4android

#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    Dim reqManager As DBRequestManager
End Sub

Sub Globals

End Sub

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

Sub Activity_Click
    'GetAnimal("DIVERSOS.pdf") Usando sqlite
    'GetAnimal("")
    InsertData("")
End Sub

'here!
Sub InsertData(Name As String)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "insert_clientes"
    cmd.Parameters = Array As Object(cmd,4,"wagner",1,0)
    reqManager.ExecuteQuery(cmd, 0, Null)
End Sub

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

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)
            reqManager.PrintTable(result)
        End If
    End If
    Job.Release
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Log IDE

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
org.firebirdsql.jdbc.field.TypeConversionException: Error converting to int. [Parameters=[Ljava.lang.Object;@427cf558, Name=insert_clientes, IsInitialized=true
]
Error: Server Error

the error here?

Sub InsertData(Name As String)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "insert_clientes"
    cmd.Parameters = Array As Object(cmd,4,"wagner",1,0)
    reqManager.ExecuteQuery(cmd, 0, Null)
End Sub

or

File: config.properties in RDC Server

sql.insert_clientes=INSERT INTO clientes (codigo,nome,ativo,hardreset)VALUES(?,?,?,?)

Tank You
Wagner

 

[LucaMs]

Sub InsertClientes(Codigo As Int, Name As String, Ativo As Int, HardReset As Int)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "insert_clientes"
    cmd.Parameters = Array As Object(Codigo, Name, Ativo, HardReset)
    reqManager.ExecuteCommand(cmd, "InsertClientes")
End Sub

 

[luiswagnersantos]

Perfect!
Tank You!
Wagner

 

[ixdev]

Hi I'm using this to connect to MS SQL Server, when I'm inserting records and the WiFi is off, Job.Success still returns True. shouldn't it be false?

 

[Erel]

Are you 100% sure that there is no valid connection between the device and the server?

Job.Success will only be true if the server has responded successfully.

 

[ixdev]

Are you 100% sure that there is no valid connection between the device and the server?

Job.Success will only be true if the server has responded successfully.

Hi Erel,

I'm only sure that the records where not inserted in the server, however I'm not a 100% sure about the connection.
It runs smoothly when I'm debugging since it is 100% connected, but seems to fail when the user transfers to another access point.

What the app does is log the time that it is in a certain place in the building then updates it in the server. after transferring to another
room/place it seems the phone takes longer to connect to the new access point therefore I concluded that the user probably triggers the update
without making sure that the phone is connected. but then again I'm not a 100% sure.

 

[Erel]

Check your code. Job.Success can only be true if the server responded with a 200 code. In all other cases it will be false. RDC only responds with 200 if there were no errors.

 

[ixdev]

Check your code. Job.Success can only be true if the server responded with a 200 code. In all other cases it will be false. RDC only responds with 200 if there were no errors.

I'm not really sure what to check, what I temporarily did was run another command on the job success of the insert command to query for the last inserted data before tagging the local DB as transferred. Now it works as needed but I would prefer to just tag the local db as transferred without having to query from the server after trying to insert.

 

[tdocs2]

Sub InsertClientes(Codigo As Int, Name As String, Ativo As Int, HardReset As Int)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "insert_clientes"
    cmd.Parameters = Array As Object(Codigo, Name, Ativo, HardReset)
    reqManager.ExecuteCommand(cmd, "InsertClientes")
End Sub

Ciao, Luca.

First, to Erel - this is a wonderful and clever piece of architecture (not trivial). Thank you.

Luca, your code helped me on the insert in the same fashion as it did Wagner. I am still in the very early stages of testing, but like many others, I had problems with the insert. Actually, I had problems deciphering the whole process. But Erel kept pointing out connection and firewall issues which I focused on. Then, I did a query for "Ocelot" and it worked, but then I went over and over the insert issue until I found your post (had to read 4 pages of comments, but worthwhile).

I am using the db that came with SQLite Expert, table "animals".

Lots of components to this RDC process:

Identifying PC IP and using a port
Windows Firewall Issues (adding a new rule by allowing incoming thru the selected port)
RDC setup for PC including the concept of modifying the config.properties (I am still learning)
RDC client (B4A component)

Thank you and best regards.

Sandy

 

[Tom1s]

I am trying to use RDC to make remotedatabase copy before i read the table. Query works ok in mssql and I can read(select) ok with RDC.

sql.insert_animals=INSERT dbo.Loaded_sync (ID,Timeloaded,Name) SELECT ID,Timeloaded,Name FROM (DELETE dbo.Loaded OUTPUT DELETED.ID,DELETED.Timeloaded,DELETED.Name) AS RowsToMove

Gives alot of errors. How should I call it if I dont want to send or receive anything?
reqManager.ExecuteCommand ?

Thanks

 

[Tom1s]

Finally I think I got it...too easy if it is ok.

Sub GetAnimal(Name As String)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "insert_animals"

    reqManager.ExecuteCommand(cmd,cmd)
End Sub

 

[vecino]

Hi, RDC works on a Linux server?
Regards.

 

[Erel]

Please start a new thread for this question.

 

[vecino]

I'm sorry.

 

[chris ash]

Hi there

I've been having a play with this functionality and am having a problem. The error seems quite self explanatory but I cannot work out why it is not working.

Ok so I am trying to use an SQLite database file. I have set up the configuration file as follows as per the attached config.png image

Which this all seems to work as I then run the RunRLC.bat file and get the screen shown in starting.png.

I believe this is working as I get the working.png message both on my PC from which it is running, and also my mobiles browser on the same network.

I then use the project template supplied to try and run a query "ListAvailableSlots" and then get the error shown in the error.png file.

As this error seems to know the table its looking for "sql.ListAvailableSlots=SELECT * FROM [SlotAvailability];" where SlotAvailability is the table in the SQL query. I think the app side of things is ok, and the comms to the server is ok. Its just that it is either not seeing the database, or it does not have access to it?

I have tried all sorts of combinations on the config file, but I cant get it working.

Is there something obvious that I am missing?

Thanks in advance