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

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

Thread Status:
Not open for further replies.
  1. Softflow Systems

    Softflow Systems Member Licensed User

    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.
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    The port in JdbcUrl is wrong. It should point to the SQL Server port. Probably 1433.
     
  3. Softflow Systems

    Softflow Systems Member Licensed User

    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.
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    DO you get the same error message? Also make sure that the SQL Server is configured to allow remote connections.
     
  5. Softflow Systems

    Softflow Systems Member Licensed User

    Remote connection is also allowed but the problem still persists.
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    Where is the db server located? On the same computer as RDC?

    Maybe their is a firewall somewhere that blocks the connection.
     
  7. Softflow Systems

    Softflow Systems Member Licensed User

    Db server is on the same computer as RDc and also the firewalls are opened, still the problem exists.
     
  8. Erel

    Erel Administrator Staff Member Licensed 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.
     
  9. Softflow Systems

    Softflow Systems Member Licensed 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 SelectChannelConnector@0.0.0.0: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)
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    What is this ip: 198.68.2.105 ?

    Is this the ip address of the computer that is running RDC and the db server? If yes then try to change this to 127.0.0.1
     
    incendio likes this.
  11. Softflow Systems

    Softflow Systems Member Licensed User

    Done as suggested but same error comes
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    Sorry but there is nothing more that I can suggest. RDC was tested with several MS SQL servers and it worked properly. It is probably some issue with the SQL Server configuration.
     
  13. Fifi Donkor

    Fifi Donkor Member Licensed 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
     
  14. warwound

    warwound Expert Licensed 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 [ ! -/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 +/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.
     

    Attached Files:

  15. Softflow Systems

    Softflow Systems Member Licensed User

    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)
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    If I remember correctly jtds requires Java 7. Make sure that you are referencing Java 7.
     
  17. Softflow Systems

    Softflow Systems Member Licensed User

    I am giving the reference of Java 7 directory in RunRlc.bat
     
  18. Erel

    Erel Administrator Staff Member Licensed 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.
     
  19. Softflow Systems

    Softflow Systems Member Licensed User

    There was an error in config.properties that i changed which was wrong. But the problem still persists
     
    Last edited: Jan 25, 2014
  20. Erel

    Erel Administrator Staff Member Licensed User

    You should check your configuration. According to the error message the driver could not be found:
    WARNING: Could not load driverClass net.sourceforge.jtds.jdbc.Drive
    java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Drive
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice