Android Tutorial Remote Database Connector (RDC) - Connect to any remote DB

Status
Not open for further replies.
jRDC version 2 is available here: https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/

This tutorial covers a new framework named Remote Database Connector (RDC). The purpose of RDC is to make it simple to develop Android applications that interact with remote database servers.

There are two components in this framework: a lightweight Java web server (the middleware) and a B4A class named DBRequestManager that interacts with this web server.

The Java web server can connect to any database platform that provides a JDBC driver.

This includes: MySQL, SQL Server, Oracle, Sybase, DB2, postgreSQL, Firebird and many others.

You can also use the web server without a database server by connecting to a local SQLite database file.

The Java web-server is a simple server that connects to the database server and to the Android clients.
As this is a Java app you can run it on Linux or Windows computers. It requires Java JRE 6 or 7.

This solution is much more powerful than the PHP (MySQL) and ASP.Net (MS SQL) solutions that are already available.

Main advantages over previous solutions:
  • Support for many types of database platforms.
  • Significantly better performance.
  • SQL statements are configured in the server (safer).
  • Support for all types of statements, including batch statements.
  • Support for BLOBs.

Server Configuration

JDBC is a Java API that provides a standard method to access any database. A database driver (jar file) is required for each type of database. You will need to download the driver for your database and put it in the jdbc_driver folder.

The Java server configuration is saved in a file named config.properties.
This file includes two sections: general configuration and a list of SQL commands.

For example:

SS-2013-08-04_16.10.20.png


Note that the configuration fields are case sensitive.

DriverClass / JdbcUrl - The values of these two fields are specific to each database platform. You will usually find the specification together with the required driver jar file.

User / Password - Database user and password.

ServerPort - The Java server will listen to this provided port.

Debug - If Debug is set to true then the SQL commands list will be loaded on every request. This is useful during development as it allows you to modify the commands without restarting the server process.

SQL Commands - A list of commands. Each command starts with 'sql.'. The commands can include question marks (parameterised queries). Question marks will be replaced with the values provided by the Android clients. Note that the command name is case sensitive and it doesn't include the 'sql.' prefix.

Client Code
The client sends requests to the server. There are two types of requests: query requests (usually SELECT statements) and batch requests (any other statement).

Note that both the client and the server can manage multiple requests in parallel.
Usually you will only need a single DBRequestManager object.

Each request is made of a command (or a list of commands) and a tag. The tag can be any object you like. You can use it later when the result is ready. The tag is not sent to the server.

A command is an object of type DBCommand:
B4X:
Type DBCommand (Name As String, Parameters() As Object)
Name - The case sensitive command name as configured in the server configuration (without sql.).
Parameters - An array of objects that will be sent to the server and will replace the question marks in the command.

For example to send a SELECT request:
B4X:
Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "select_animal"
cmd.Parameters = Array As Object("cat 1")
reqManager.ExecuteQuery(cmd, 0, "cat 1")
ExecuteQuery expects three parameters: the command, maximum number of rows to return or 0 if there is no limit and the tag value.

Under the hood DBRequestManager creates a HttpJob for each request. The job name is always "DBRequest".

You should handle the JobDone event in your activity or service:
B4X:
Sub JobDone(Job As HttpJob)
   If Job.Success = False Then
     Log("Error: " & Job.ErrorMessage)
   Else
     If Job.JobName = "DBRequest" Then
       Dim result As DBResult = reqManager.HandleJob(Job)
       If result.Tag = "cat 1" Then 'query tag
         For Each records() As Object In result.Rows
           Dim name As String = records(0) 'or records(result.Columns.Get("name"))
           Log(name)
         Next
       End If
     End If
   End If
   Job.Release
End Sub

As you can see in the above code, DBRequestManager.HandleJob method takes the Job and returns a DBResult object:
B4X:
Type DBResult (Tag As Object, Columns As Map, Rows As List)
Tag - The request tag.
Columns - A Map with the columns names as keys and the columns ordinals as values.
Rows - A list that holds an array of objects for each row in the result set.

Non-select commands are sent with ExecuteCommand (single command) or ExecuteBatch (any number of commands). It is significantly faster to send one request with multiple commands over multiple requests. Batch statements are executed in a single transaction. If one of the commands fail all the batch commands will be cancelled (roll-backed).

Note that for non-select requests, Rows field in the results will hold an array with a single int for each command. The int value is the number of affected rows (for each command separately).

Initializing DBRequestManager:
B4X:
Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
     reqManager.Initialize(Me, "http://192.168.0.100:17178")
   End If
End Sub
The first parameter is the module that will handle the JobDone event.
The second parameter is the link to the Java server (with the port number).

DBRequestManager provides the following helper methods for common tasks related to BLOB fields: FileToBytes, ImageToBytes and BytesToImage.It also includes a method named PrintTable that prints DBTable objects to the logs.

Framework Setup
  1. Unpack the server zip file.
  2. You will need to download the driver for your database platform and copy the jar file to jdbc_driver folder.
  3. Edit config.properties as discussed above.
  4. Edit RunRLC.bat and set the path to java.exe. If you are running it on Linux then you need to create a similar script with the path to java. On Linux you should change the ';' separator to ':'.
  5. Run the server :)
    You should see something like:

    SS-2013-08-04_17.05.16.png


    Note that the path to config.properties is printed in the second line.
  6. Add an exception for the server port in your firewall.
  7. Try to access the server from the browser:

    SS-2013-08-04_17.06.17.png


    You will probably not see the above message on the first run :(. Instead you will need to check the server output and read the error message.
    If you are able to call the server from the local computer and not from other devices then it is a firewall issue.

Tips

  • MySQL driver is available here: http://dev.mysql.com/downloads/connector/j/
  • Google for <your database> JDBC Driver to find the required driver. For SQL Server you can use this open source project: http://jtds.sourceforge.net/
  • The server uses an open source project named c3p0 to manage the database connection pool. It can be configured if needed by modifying the c3p0.properties file.
  • Use a text editor such as Notepad++ to edit the properties file.
  • If you are running the server on Linux then you can run it with nohup to prevent the OS from killing the process when you log out.
  • Java doesn't need to be installed. You can just unpack the files.
The server is based on the two following open source projects:
Jetty - http://www.eclipse.org/jetty/
c3p0 - http://www.mchange.com/projects/c3p0/index.html
http://www.mchange.com/projects/c3p0/index.html
 
Last edited:

Softflow Systems

Member
Licensed User
Longtime User
There is no such method. You should use the code module attached to the first post to make the requests.

Dear Erel

I changed the method to test
and now the message in Log is coming like this
SQLException: An attempt by a client to checkout a connection has timed out.


I have followed exactly as the code module.

in config.properties I am giving
#SQL Server
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://192.168.2.110:17178/testdata
User=myuser
Password=mypass
ServerPort=17178


testdata is my database

I can connect to my database in ODBC by giving the server as 192.168.2.110/SQL2
SQL2 is the instance of SQL Server.

Please suggest the exact changes I should make in config.properties.
 

Softflow Systems

Member
Licensed User
Longtime User
The port in JdbcUrl is wrong. It should point to the SQL Server port. Probably 1433.

I changed to 1433 but still same error is coming

#SQL Server
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://49.249.131.39:1433/SQL2\testdata
User=sal
Password=data123
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

I changed it like this also
JdbcUrl=jdbc:jtds:sqlserver://49.249.131.39:1433\testdata

JdbcUrl=jdbc:jtds:sqlserver://49.249.131.39\testdata

but nothing works where is the problem.

I can read the database using ODBC, ADO.Net etc.
 

Softflow Systems

Member
Licensed User
Longtime User
Try to use 127.0.0.1 instead of the external ip address.

The error you are getting is a configuration error on your side. The db server is not responding to RDC.



The following error is coming in RDC LOG when I am testing it in internet with the following
http://localhost:17178/?method=test

instead of localhost I have tried 127.0.0.1 and also the IP of the computer


My config.properties file is as follows
#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://192.168.2.110/test?characterEncoding=utf8
#SQL Server
riverClass=net.sourceforge.jtds.jdbc.Drive
JdbcUrl=jdbc:jtds:sqlserver://198.68.2.105/testdata
User=sal
Password=mypass
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
#commands
sql.select_party=SELECT party_nm, acc_id FROM z_par_0001 WHERE bal_cd = 'A5'

(In the properties JdbcUrl I have tried with ip:1433 etc etc. Pls suggest the changes)


RDC LOG OUTPUT :

F:\android_projects\b4android\rdc\rdcserver\RemoteDatabaseConnector>"C:\Program
Files\Java\jdk1.7.0_45\bin\java" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheresof
tware.b4a.remotedatabase.RemoteServer
B4A Remote Database Connecter (version 0.9)
loading: F:\android_projects\b4android\rdc\rdcserver\RemoteDatabaseConnector\con
fig.properties
2014-01-14 17:39:15.905:INFO::jetty-7.4.2.v20110526
2014-01-14 17:39:15.970:INFO::started o.e.j.s.ServletContextHandler{/,null}
Jan 14, 2014 5:39:16 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Jan 14, 2014 5:39:16 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.2.1 [built 20-March-2013 11:16:28 +0000; debug? true
; trace: 10]
2014-01-14 17:39:16.428:INFO::Started [email protected]:17178 START
ING
Jan 14, 2014 5:39:22 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource ge
tPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acqu
ireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCo
mmitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> fa
lse, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connection
TesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceN
ame -> 1hge1d58z144xb2szd06bu|c042ab, debugUnreturnedConnectionStackTraces -> fa
lse, description -> null, driverClass -> null, factoryClassLocation -> null, for
ceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge1d58z144xb2szd06bu
|c042ab, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:
jtds:sqlserver://198.68.2.105/suruchi, maxAdministrativeTaskTime -> 0, maxConnec
tionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSiz
e -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3
, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******,
password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads ->
0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturn
edConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies
-> false ]
java.sql.SQLException: An attempt by a client to checkout a Connection has timed
out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(A
bstractPoolBackedDataSource.java:140)
at anywheresoftware.b4a.remotedatabase.Servlet.doGet(Servlet.java:64)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:538
)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java
:478)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandl
er.java:937)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:
406)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandle
r.java:871)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.j
ava:117)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper
.java:110)
at org.eclipse.jetty.server.Server.handle(Server.java:346)
at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.
java:589)
at org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete
(HttpConnection.java:1048)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:601)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:214)
at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:41
1)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEn
dPoint.java:535)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEnd
Point.java:40)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool
.java:529)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out whil
e waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePo
ol@68fede -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicRes
ourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(
BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicR
esourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConn
ectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:682)
 

Fifi Donkor

Member
Licensed User
Longtime User
The following error is coming in RDC LOG when I am testing it in internet with the following
http://localhost:17178/?method=test

instead of localhost I have tried 127.0.0.1 and also the IP of the computer


My config.properties file is as follows
#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://192.168.2.110/test?characterEncoding=utf8
#SQL Server
riverClass=net.sourceforge.jtds.jdbc.Drive
JdbcUrl=jdbc:jtds:sqlserver://198.68.2.105/testdata
User=sal
Password=mypass
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
#commands
sql.select_party=SELECT party_nm, acc_id FROM z_par_0001 WHERE bal_cd = 'A5'

(In the properties JdbcUrl I have tried with ip:1433 etc etc. Pls suggest the changes)


RDC LOG OUTPUT :

F:\android_projects\b4android\rdc\rdcserver\RemoteDatabaseConnector>"C:\Program
Files\Java\jdk1.7.0_45\bin\java" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheresof
tware.b4a.remotedatabase.RemoteServer
B4A Remote Database Connecter (version 0.9)
loading: F:\android_projects\b4android\rdc\rdcserver\RemoteDatabaseConnector\con
fig.properties
2014-01-14 17:39:15.905:INFO::jetty-7.4.2.v20110526
2014-01-14 17:39:15.970:INFO::started o.e.j.s.ServletContextHandler{/,null}
Jan 14, 2014 5:39:16 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Jan 14, 2014 5:39:16 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.2.1 [built 20-March-2013 11:16:28 +0000; debug? true
; trace: 10]
2014-01-14 17:39:16.428:INFO::Started [email protected]:17178 START
ING
Jan 14, 2014 5:39:22 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource ge
tPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acqu
ireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCo
mmitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> fa
lse, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connection
TesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceN
ame -> 1hge1d58z144xb2szd06bu|c042ab, debugUnreturnedConnectionStackTraces -> fa
lse, description -> null, driverClass -> null, factoryClassLocation -> null, for
ceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge1d58z144xb2szd06bu
|c042ab, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:
jtds:sqlserver://198.68.2.105/suruchi, maxAdministrativeTaskTime -> 0, maxConnec
tionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSiz
e -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3
, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******,
password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads ->
0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturn
edConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies
-> false ]
java.sql.SQLException: An attempt by a client to checkout a Connection has timed
out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(A
bstractPoolBackedDataSource.java:140)
at anywheresoftware.b4a.remotedatabase.Servlet.doGet(Servlet.java:64)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:538
)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java
:478)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandl
er.java:937)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:
406)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandle
r.java:871)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.j
ava:117)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper
.java:110)
at org.eclipse.jetty.server.Server.handle(Server.java:346)
at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.
java:589)
at org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete
(HttpConnection.java:1048)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:601)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:214)
at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:41
1)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEn
dPoint.java:535)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEnd
Point.java:40)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool
.java:529)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out whil
e waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePo
ol@68fede -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicRes
ourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(
BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicR
esourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConn
ectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConne
ction(C3P0PooledConnectionPool.java:682)


Hi Softflow,
I don't know if you've solved your issues since Tuesday. I just noticed that you had some typing errors in the config that you posted above.

Specifically the underlined line:

#DriverClass=com.mysql.jdbc.Driver
#JdbcUrl=jdbc:mysql://192.168.2.110/test?characterEncoding=utf8
#SQL Server
riverClass=net.sourceforge.jtds.jdbc.Drive
JdbcUrl=jdbc:jtds:sqlserver://198.68.2.105/testdata

Should read DriverClass=net.sourceforge.jdts.jdbc
or DriverClass=net.sourceforge.jdts.jdbc.Driver

I don't know if it was in copying it here that you introduced this error but if you have that on your machine then it would definitely spew errors.

-Fifi
 

warwound

Expert
Licensed User
Longtime User
Here's a short tutorial for anyone that wants to run the RDC server on a Ubuntu server and:
  • Auto start the RDC server when the (Ubuntu) server boots.
  • Be able to start / restart / stop the RDC server, without the need to reboot the Ubuntu server.

First take a look at this page: Running a Java program as a daemon in Ubuntu Linux, it contains all the info you need but to save you time i'll provide an 'almost ready to run' script...

My script assumes that you have the RDC files installed in your home directory in a sub-directory named b4a-rdc.
For example the complete path to the config.properties file might be:
/home/martin/b4a-rdc/config.properties


This is the script, it's named b4a-rdc.sh:

PHP:
#!/bin/sh
### BEGIN INIT INFO
# Provides:  b4a-rdc
# Required-Start:  $local_fs $remote_fs $network $syslog
# Required-Stop:  $local_fs $remote_fs $network $syslog
# Default-Start:  2 3 4 5
# Default-Stop:  0 1 6
# X-Interactive:  true
# Short-Description: Start/stop b4a-rdc server
### END INIT INFO
cd /home/<USERNAME>/b4a-rdc
case $1 in
  start)
  echo "Starting b4a-rdc..."
  if [ ! -f /tmp/b4a-rdc-pid ]; then
  nohup java -Xmx256m -cp .:/home/<USERNAME>/b4a-rdc/libs/*:/home/<USERNAME>/b4a-rdc/jdbc_driver/* anywheresoftware.b4a.remotedatabase.RemoteServer /home/<USERNAME>/b4a-rdc 2>> /dev/null >> /dev/null &
  echo $! > /tmp/b4a-rdc-pid
  echo "b4a-rdc started..."
  else
  echo "b4a-rdc is already running..."
  fi
  ;;
  stop)
  if [ -f /tmp/b4a-rdc-pid ]; then
  PID=$(cat /tmp/b4a-rdc-pid);
  echo "Stopping b4a-rdc..."
  kill $PID;
  echo "b4a-rdc stopped..."
  rm /tmp/b4a-rdc-pid
  else
  echo "b4a-rdc is not running..."
  fi
  ;;
  restart)
  if [ -f /tmp/b4a-rdc-pid ]; then
  PID=$(cat /tmp/b4a-rdc-pid);
  echo "Stopping b4a-rdc...";
  kill $PID;
  echo "b4a-rdc stopped...";
  rm /tmp/b4a-rdc-pid
  echo "Starting b4a-rdc..."
  nohup java -Xmx256m -cp .:/home/<USERNAME>/b4a-rdc/libs/*:/home/<USERNAME>/b4a-rdc/jdbc_driver/* anywheresoftware.b4a.remotedatabase.RemoteServer /home/<USERNAME>/b4a-rdc 2>> /dev/null >> /dev/null &
  echo $! > /tmp/b4a-rdc-pid
  echo "b4a-rdc started..."
  else
  echo "b4a-rdc is not running..."
  fi
  ;;
esac

So you need to:
  • Ensure that all RDC files are located in your user directory in a sub-directory named b4a-rdc.
  • Perform a search and replace in the script, replacing <USERNAME> with your username.
  • Save the script and upload it to the directory b4a-rdc.
    Be careful when saving the script, it must be saved with UNIX style line endings and NOT Windows style line endings.
    Do not copy/paste the above script included in this post - you're likely to get problems with line endings, instead download the attached script.
  • Move the script to the system init.d directory:
    PHP:
    sudo mv /home/<USERNAME>/b4a-rdc/b4a-rdc.sh /etc/init.d/b4a-rdc
  • Make the script executable:
    PHP:
    sudo chmod +x /etc/init.d/b4a-rdc
  • Add the script to your system startup scripts:
    PHP:
    sudo update-rc.d b4a-rdc defaults

You can now run these commands to control the RDC server:
  • sudo /etc/init.d/b4a-rdc start
  • sudo /etc/init.d/b4a-rdc stop
  • sudo /etc/init.d/b4a-rdc restart

And of course the RDC server will be started automatically when your server starts.

If you want to locate your RDC files elsewhere then the script will still work as long as you update all occurrences of /home/<USERNAME>/b4a-rdc with the full path to the directory containing the RDC files.
Do not modify /tmp/b4a-rdc-pid, if you modify this then you'll likely find that the script fails to start.

Martin.
 

Attachments

  • b4a-rdc.sh.zip
    606 bytes · Views: 589

Softflow Systems

Member
Licensed User
Longtime User
Hi Softflow,
I don't know if you've solved your issues since Tuesday. I just noticed that you had some typing errors in the config that you posted above.

Specifically the underlined line:

#DriverClass=com.mysql.jdbc.Driver
#JdbcUrl=jdbc:mysql://192.168.2.110/test?characterEncoding=utf8
#SQL Server
riverClass=net.sourceforge.jtds.jdbc.Drive
JdbcUrl=jdbc:jtds:sqlserver://198.68.2.105/testdata

Should read DriverClass=net.sourceforge.jdts.jdbc
or DriverClass=net.sourceforge.jdts.jdbc.Driver

I don't know if it was in copying it here that you introduced this error but if you have that on your machine then it would definitely spew errors.

-Fifi

Thanks FIFi
I made a mistake in my config file but still it is giving error as follows even though the jtds.1.3.jar is present.

Jan 21, 2014 10:06:07 PM com.mchange.v2.c3p0.DriverManagerDataSource ensureDrive
rLoaded
WARNING: Could not load driverClass net.sourceforge.jtds.jdbc.Drive
java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Drive
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:190)
at com.mchange.v2.c3p0.DriverManagerDataSource.ensureDriverLoaded(Driver
ManagerDataSource.java:112)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManag
erDataSource.java:144)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionRe
sourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResource
Pool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPe
ndingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourc
ePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.ru
n(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:648)
Jan 21, 2014 10:06:08 PM com.mchange.v2.resourcepool.BasicResourcePool$Scattered
AcquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@15b1
b88 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to
acquire a needed new resource, we failed to succeed more than the maximum number
of allowed acquisition attempts (30). Last acquisition attempt exception:
java.sql.SQLException: Network error IOException: Connection refused: connect
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:4
36)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManag
erDataSource.java:146)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnecti
on(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionRe
sourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResource
Pool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPe
ndingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourc
ePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.ru
n(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(Thre
adPoolAsynchronousRunner.java:648)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketI
mpl.java:79)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.ja
va:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocket
Impl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java
:182)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:579)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSoc
ket.java:288)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251)
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:3
31)
 

Softflow Systems

Member
Licensed User
Longtime User
It is a configuration issue. It is unable to find the jtds class.

How did the error message change? The previous errors you posted were about a connection issue, this means that the library was loaded correctly.
There was an error in config.properties that i changed which was wrong. But the problem still persists
 
Last edited:
Status
Not open for further replies.
Top