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

 

[PierPaduan]

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

Hi Erel and everybody else, I'm trying to understand this tutorial. I'm unable to connect to the jdbc web server from the browser. I'm new to this argument so probably I make some stupid errors. Please may you or someone else help me? Thanks a Lot.

I have followed instructions ( I hope ).
I have "sqlite-jdbc-3.7.2.jar" in jdbc_driver folder
My config.properties file is:

the c3p0.properties file is:

with sqliteexpert I have created a test.db file with a table "animals" and two records inside it ( fields are id, name, image ).
test.db is in "c:"

I have modified RunRLC.bat to address java.exe on my pc.
I run RunRLC.bat and I see:

The windows firewall of my pc is deactivated. I think that is not necessary to create an exception on the network firewall, is correct?
After that I try to connect to the database from my pc ( my pc is in a LAN and IP is: 192.168.100.187 ) with chrome and I obtain:

I've tried from another pc in the LAN and the resul has been the same.
And the exception I think is this one:

opening db: 'C: est.db': the sintax of filename, folder or the volume is incorrect.
I don't know what to do now.
Thanks a lot Erel.

 

[Erel]

Try to move the database to a different folder. There might be permission issues with the root folder.

Change the slashes to:

JdbcUrl=jdbc:sqlite:C:/temp/test.db

Tip: Don't use Notepad. Switch to Notepad++.

 

[PierPaduan]

Try to move the database to a different folder. There might be permission issues with the root folder.

Change the slashes to:

JdbcUrl=jdbc:sqlite:C:/temp/test.db

Tip: Don't use Notepad. Switch to Notepad++.

WOW, now it works on my pc and on the other one:

RemoteServer is running (Tue Apr 08 16:10:24 CEST 2014)
Connection successful.

Tomorrow I will try to connect with the client.
Thanks a Lot Erel Have a nice evening.

 

[PierPaduan]

Hi Erel.
All works very well. Thanks Thanks Thanks.....

Now I hope you can give me your opinion about the following issue.

I have implemented in the database above a new able "users" with "username" and "psw" columns; in the table "animals" I have added a column "user". Modified config.properties file and the app, now I access to database with user and password and I can "create new account" as well.
Do you think this is a good and safe approach to manage multiple users on a SQLite database?
Thanks a Lot and Best Regards.
Bye Bye.

 

[Erel]

Sounds good.

Storing passwords in a database is considered bad practice as someone might get access to the database and still the users passwords.
A better approach is explained here: http://www.b4x.com/android/forum/threads/server-login-system-filters-tutorial.39020/#content

 

[PierPaduan]

Sounds good.

Storing passwords in a database is considered bad practice as someone might get access to the database and still the users passwords.
A better approach is explained here: http://www.b4x.com/android/forum/threads/server-login-system-filters-tutorial.39020/#content

Thanks for fast answer Erel, but I'm not very clever, sorry.
This is a B4J example, correct?
Do I need to dowload B4J dev tool to compile it and run?
After that ,How can I connect to the login from my app?

Please be patient with me.
Thanks a Lot.
Bye.

 

[Erel]

This is a B4J example. However you can use the same "encryption" code to make the hashes.

Another option is to use B4J server instead of RDC. You can build powerful server solutions with B4J.

 

[LéonE]

Hi,

Followed all and working. Great job! (RDC)

now a question. i don't no where to begin.
i'm using in an app WebView1.LoadHtml(DBUtils.ExecuteHtml and DBUtils.ExecuteListView

i would like to use them also for remote. can someone give me a hint where to start or do i have to rewrite all?

as an example how to create a cursor for the remote database/table (sqlite.db).

thanks for any help.

 

[Erel]

DBUtils module works with local SQLite databases. You can download the data from RDC, add it to a local (or in memory) SQLite database and then use DBUtils.

 

[LéonE]

Thank you Erel,

I have seen the printtabel sub that logs a result.
What i wanted to know is how to create a cursor that allows me to put the cmd-result in a web view. Having that i can make a sycronize module. What i mean in following:

Dim result() as string
How to fill result from cmd to rdc.

I have tried some things but can't get it to work.
Please if you have some minutes in spare help me.
Do you have some simple code for this?

Thanks!

 

[Erel]

You cannot use a Cursor with the result from RDC. See how PrintTable is implemented. You can use similar code to go over all the results.

 

[achtrade]

Hi,

How can I do it for a MS SQL 2008 R2 under a Windows Server 2008 R2 ??

A step by step tutorial should be appreciate.

thanks.

 

[Erel]

Please start a new thread for this question.

 

[cncncn]

It's works with Paradox?

 

[Erel]

It works with any database that provides a JDBC driver.

According to this article a JDBC driver is included with Paradox: http://edn.embarcadero.com/article/21642

 

[johnB]

Have you actually called create_table from the device?

Have you called insert_animal?

Hi, sorry for my ignorance but I'm new to this aspect. I battled for a couple of hours thru the tutorials and forum, then had success but used a sqllite db I already had on my computer and was able to view a table with B4A.
Is there anywhere that explains the cmd(s).
With the create_table, I've no idea what parameters that I should use in the cmd.parameters line( ie cmd.Parameters = Array As Object) and kept on getting a table missing message, so I'm not able to create a table via this method but could use an existing db which proved that my connection was OK.

Thanks in advance

PS congratulations to all those that have obviously put many hours into helping others with both B4A and B4J - two great products. I now do my development work on B4J then transfer it to B4A, well done

 

[Erel]

The parameters in cmd.Parameters array will replace the question marks in the sql statement. If there are no question marks then you can set it to be Null.

 

[johnB]

The parameters in cmd.Parameters array will replace the question marks in the sql statement. If there are no question marks then you can set it to be Null.

Erel, thanks again for your prompt reply, will try it later

Thanks again for all your hard work

 

[luiswagnersantos]

Hi Erel.
I'm trying to insert records in my table
My Database is firebird
Firebird is configured successfully in RDC - Server

config.properties file.

#************************************************************************
#Conexão com banco Firebird - Local
DriverClass=org.firebirdsql.jdbc.FBDriver
JdbcUrl=jdbc:firebirdsql:192.168.0.51:C:/Guarani Local/Guarani ERP/Banco/17_04_2014_120000.fdb
#JdbcUrl=jdbc:firebirdsql://192.168.0.51/test?characterEncoding=utf8
User=SYSDBA
Password=masterkey
ServerPort=17178
Debug=True

#commands
sql.select_clientes=SELECT clientes.cli_codigo, clientes.cli_razao FROM clientes
sql.insert_clientes=INSERT INTO clientes (codigo,nome,ativo,hardreset)VALUES(?,?,?,?)

My problem is in inserting records

I am using this command RDC

sql.insert_clientes=INSERT INTO clientes (codigo,nome,ativo,hardreset)VALUES(?,?,?,?)

I am using the example RemoteServerClient

what's wrong?
sql syntax or code in remoteServerClient?

Log do Basic4Android

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error

SQL error code = -206
Column unknown
CODIGO
At line 1, column 23
Error: Server Error

I believe I am wrong in my code.
Could anyone give some tips?
Tank You
Wagner

 

[LucaMs]

Your message error:

SQL error code = -206
Column unknown
CODIGO


CODIGO is not a field in Clientes (fdb?)