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

Erel · Aug 7, 2013

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:

Copy the jar file to the jdbc_driver folder.
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
```
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

Erel · Jan 3, 2014

How are you testing the database?

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

LucaMs · Jan 3, 2014

Erel said:
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 · Jan 3, 2014

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

Erel · Jan 3, 2014

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 · Jan 3, 2014

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:

B4X:

#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 · Jan 3, 2014

Have you actually called create_table from the device?

Have you called insert_animal?

LucaMs · Jan 3, 2014

Erel said:
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 · Jan 3, 2014

Unbelievable: it works now

EddyW · Jan 29, 2014

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

B4X:

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

c3p0.properties

B4X:

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

runRLC.cmd

B4X:

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 · Jan 30, 2014

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 · Jan 30, 2014

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

JohnD · Feb 27, 2014

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

B4X:

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 · Feb 27, 2014

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

JohnD · Feb 27, 2014

Erel said:
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 · Feb 27, 2014

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

JohnD · Feb 27, 2014

No, my Galaxy s3 phone.

Erel · Feb 28, 2014

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

JohnD · Feb 28, 2014

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 · Mar 2, 2014

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 · Mar 7, 2014

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

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

B4X founder

Expert

Expert

B4X founder

Expert

B4X founder

Expert

Expert

Member

B4X founder

Member

Active Member

Attachments

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

Active Member

Active Member

Privacy & Transparency

Privacy & Transparency