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:

Tom1s

Member
Licensed User
Longtime User
Check the values before you add them to cmd.Parameters.

You are right it is rounded version when putting it to cmd.parameters. I checked that value is ok when storing it to litesql but when reading it something is not right in the code I previously send.
 

lvdp

Member
Licensed User
Longtime User
I'm trying to connect to SQLserver (SQLEXPRESS) on my PC (using the SQLServer driver), but I get:
RemoteServer is running (Mon Aug 24 14:50:28 CEST 2015)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

When looking in the log I see:
Unknown Server host name '192.168.1.13\SQLEXPRESS'

I've put in config.properties:
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://192.168.1.13\\SQLEXPRESS/InfoSystem
User=****
Password=****
ServerPort=1433

and in jdbc_driver\conf\connection.properties.tmpl
driver=net.sourceforge.jtds.jdbc.Driver
url=jdbc:jtds:sqlserver://192.168.1.13\\SQLEXPRESS/InfoSystem
USER=****
PASSWORD=****
SERVERNAME=192.168.1.13\\SQLEXPRESS
PORTNUMBER=1433
DATABASENAME=InfoSystem
SERVERTYPE=1XA
EMULATION=true
TDS=8.0

I tried different server names, but none worked.
The 192.168.1.13 I found with ipconfig.
What server name should I use?
 

lvdp

Member
Licensed User
Longtime User
Thank you for the quick answer.

However: I also tried this already, and when I try again it gives the same error:
RemoteServer is running (Mon Aug 24 15:25:10 CEST 2015)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

This is the full log (I put in bold what I thought was most important):

C:\Basic4Android\Downloaded_Examples\RemoteDatabaseConnector>"C:\Program Files (
x86)\Java\jdk1.7.0_71\bin\java" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheresoft
ware.b4a.remotedatabase.RemoteServer
B4A Remote Database Connecter (version 0.9)
loading: C:\Basic4Android\Downloaded_Examples\RemoteDatabaseConnector\config.pro
perties
2015-08-24 15:24:43.749:INFO::jetty-7.4.2.v20110526
2015-08-24 15:24:43.780:INFO::started o.e.j.s.ServletContextHandler{/,null}
aug 24, 2015 3:24:43 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
aug 24, 2015 3:24:44 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]
2015-08-24 15:24:44.226:INFO::Started SelectChannelConnector@0.0.0.0:1433 STARTI
NG
aug 24, 2015 3:25:10 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 -> 1hgeby99bqlqqb71eq1dgx|1711970, debugUnreturnedConnectionStackTraces -> f
alse, description -> null, driverClass -> net.sourceforge.jtds.jdbc.Driver, fact
oryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityTo
ken -> 1hgeby99bqlqqb71eq1dgx|1711970, idleConnectionTestPeriod -> 600, initialP
oolSize -> 3, jdbcUrl -> jdbc:jtds:sqlserver://127.0.0.1\SQLEXPRESS/InfoSystem,
maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxI
dleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxState
mentsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQ
uery -> null, properties -> {user=******, password=******}, propertyCycle -> 0,
statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, te
stConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 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:745)
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@470524 -- 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)
... 21 more
aug 24, 2015 3:25:40 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredA
cquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@18db
76b -- 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: Unknown server host name '127.0.0.1\SQLEXPRESS'.
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:4
27)
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.UnknownHostException: 127.0.0.1\SQLEXPRESS
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java
:178)
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)
... 10 more

aug 24, 2015 3:25:40 PM com.mchange.v2.resourcepool.BasicResourcePool forceKillA
cquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicR
esourcePool@470524 is interrupting all Threads waiting on a resource to check ou
t. Will try again in response to new client requests.
aug 24, 2015 3:25:40 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredA
cquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4505
63 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to a
cquire 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: Unknown server host name '127.0.0.1\SQLEXPRESS'.

at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:4
27)
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.UnknownHostException: 127.0.0.1\SQLEXPRESS
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java
:178)
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)
... 10 more

aug 24, 2015 3:25:40 PM com.mchange.v2.resourcepool.BasicResourcePool forceKillA
cquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicR
esourcePool@470524 is interrupting all Threads waiting on a resource to check ou
t. Will try again in response to new client requests.
aug 24, 2015 3:25:40 PM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredA
cquireTask run
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1f27
f74 -- 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: Unknown server host name '127.0.0.1\SQLEXPRESS'.
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:4
27)
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.UnknownHostException: 127.0.0.1\SQLEXPRESS
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java
:178)
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)
... 10 more

aug 24, 2015 3:25:40 PM com.mchange.v2.resourcepool.BasicResourcePool forceKillA
cquires
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicR
esourcePool@470524 is interrupting all Threads waiting on a resource to check ou
t. Will try again in response to new client requests.

Any further suggestions (I put the Firewall off) ?
Thank you...
 
Last edited:

lvdp

Member
Licensed User
Longtime User
By the way: I was trying with SQLEXPRESS, but finally I'd like to use Microsoft Azure as cloud database.
Anyone knows how to do this (and were to put the server code) ?
 

lvdp

Member
Licensed User
Longtime User
Thank you for the excellent support!
I now get "Connection successful" with these settings:

DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://127.0.0.1:1433;instance=SQLEXPRESS;DatabaseName=InfoSystem
 

John Decowski

Member
Licensed User
Longtime User
Just curious does this system implement any safe guards against sql injection type hacking "ie . Max request lengths or illegal characters?
Just curious in regards to security.
 

John Decowski

Member
Licensed User
Longtime User
Ok i see that but... I was wanting to make sure it wasnt possible to inject sql code into the argument variable passed between console and app. "?" Portion?
Sorry didn't explain it right
 

jsanchezc

Member
Licensed User
Longtime User
By the way: I was trying with SQLEXPRESS, but finally I'd like to use Microsoft Azure as cloud database.
Anyone knows how to do this (and were to put the server code) ?

You can use Sql Server Management Studio.
To connect you must use IP but at SSManagement Studio is this format:
Server= 127.0.0.1,1433
User: (indicate sql server username)
Password: xxxxxxxx

Notice: If you indicate port number you don't need to indicate instance name.
Be sure port is 1433. This is standard port for SQL but sometimes other number is configured, also SQL allow dynamic ports.
If you can connect by app, then you will connect by SSManager Studio.

Each version of SQL Server Management Studio allow to admin same version of SQL Server and previuos versions.
There are free versions you can install on your PC.
You can manage Tables, Views, Stored Procedures, User Functions.....

See this link:
https://azure.microsoft.com/es-es/documentation/articles/sql-database-manage-azure-ssms/

hope this help.
 

lvdp

Member
Licensed User
Longtime User
@jsanchezc

With the info you gave me, I got access to my SQL Server database on Azure from SQL Server Management Studio.
I have a database Samagis_db with a table Messages, in whcich I put 2 records.
I created a user dbacces with a pasword that should have read access (exec sp_addrolemember 'db_datareader', 'dbaccess'; )
I added my local IP address in the Azure firewall.

Next I'm trying to use this with the RDC connector, at first located on my local PC (in the future it should also reside on Azure).
I found somewhere I should add ";ssl=require" in the JdbcUrl, so in the config.properties file I wrote:

B4X:
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://mvr8bm4spa.database.windows.net:1433/Samagis_db;ssl=require
User=dbaccess
Password=*******
ServerPort=1433
Debug=true
#commands
sql.select_Messages=SELECT [ID], [Message] FROM [Samagis_db].[dbo].[Messages] ORDER BY TimeCreated
sql.select_Message=SELECT [ID], [Message] FROM [Samagis_db].[dbo].[Messages] WHERE ID = ?

Then I start RunRLC.bat and try http://127.0.0.1:1433/?method=test
But I get:

RemoteServer is running (Sat Aug 29 13:50:01 CEST 2015)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

Any suggestions ?

This is the log:

C:\Basic4Android\Downloaded_Examples\RemoteDatabaseConnector>"C:\Program Files (x86)\Java\jdk1.7.0_71\bin\java" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheresoftware.b4a.remotedatabase.RemoteServerB4A Remote Database Connecter (version 0.9)
loading: C:\Basic4Android\Downloaded_Examples\RemoteDatabaseConnector\config.properties
2015-08-29 13:49:33.729:INFO::jetty-7.4.2.v20110526
2015-08-29 13:49:33.760:INFO::started o.e.j.s.ServletContextHandler{/,null}
aug 29, 2015 1:49:33 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
aug 29, 2015 1:49:34 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]
2015-08-29 13:49:34.168:INFO::Started SelectChannelConnector@0.0.0.0:1433 STARTING
aug 29, 2015 1:50:01 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceN
ame -> 1hgeby99bxnjlrjikqtbz|a1e7ad, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> net.sourceforge.jtds.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgeby99bxnjlrjikqtbz|a1e7ad, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:jtds:sqlserver://mvr8bm4spa.database.windows.net:1433/
Samagis_db;ssl=require, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 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.checkoutPooledConnection(C3P0PooledConnectionPool.java:687)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.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(ContextHandler.java:937)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:871)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java: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:411)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:535)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:40)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:529)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@1191056 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1416)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682)
... 21 more
 

galimpic

Member
Licensed User
Longtime User
Can server commands from the config.properties file be executed through a web browser, or is just "method=test" parameter available?
 

galimpic

Member
Licensed User
Longtime User
If you only released the source code for this server, I am sure it would be easy to make changes and improvements... I know you haven't planned it, I'm just saying it would be the easiest way for us who asked for small changes (eg. encoded instead of plain communication) to just work on existing code instead of building it from scratch after reading about Jetty, c3po etc.
 
Last edited:

chrisinky

Member
Licensed User
Longtime User
Assuming I'm overlooking something... I'm trying to run a SQL statement similar to this:
Select * from table where field like 'x%'

How would I make this work? In my config file I have the "x" replaced with the ? and that doesn't work, I've put the ? after LIKE and have the program send "'x%' but that doesn't work either.
Thanks!
Chris
 
Status
Not open for further replies.
Top