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

Discussion in 'Tutorials & Examples' started by Erel, Aug 7, 2013.

Thread Status:
Not open for further replies.
  1. Erel

    Erel Administrator Staff Member Licensed User

    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):
      Code:
      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:
      Code:
      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
     
    jmon and tdocs2 like this.
  2. devlei

    devlei Active Member Licensed User

    Two Questions:

    Does this mean the SQLite file must reside on a website somewhere, or can it be on a pc that is connected to the internet?

    I use a SQLite database that is stored locally (on the device), that I want to synchronise with a central database (that can also be accessed by other devices). I had planned to synchronise the local SQLite with a MySQL database on the server. Would it be easier (& better) to use this method of using a SQLite file as the central database?
     
  3. derez

    derez Expert Licensed User

    I have just played with this capability and it works very well.
    I had SQLite DB in my devices, now I copied it to the pc and I can see it with "sqlite expert personal" which I downloaded from the internet (link in the first post). I edited the config.properties and the devices now can insert or load data from the pc DB.
    It works only in the local wifi net because I didn't put the port in my router, but this can be done if you want the DB exposed to the whole internet. It will be a good idea to use security measures before doing so...
     
    Last edited: Aug 12, 2013
  4. devlei

    devlei Active Member Licensed User

    Thanks, David!

    I didn't word my previous question properly:
    Would it be easier (& better) to use this method of using a SQLite file as the central database as opposed to using MySQL server?
     
  5. coslad

    coslad Active Member Licensed User

    It would be very interesting, even for those applications with few write accesses to the database
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    The advantage of this solution (based on SQLite) is that it doesn't require any configuration. You do not need to run a MySQL server.

    This solution is suited for cases where there will not be many concurrent requests. Otherwise a solution based on a database server will be better.
     
    devlei likes this.
  7. alienhunter

    alienhunter Active Member Licensed User

    Hi to all ,

    how do i write in the database , i don't quite understand
    i am able to read the database but not write


    Code:
    Sub putjobnr(jobnr As String)
        
    Dim cmd As DBCommand
        cmd.Initialize
        cmd.Name = 
    "insert_animal"
        cmd.Parameters = 
    Array As Object(jobnr,jobnr,jobnr,"Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null","Null")
        reqManager. xxxxxx here how 
    to ?
    End Sub

    the config.properties are set like this
    sql.insert_animal=INSERT INTO animals VALUES (null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)


    :eek:o_Oo_Oo_O:mad:

    thanks AH
     
  8. derez

    derez Expert Licensed User

    Code:
    reqManager.ExecuteCommand(cmd,0)
    The last number is the ID of the job, you can put any number.
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    @alienhunter if this issue was not resolved then please start a new thread.
     
  10. liangchaozu

    liangchaozu Member Licensed User

    @Erel,@alienhunter,I met the same question.I can't insert data into ms sqlserver,but can delete,update,select.

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

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

    #SQL Server
    DriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
    JdbcUrl=jdbc:sqlserver://localhost:1433;databaseName=Pexist
    User=sa
    Password=adminadmin
    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=false

    #commands
    sql.update_animal=update Pexist.dbo.animals set name=10
    sql.insert_animal=INSERT into Pexist.dbo.animals(NAME,ID) VALUES(1,1)
    sql.delete_animal=delete FROM Pexist.dbo.animals
    sql.select_animal=SELECT name FROM Pexist.dbo.animals WHERE id = ?


    client code:
    Sub Activity_Click
    GetAnimal("2","mmm")
    End Sub

    Sub GetAnimal(Name As String,id As String )
    Dim cmd As DBCommand
    cmd.Initialize
    ' cmd.Name = "select_animal"
    ' cmd.Parameters = Array As Object(Name)
    ' reqManager.ExecuteQuery(cmd, 0, Null)
    ' cmd.Name = "delete_animal"
    ' reqManager.ExecuteCommand(cmd,0)
    ' cmd.Name = "update_animal"
    ' reqManager.ExecuteCommand(cmd,0)
    cmd.Name = "insert_animal"
    ' cmd.Parameters = Array As Object("1","1")
    reqManager.ExecuteCommand(cmd,0)

    End Sub

    i test,select_animal,delete_animal,update_animal,the three commands are ok.Just insert_animal command is failed.
    com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException:
    Incorrect syntax near the keyword 'SET'.

    can you help me?
     
    patrick14384 likes this.
  11. derez

    derez Expert Licensed User

    Strange because in insert there is no SET...
    however, the string should be:
    INSERT into Pexist.dbo.animals(NAME,ID) VALUES(?,?)
    The values are supplied in the cmd.parameter
     
  12. liangchaozu

    liangchaozu Member Licensed User

    derez,thanks.
    I try it, but the same.
    1.JPG
    2.JPG 3.JPG
     
  13. liangchaozu

    liangchaozu Member Licensed User

    Is it possible there is a bug in RDC server?
     
  14. Erel

    Erel Administrator Staff Member Licensed User

    You should start a new thread for your question (this thread is about RDC with SQLite). Please also post the error message you get after you corrected the SQL statement.
     
  15. acc

    acc Member Licensed User

     
  16. acc

    acc Member Licensed User

    Hi,

    Am new to B4A, and i have test this great tool with success using SQL Server as my backend. However, could RDC server accommodate multiple client devices synchronizing data with the server? Am planning to use SQLlite on all my android clients and they will all access remotely.

    Thanks in advance.
     
  17. Erel

    Erel Administrator Staff Member Licensed User

    RDC can manage multiple concurrent connections. You will need however to implement the data synchronization.
     
  18. acc

    acc Member Licensed User

    Thanks Erel.
     
  19. peggjones

    peggjones Active Member Licensed User

    Can anyone tell me where the jdbc_driver folder is?

    Many Thanks.
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    It should be in the same folder you extracted RDC-Server.zip.
     
Thread Status:
Not open for further replies.
Loading...