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

 

[Erel]

How are you testing the database?

You can try to open it with this B4J tool:
SQLite Viewer

 

[LucaMs]

How are you testing the database?

You can try to open it with this B4J tool:
SQLite Viewer

I used SQLite Database Browser 2.0.

Putting a db created "from" DBUtils in the Temp folder, I have not had any problems

 

[LucaMs]

I tried SQLite Viewer (I like it) but ... I saw that test.db size is 0 bytes

 

[Erel]

You should use the driver that is mentioned in the first post of this tutorial. Make sure that you created at least one table.

 

[LucaMs]

I used that driver.

C:\Program Files\Java\jdk1.7.0_07\bin\java

May be jdk1.7.0_07 the problem?

config.properties "creates" the table:

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

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

# - SQL Server
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<database server ip>/<database>

# - SQLite
DriverClass=org.sqlite.JDBC
JdbcUrl=jdbc:sqlite:C:/temp/test.db

User=root
Password=
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

sql.create_table=CREATE TABLE animals (\
    id INT NOT NULL AUTO_INCREMENT,\
    name CHAR(30) NOT NULL,\
    image BLOB,\
    PRIMARY KEY (id))
sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)
sql.select_animal=SELECT name, image FROM animals WHERE name = ?

 

[Erel]

Have you actually called create_table from the device?

Have you called insert_animal?

 

[LucaMs]

Have you actually called create_table from the device?

Have you called insert_animal?

Ehm... not !

Stupidly, I had assumed that it would create even the table!

S O R R Y

 

[LucaMs]

Unbelievable: it works now

 

[EddyW]

Hi i try to get the server running but looks like i am doing wrong but dont see it

downloaded sqlite-jdbc-3.7.2.jar and placed it in the jdbc_driver folder

config.properties

#SQLite
DriverClass=org.sqlite.JDBC
JdbcUrl=jdbc:sqlite:d:/dropbox/Radio.sqlite
User=root
Password=
ServerPort=17178

c3p0.properties

c3p0.maxStatements=150
c3p0.maxIdleTime=1800
c3p0.idleConnectionTestPeriod=600
c3p0.checkoutTimeout=20000
c3p0.minPoolSize=1
c3p0.maxPoolSize=1

runRLC.cmd

cd "C:\Program Files (x86)\Java\jre7\bin\"
java.exe -Xmx256m -cp .;libs\*;jdbc_driver\*
anywheresoftware.b4a.remotedatabase.RemoteServer
pause

error:
C:\Program Files (x86)\Java\jre7\bin>java.exe -Xmx256m -cp .;libs\*;jdbc_driver\*
Usage: java [-options] class [args...]
(to execute a class)
or java [-options] -jar jarfile [args...]
(to execute a jar file)
where options include:
-d32 use a 32-bit data model if available
-d64 use a 64-bit data model if available
-client to select the "client" VM
-server to select the "server" VM
-hotspot is a synonym for the "client" VM [deprecated]
The default VM is client.

-cp <class search path of directories and zip/jar files>
-classpath <class search path of directories and zip/jar files>
A ; separated list of directories, JAR archives,
and ZIP archives to search for class files.
-D<name>=<value>
set a system property
-verbose:[class|gc|jni]
enable verbose output


looks like it that it dont reconize the java command or 1 of the parameters

i try it on a windows 7 laptop

 

[Erel]

You should change the batch file and instead of changing the directory you should call:
"C:\Program Files (x86)\Java\jre7\bin\java.exe" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheresoftware.b4a.remotedatabase.RemoteServer

 

[EddyW]

thx Erel, somehow i got a <enter> just before anywheresoftware.b4a.remotedatabase.RemoteServer.
Did try several things except place everything behind each other

 

[JohnD]

I'm having trouble with the RDC-Client. I turn my firewall off - temporarily. When I issue: http://192.168.1.122:17178/?method=test from my browser I get.

RemoteServer is running (Thu Feb 27 10:04:07 EST 2014)
Connection successful.

The StartJob() sub may have problems with XML formatting??

Private Sub StartJob(j As HttpJob, MemoryStream As OutputStream, Tag As Object) As OutputStream
    j.Initialize("DBRequest", mTarget)
    j.Tag = Tag
    MemoryStream.InitializeToBytesArray(0)
    Dim compress As CompressedStreams
  
    'When I look at MemoryStream below it says the XML is improperly formatted.
    Dim out As OutputStream = compress.WrapOutputStream(MemoryStream, "gzip")

In Log:
Error:...Connection to http://192.168.1.122:17178 refused

I realize I am using persons.db. I created one table called animals and am trying to issue a Select query.
config.properties and c3po.properties are in an attached zip. Your help is appreciated. Thanks, JD.

 

[Erel]

Have you tried to call the test url from the device browser?

 

[JohnD]

Have you tried to call the test url from the device browser?

Yes. It says document not found. Should I be calling it a different way than: http://192.168.1.122:17178/?method=test

 

[Erel]

This means that the PC is not accessible. Are you using an emulator?

 

[JohnD]

No, my Galaxy s3 phone.

 

[Erel]

The PC is not accessible from the device. This is most likely a firewall issue (or a wrong IP address).

 

[JohnD]

I agree. I thought of peeling away my router very briefly and using my nic address. Just to prove to myself I can get the example working. Thanks, JD.

 

[JohnD]

No luck with router removal. I've decided to move on to another task since this isn't critical for me now. Thanks for your help. JD.

 

[JohnD]

I found the cause. My ISP prevents users from local hosting.