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
 
Click to expand...
P

PierPaduan

Active Member
Licensed User
Longtime User
Erel said:
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
Click to expand...


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:
upload_2014-4-8_10-31-22.png


the c3p0.properties file is:
upload_2014-4-8_10-30-11.png


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:"
upload_2014-4-8_10-38-27.png


I have modified RunRLC.bat to address java.exe on my pc.
I run RunRLC.bat and I see:
upload_2014-4-8_10-19-56.png


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:
upload_2014-4-8_10-23-27.png

I've tried from another pc in the LAN and the resul has been the same.
And the exception I think is this one:
upload_2014-4-8_10-25-54.png

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.
 
Last edited:
P

PierPaduan

Active Member
Licensed User
Longtime User
Erel said:
Try to move the database to a different folder. There might be permission issues with the root folder.

Change the slashes to:
B4X: 
JdbcUrl=jdbc:sqlite:C:/temp/test.db


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

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.
 
P

PierPaduan

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

PierPaduan

Active Member
Licensed User
Longtime User
Erel said:
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
Click to expand...

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.
 
L

LéonE

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

LéonE

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

achtrade

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

johnB

Active Member
Licensed User
Longtime User
Erel said:
Have you actually called create_table from the device?

Have you called insert_animal?
Click to expand...
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
 
johnB

johnB

Active Member
Licensed User
Longtime User
Erel said:
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.
Click to expand...
Erel, thanks again for your prompt reply, will try it later

Thanks again for all your hard work
 
luiswagnersantos

luiswagnersantos

Member
Licensed User
Longtime User
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.
B4X: 
#************************************************************************
#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:mad:

I am using this command RDC

B4X: 
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
B4X: 
** 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
 

Attachments

  • RemoteServerClientSample.zip
    9.3 KB · Views: 544
Status
Not open for further replies.
Top