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

 

[devlei]

Two Questions:

Does this mean the SQLite file must reside on a website somewhere, or can it be on a pc that is connected to the internet?

I use a SQLite database that is stored locally (on the device), that I want to synchronise with a central database (that can also be accessed by other devices). I had planned to synchronise the local SQLite with a MySQL database on the server. Would it be easier (& better) to use this method of using a SQLite file as the central database?

 

[derez]

I have just played with this capability and it works very well.
I had SQLite DB in my devices, now I copied it to the pc and I can see it with "sqlite expert personal" which I downloaded from the internet (link in the first post). I edited the config.properties and the devices now can insert or load data from the pc DB.
It works only in the local wifi net because I didn't put the port in my router, but this can be done if you want the DB exposed to the whole internet. It will be a good idea to use security measures before doing so...

 

[devlei]

Thanks, David!

I didn't word my previous question properly:
Would it be easier (& better) to use this method of using a SQLite file as the central database as opposed to using MySQL server?

 

[coslad]

It would be very interesting, even for those applications with few write accesses to the database

 

[Erel]

The advantage of this solution (based on SQLite) is that it doesn't require any configuration. You do not need to run a MySQL server.

This solution is suited for cases where there will not be many concurrent requests. Otherwise a solution based on a database server will be better.

 

[alienhunter]

Hi to all ,

how do i write in the database , i don't quite understand
i am able to read the database but not write

Sub putjobnr(jobnr As String)
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "insert_animal"
    cmd.Parameters = Array As Object(jobnr,jobnr,jobnr,"Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null")
    reqManager. xxxxxx here how to ?
End Sub

the config.properties are set like this
sql.insert_animal=INSERT INTO animals VALUES (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)




thanks AH

 

[derez]

reqManager.ExecuteCommand(cmd,0)

The last number is the ID of the job, you can put any number.

 

[Erel]

@alienhunter if this issue was not resolved then please start a new thread.

 

[liangchaozu]

@Erel,@alienhunter,I met the same question.I can't insert data into ms sqlserver,but can delete,update,select.

config.properties:
#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DriverClass=com.mysql.jdbc.Driver
#JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8

#SQL Server
DriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
JdbcUrl=jdbc:sqlserver://localhost:1433;databaseName=Pexist
User=sa
Password=adminadmin
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=false

#commands
sql.update_animal=update Pexist.dbo.animals set name=10
sql.insert_animal=INSERT into Pexist.dbo.animals(NAME,ID) VALUES(1,1)
sql.delete_animal=delete FROM Pexist.dbo.animals
sql.select_animal=SELECT name FROM Pexist.dbo.animals WHERE id = ?


client code:
Sub Activity_Click
GetAnimal("2","mmm")
End Sub

Sub GetAnimal(Name As String,id As String )
Dim cmd As DBCommand
cmd.Initialize
' cmd.Name = "select_animal"
' cmd.Parameters = Array As Object(Name)
' reqManager.ExecuteQuery(cmd, 0, Null)
' cmd.Name = "delete_animal"
' reqManager.ExecuteCommand(cmd,0)
' cmd.Name = "update_animal"
' reqManager.ExecuteCommand(cmd,0)
cmd.Name = "insert_animal"
' cmd.Parameters = Array As Object("1","1")
reqManager.ExecuteCommand(cmd,0)

End Sub

i test,select_animal,delete_animal,update_animal,the three commands are ok.Just insert_animal command is failed.
com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near the keyword 'SET'.

can you help me?

 

[derez]

Strange because in insert there is no SET...
however, the string should be:
INSERT into Pexist.dbo.animals(NAME,ID) VALUES(?,?)
The values are supplied in the cmd.parameter

 

[liangchaozu]

derez,thanks.
I try it, but the same.

 

[liangchaozu]

Is it possible there is a bug in RDC server?

 

[Erel]

You should start a new thread for your question (this thread is about RDC with SQLite). Please also post the error message you get after you corrected the SQL statement.

 

[acc]

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

 

[acc]

Hi,

Am new to B4A, and i have test this great tool with success using SQL Server as my backend. However, could RDC server accommodate multiple client devices synchronizing data with the server? Am planning to use SQLlite on all my android clients and they will all access remotely.

Thanks in advance.

 

[Erel]

RDC can manage multiple concurrent connections. You will need however to implement the data synchronization.

 

[acc]

RDC can manage multiple concurrent connections. You will need however to implement the data synchronization.

Thanks Erel.

 

[peggjones]

Can anyone tell me where the jdbc_driver folder is?

Many Thanks.

 

[Erel]

It should be in the same folder you extracted RDC-Server.zip.