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
 

LucaMs

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

EddyW

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

EddyW

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

JohnD

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

Attachments

  • rdc_client.zip
    9.1 KB · Views: 481
  • config_c3po.zip
    885 bytes · Views: 420
Status
Not open for further replies.
Top