Android Question B4J jRDC Server runs and connects to web browser, but doesn't attach to Microsoft SQL Server DB

rgarnett1955 · May 14, 2020

Hi People,

I am trying to get jRDC to work with my Microsoft SQL Server Express Data base. The database works fine with Qt C++ via a machine DNS, but I can't get it to work with jRDC. I want to get jRDC running so I can use B4X otherwise I'll have to do everything in Qt.

I based my code on Erel's examples and tutorial with mods to the config file to accommodate MS SQL Server instead of my SQL. I was going to use mySQL instead of MS SQL, but the mySQL installer won't run on my main Windows 10 machine, although it would run on my Windows 10 laptop. I also tries moriaDB, but found it was far too slow for my application where I get data in descending order top 5 values. MS SQL Server on the other hand is extremely fast both locally and on a remote machine.

My MS SQL Server is configured for TCP:

The connection name of the server is shown by the MS SQL Server Studio opening dialog below:
[Computer running SQL Server & jRDC2 B4J server]

I can connect to this server using SQL Management Studio running on a remote machine, with SQL Server security credentials:
[Remote computer using TCP port 1433]

I can run queries against the data successfully on the remote machine:

On the server machine I have the following config file:

When I run my browser on the server machine I get:

So it seems that the jRDC server is running, but not connecting to the MS SQL Server DB.

The jRDC driver file I am using is jtds-1.3.1.jar which I have located in my main libraries directory.

Sysinternals TCP View shows two instances of the port 17178 connection one for TCP and one for TCPV6:

I am pretty sure that it is my connection string that is the problem:

Config File Connection strings:

#example of MS SQL Server configuration:
DriverClass=net.sourceforge.jtds.jdbc.Driver
JdbcUrl=jdbc:jtds:sqlserver://ROBSDESKTOP02:1433/MSSQLSERV_01;instance=SQLEXPRESS
User=rjg
Password=Toyota_b1923
#Java server port
ServerPort=17178

I had a look at the help and FAQ for the jRDC driver; these are included in the download, however there were no examples for using MS SQL Server.

The error log I get is shown below:

Error log:

Waiting for debugger to connect...

[My comments 1: First block is when the jRDC server is started.]

Program started.
2020-05-14 12:28:49.823:INFO::JavaFX Application Thread: Logging initialized @881ms to org.eclipse.jetty.util.log.StdErrLog
May 14, 2020 12:28:49 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
May 14, 2020 12:28:50 PM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2020-05-14 12:28:50.995:INFO:oejs.Server:JavaFX Application Thread: jetty-9.4.z-SNAPSHOT; built: 2018-05-03T15:56:21.710Z; git: daa59876e6f384329b122929e70a80934569428c; jvm 1.8.0_131-b11
2020-05-14 12:28:51.025:INFO:oejs.session:JavaFX Application Thread: DefaultSessionIdManager workerName=node0
2020-05-14 12:28:51.026:INFO:oejs.session:JavaFX Application Thread: No SessionScavenger set, using defaults
2020-05-14 12:28:51.027:INFO:oejs.session:JavaFX Application Thread: node0 Scavenging every 660000ms
2020-05-14 12:28:51.043:INFO:oejsh.ContextHandler:JavaFX Application Thread: Started o.e.j.s.ServletContextHandler@a57b23{/,file:///G:/B4J/TestMsSql/msSqlTest/Objects/www,AVAILABLE}
2020-05-14 12:28:51.047:INFO:oejs.AbstractNCSARequestLog:JavaFX Application Thread: Opened G:\B4J\TestMsSql\msSqlTest\Objects\logs\b4j-2020_05_14.request.log
2020-05-14 12:28:51.061:INFO:oejs.AbstractConnector:JavaFX Application Thread: Started ServerConnector@f40227{HTTP/1.1,[http/1.1]}{0.0.0.0:17178}
2020-05-14 12:28:51.061:INFO:oejs.Server:JavaFX Application Thread: Started @2120ms
Emulated network latency: 100ms
jRDC is running (version = 2.22)

[END OF Comments 1]


[My Comments 2: Response when Browser is connected to local host port 17178]

May 14, 2020 12:34:51 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
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, contextClassLoaderSource -> caller, dataSourceName -> 1hge16aaa4pxri112j2ilj|129034d, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> net.sourceforge.jtds.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge16aaa4pxri112j2ilj|129034d, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:jtds:sqlserver://ROBSDESKTOP02:1433/MSSQLSERV_01;instance=SQLEXPRESS, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, m...
May 14, 2020 12:35:53 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4ceed9 -- 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: Cannot open database "MSSQLSERV_01" requested by the login. The login failed.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
May 14, 2020 12:35:53 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@3a756c is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
May 14, 2020 12:35:54 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1315034 -- 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: Cannot open database "MSSQLSERV_01" requested by the login. The login failed.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
May 14, 2020 12:35:54 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@3a756c is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
May 14, 2020 12:35:54 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1e3bdb0 -- 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: Cannot open database "MSSQLSERV_01" requested by the login. The login failed.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
May 14, 2020 12:35:54 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@3a756c is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

Does anyone have any ideas or working examples of MS SQL Server connections using jRDC2?

Best regards

Rob

aeric · May 14, 2020

I assume your database name 'main', try:

B4X:

#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>
JdbcUrl=jdbc:jtds:sqlserver://ROBDESKTOP02:1433/SQLEXPRESS/main

OliverA · May 14, 2020

Switch to Microsoft’s JDBC driver. JTDS is not maintained anymore. On Non-Android platforms, there is no reason not to use the newer drivers. JTDS 1.3.1 was published in 2013.

OliverA · May 14, 2020

More constructive. The latest MS SQL JDBC Driver is 8.2.2 (see https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15 . Click on the Download JDBC driver link). The zip file will contain JRE8, JRE11 and JRE13 drivers. Pick the appropriate one.

Documentation for building the URL can be found here: https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15
Note that you should either a) specify the port number or b) use the instance name to connect to your server. So if you know the port of your instance you could try

B4X:

'MS JDBC URL!!!
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02:1433;databaseName=MSSQLSERV_01

if you don't know the port of your instance, then try

B4X:

'MS JDBC URL!!!
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02;instanceName=SQLEXPRESS;databaseName=MSSQLSERV_01

Depending on SSL configuration, other parameters may need to be furnished.

Note: Nowhere in the screenshots above do you show the DB names set up, and therefore we don't even know if MSSQLSERV_01 is an actual name of a DB. Depending on the rights of the user that logs into the system, the URL could also be as simple as

B4X:

'MS JDBC URL!!!
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02

This Url will connect to the default port (1433) and to the default instance. The user than has access to any and all DB configurations under that instance as is granted to that user.

LucaMs · May 14, 2020

Warning: you have published user and pw of your server.

rgarnett1955 · May 14, 2020

LucaMs said:
Warning: you have published user and pw of your server.

Thank's

I know that, but there is nothing in the DB at the moment, it's only for testing and I will use a 64 character PW when I get it working and have real data.

aeric said:
I assume your database name 'main', try:

B4X:

#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database> JdbcUrl=jdbc:jtds:sqlserver://ROBDESKTOP02:1433/SQLEXPRESS/main

Best regards

Rob

aeric said:
I assume your database name 'main', try:

B4X:

#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database> JdbcUrl=jdbc:jtds:sqlserver://ROBDESKTOP02:1433/SQLEXPRESS/main

Thanks Aeric,

I tried your suggestion with my actual DB name, but it still didn't work.

BR RJG

rgarnett1955 · May 14, 2020

OliverA said:
Switch to Microsoft’s JDBC driver. JTDS is not maintained anymore. On Non-Android platforms, there is no reason not to use the newer drivers. JTDS 1.3.1 was published in 2013.

Hi,

I wondered about that.

I will give the Microsoft one a try. I had downloaded it, but Erel had used the jRDC one so I thought I would try that first.

BR RJG

rgarnett1955 · May 14, 2020

OliverA said:
More constructive. The latest MS SQL JDBC Driver is 8.2.2 (see https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15 . Click on the Download JDBC driver link). The zip file will contain JRE8, JRE11 and JRE13 drivers. Pick the appropriate one.

Documentation for building the URL can be found here: https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15
Note that you should either a) specify the port number or b) use the instance name to connect to your server. So if you know the port of your instance you could try

B4X:

'MS JDBC URL!!! JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02:1433;databaseName=MSSQLSERV_01

if you don't know the port of your instance, then try

B4X:

'MS JDBC URL!!! JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02;instanceName=SQLEXPRESS;databaseName=MSSQLSERV_01

Depending on SSL configuration, other parameters may need to be furnished.

Note: Nowhere in the screenshots above do you show the DB names set up, and therefore we don't even know if MSSQLSERV_01 is an actual name of a DB. Depending on the rights of the user that logs into the system, the URL could also be as simple as

B4X:

'MS JDBC URL!!! JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02

This Url will connect to the default port (1433) and to the default instance. The user than has access to any and all DB configurations under that instance as is granted to that user.

The problem I have with all of this is sorting out what all the names mean and which one you should use when.

You say that nowhere in the screen shots do I show the DB names, but I want to attach to a linked database that doesn't show up as a db name. I have attached a screen shot of the SQL Management Studio of the database tree. tblRealtimeData is the table I am running my queries against.

Does this mean I should use "//ROBSDESKTOP/SQLEXPRESS/SERVER OBJECTS/main/tables/tblRealtimeData"?

To talk to the DB using SQL Management studio I use the name I put in the screen shots and can then run queries against my tblRealtimeData without any problems.

The server service instance running on the database server PC is called MSSQLSERV_01, but I don't understand whether this name means anything to TCP connections. I suspect it doesn't.

I'm sure there is some twisted Microsoft logic in all of this, but it escapes me I'm afraid.

In fact the database table I really want to talk to is in the sqLite DB attached as a linked DB to MS SQL Server shown in the above screenshot. I am using MS SQL Server as the intermediate DB so I can access the sqLite data without trying to run queries against the sqLite file over the network. I can do this in QT really easily, and it's really fast and efficient. I just want to do the same in B4X because I prefer this to QT for doing Android Apps.

BR RJG

aeric · May 14, 2020

It seems PSMDB_WIN_SVR is a linked server and not a local server.

rgarnett1955 · May 15, 2020

aeric said:
It seems PSMDB_WIN_SVR is a linked server and not a local server.

Yes

That is correct.

In Qt I connect from a remote pc using TCP the following:

Qt Database Connection fro remote server:

/*------------------------------------------------------------------------------------------------------*/
bool MainWindow::checkIfFileExistsAndMakeConns(QSqlDatabase &db, QString DNS, QString connName)
{
    QFile file;
    QSqlError errSQL;
    QString errorString;
    QString connectionString = "Driver={SQL Server Native Client 11.0};";
    connectionString.append("Server=ROBSDESKTOP02\\SQLEXPRESS;");
    connectionString.append("Uid=rjg;");           // User
    connectionString.append("Pwd=Toyota_b1923;");  // Pass

    db = QSqlDatabase::addDatabase("QODBC", connName);
    db.setDatabaseName(connectionString);

    if(!db.open())
    {
         errSQL = MainWindow::dbPMon.lastError();
         errorString = errSQL.text();
         errorString = "Could not open database file connection to: " + DNS;
         errorString = "Could not find database file: " + DNS;
         /* TODO */

        QSqlDatabase::removeDatabase(connName);
        return DB_ERROR;
    }

    return DB_OK;
}

Of course I am not using the jRDC driver with Qt, but the SQL Server client. You will note that I do not specify the linked database PSMDB_WIN_SVR, however I can query this db using the following query within QT:

QT QUERY OF PSMDB_WIN_SVR OVER LAN:

        /* Query the pMon (2 second db) - last five records */
        QSqlQuery qryPMon(MainWindow::dbPMon);

        qryStr = "SELECT * \
                 FROM OPENQUERY(PSMDB_WIN_SVR, \
                 'SELECT \
                 julianDateUTC, \
                 frequency, \
                 Vrms, \
                 P1, \
                 P2, \
                 P3, \
                 ProcessorTemp, \
                 mainBatteryVolts, \
                 Quality \
                 FROM \
                 [main].[tblRealTimeData] \
                 ORDER  BY julianDateUTC DESC \
                 LIMIT 5;')";

        qryStr = qryStr.simplified();

        if (!qryPMon.prepare(qryStr))
        {
            /* Notify Error TODO */
            qDebug() << "getData DB Prep error";
            return;
        }

        if (!qryPMon.exec())
        {
                return;
        }

So it seems that you don't have to specify the linked db in the connection you can do it in the query. Also interestingly you can use the query keyword "LIMIT" which is not an

SQL Server sql keyword. SQL Server uses the keyword "TOP"

I will have a go at specifying the PSMDB_WIN_SVR database in the config file of the B4J server app and see if that works.

BR Rob

OliverA · May 15, 2020

Have you tried to connect with this?

B4X:

'MS JDBC URL!!!
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02

using the MS JDBC driver?

aeric · May 15, 2020

If cannot, try use any database name like pMonNetworkServer or WeatherMation to replace master

B4X:

#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>
JdbcUrl=jdbc:jtds:sqlserver://ROBDESKTOP02/SQLEXPRESS/master

rgarnett1955 · May 15, 2020

Hi People,

I have tried the following url connection strings, but none of them work:

B4X:

JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02:1433;databaseName=pMonNetworkServer
JdbcUrl=jdbc:sqlserver://localhost:1433;databaseName=pMonNetworkServer
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02\\SQLEXPRESS
JdbcUrl=jdbc:sqlserver://localhost:1433;
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02:1433;
JdbcUrl=jdbc:sqlserver://ROBSDESKTOP02:1433;databaseName=PSMDB_WIN_SRV

I always get the same result in the browser which is
Remote Server Running ...
Error fetching connection.

The B4J log is

ERROR LOG:

Waiting for debugger to connect...
Program started.
2020-05-15 11:08:04.603:INFO::main: Logging initialized @559ms to org.eclipse.jetty.util.log.StdErrLog
May 15, 2020 11:08:04 AM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
May 15, 2020 11:08:05 AM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2020-05-15 11:08:05.876:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT; built: 2018-05-03T15:56:21.710Z; git: daa59876e6f384329b122929e70a80934569428c; jvm 1.8.0_131-b11
2020-05-15 11:08:05.907:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2020-05-15 11:08:05.907:INFO:oejs.session:main: No SessionScavenger set, using defaults
2020-05-15 11:08:05.909:INFO:oejs.session:main: node0 Scavenging every 660000ms
2020-05-15 11:08:05.913:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@950e31{/,file:///G:/B4J/jRDC/Objects/www,AVAILABLE}
2020-05-15 11:08:05.918:INFO:oejs.AbstractNCSARequestLog:main: Opened G:\B4J\jRDC\Objects\logs\b4j-2020_05_15.request.log
2020-05-15 11:08:05.932:INFO:oejs.AbstractConnector:main: Started ServerConnector@607a4f{HTTP/1.1,[http/1.1]}{0.0.0.0:17178}
2020-05-15 11:08:05.933:INFO:oejs.Server:main: Started @1889ms
Emulated network latency: 100ms
jRDC is running (version = 2.22)
May 15, 2020 11:08:13 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
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, contextClassLoaderSource -> caller, dataSourceName -> 1hge16aaa62hrlx4guykc|f67ac7, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.microsoft.sqlserver.jdbc.SQLServerDriver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge16aaa62hrlx4guykc|f67ac7, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:sqlserver://ROBSDESKTOP02\\SQLEXPRESS, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, max...
May 15, 2020 11:16:13 AM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1303b51 -- 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:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host ROBSDESKTOP02, named instance \SQLEXPRESS failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434.  For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(SQLServerConnection.java:6068)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(SQLServerConnection.java:2457)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2200)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2067)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1204)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:825)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
May 15, 2020 11:16:13 AM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6e1e71 -- 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:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host ROBSDESKTOP02, named instance \SQLEXPRESS failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434.  For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.

I know my DB is running OK and that I am using the correct password because my QT Application works. I noticed that in the error log it says to:

check that no firewall is blocking UDP traffic to port 1434

I don't know why 1434 and not 1433 which is the default for MS SQL Server, but I had the firewall disabled anyway.

I can run my QT app on the server pc or a remote pc witout problems using the same connect string.

There is something that is required to get the jRDC ms sql driver to work, but I don't know what it is.

The connection string I am using for my Qt app is:

"Server=ROBSDESKTOP02\\SQLEXPRESS;" Why doesn't this work with the jRDC Driver?

I have looked at the Microsoft Website on this driver and they say:

Microsoft jRDC driver Using Page

Microsoft jdbc driver connection string:

String connectionUrl = "jdbc:sqlserver://localhost:1433;" + 
   "databaseName=AdventureWorks;user=MyUserName;password=*****;"; 
Connection con = DriverManager.getConnection(connectionUrl);

Help!!!

BR RJG

aeric · May 15, 2020

aeric said:
If cannot, try use any database name like pMonNetworkServer or WeatherMation to replace master

B4X:

#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database> JdbcUrl=jdbc:jtds:sqlserver://ROBDESKTOP02/SQLEXPRESS/master

Take note that the sample connection string I provided has no semicolon. Please follow the format with / as in the example.

Edit: I tested in a linked server environment. I used "master" db and other db in JdbcUrl and successfully query database table in other linked server.

OliverA · May 15, 2020

You still have not tried the simple one I suggested. It has no port number and no instance name.

rgarnett1955 · May 15, 2020

Hi All,

I fixed the problem:

I assumed I was using a server instance that I wasn't. So I was looking at the wrong one.
The server instance that was running was called SQL Server (SQLEXPRESS)
This instance did not have its port set to 1433 it was blank.
QT uses the native driver and so didn't care about the port number for some reason.

To Fix the problem I did the following:

Set the SQLEXPRESS server instance Port to 1433
Set my connection string as per below:

MS SQL Connection String:

    pool.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver", _
                    "jdbc:sqlserver://localhost\sqlexpress", _
                    "rjg", _
                    "Toyota_b1923")

What a knucklehead I was.

BR RJG

Android Question B4J jRDC Server runs and connects to web browser, but doesn't attach to Microsoft SQL Server DB

rgarnett1955

Active Member

Attachments

aeric

Expert

OliverA

Expert

OliverA

Expert

LucaMs

Expert

rgarnett1955

Active Member

rgarnett1955

Active Member

rgarnett1955

Active Member

Attachments

aeric

Expert

rgarnett1955

Active Member

OliverA

Expert

aeric

Expert

rgarnett1955

Active Member

aeric

Expert

OliverA

Expert

rgarnett1955

Active Member

Similar Threads