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

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

Active Member
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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...
 
Last edited:

devlei

Active Member
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
It would be very interesting, even for those applications with few write accesses to the database
 

alienhunter

Active Member
Licensed User
Longtime User
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


B4X:
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,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)


:eek:o_Oo_Oo_O:mad:

thanks AH
 

derez

Expert
Licensed User
Longtime User
B4X:
reqManager.ExecuteCommand(cmd,0)
The last number is the ID of the job, you can put any number.
 

liangchaozu

Member
Licensed User
Longtime User
@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

Expert
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
derez,thanks.
I try it, but the same.
1.JPG

2.JPG
3.JPG
 

acc

Member
Licensed User
Longtime User
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):
    B4X:
    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:
    B4X:
    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

Member
Licensed User
Longtime User
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.
 
Status
Not open for further replies.
Top