B4J Question jRDC2 config.properties

Alexander Stolte

Expert
Licensed User
Hello community, i have a problem, because i dont understand what i insert into 2 columns.

I have looked every tutorial, about it here, in the forum. The most user are using MySql, but i have a MS Sql Database. So, now to my Question.

In the file "config.properties" exist the column "DriverClass" and JdbcUrl"

DriverClass: I should specify the location where the driver is located? So it would look like this? : "C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\deu\jre8\sqljdbc42.jar"

JdbcUrl: and here the path where the jRDC.jar file located? Looks like: C:\Users\Administrator\Desktop\jRDC.jar

thanks for help. :)

My first post without translator jay :cool:
 

Alexander Stolte

Expert
Licensed User
And if i test in browser said the log:

B4X:
Mai 05, 2017 6:37:50 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFORMATION: 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 -> nteu0l9n1oc65dj172s2g|574caa3f, debugUnreturnedCon...
Mai 05, 2017 6:38:20 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNUNG: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4999afdb -- 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: Ungültige Portnummer: 1433Test.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1317)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
    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)
Mai 05, 2017 6:38:20 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNUNG: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@6121c9d6 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Mai 05, 2017 6:38:20 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNUNG: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5a1927dd -- 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: Ungültige Portnummer: 1433Test.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1317)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
    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)
Mai 05, 2017 6:38:20 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNUNG: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@6121c9d6 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Mai 05, 2017 6:38:20 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNUNG: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@203ddad6 -- 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: Ungültige Portnummer: 1433Test.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1317)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
    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)
Mai 05, 2017 6:38:20 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNUNG: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@6121c9d6 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
 
Upvote 0

Alexander Stolte

Expert
Licensed User
My configfile:

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

#DATABASE CONFIGURATION
DriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
#JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8
JdbcUrl=jdbc:sqlserver://localhost\SQLEXPRESS:1433/Test

User=sa
Password=mypasswort
#Java server port
ServerPort=17178

#example of MS SQL Server configuration:
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>

#example of postegres configuration:
#JdbcUrl=jdbc:postgresql://localhost/test
#DriverClass=org.postgresql.Driver

#SQL COMMANDS
#sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
#     id INTEGER PRIMARY KEY AUTO_INCREMENT,\
#     name CHAR(30) NOT NULL,\
#     image BLOB)
#sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)
#sql.select_animal=SELECT name, image, id FROM animals
sql.create_table=CREATE TABLE article (col1 numeric(10,4) ,col2 text);
sql.select=select * from article
sql.insert=INSERT INTO article VALUES(?, ?)
 
Upvote 0

Alexander Stolte

Expert
Licensed User
Log said:

B4X:
Waiting for debugger to connect...
Program started.
2017-05-05 20:14:26.437:INFO::main: Logging initialized @696ms
Mai 05, 2017 8:14:26 PM com.mchange.v2.log.MLog
INFORMATION: MLog clients using java 1.4+ standard logging.
Mai 05, 2017 8:14:26 PM com.mchange.v2.c3p0.C3P0Registry
INFORMATION: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2017-05-05 20:14:27.257:INFO:oejs.Server:main: jetty-9.3.z-SNAPSHOT
2017-05-05 20:14:27.316:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@185d8b6{/,file:///C:/Users/Administrator/Desktop/jRDC/Objects/www,AVAILABLE}
2017-05-05 20:14:27.320:INFO:oejs.AbstractNCSARequestLog:main: Opened C:\Users\Administrator\Desktop\jRDC\Objects\logs\b4j-2017_05_05.request.log
2017-05-05 20:14:27.409:INFO:oejs.ServerConnector:main: Started ServerConnector@76f2b07d{HTTP/1.1,[http/1.1]}{0.0.0.0:17178}
2017-05-05 20:14:27.410:INFO:oejs.Server:main: Started @1672ms
Emulated network latency: 100ms
jRDC is running (version = 2.1)
Mai 05, 2017 8:14:33 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFORMATION: 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 -> nteu0l9n1ofqsm51czssru|59690aa4, debugUnreturnedCo...

INFORMATION: 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 -> nteu0l9n1ofu4zt13f8rgb|7a46a697, debugUnreturnedCo...

and browser said:

B4X:
RemoteServer is running (05/05/2017 20:17:06)
Error fetching connection.

Either I'm too incapable or it's harder than I thought
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
i have come to think that it is an error on ConnectionPool

Do this test:

B4X:
Dim sql As SQL
    sql.Initialize2("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://localhost\sqlexpress:1433;databaseName=Test;integratedSecurity=false;","Sa","mypasswort")
   
'with your specific table information
    Log(sql.ExecQuerySingleResult("SELECT column1 FROM table WHERE column= 1"))

For me it was a hit, i was able to connect to the database directly and retrieve information.

@Erel could it be and issue with c3p0?

i havent tested with jtds
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Yes! copy it in B4J

For example on appstart
(its just for testing your JDBC URL )

Yes i have information on my database, i am not using a Test database but catalogosSat, that it is the one i usually work with.

B4X:
Sub AppStart (Args() As String)
    Dim sql As SQL
    sql.Initialize2("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://localhost\sqlexpress:1433;databaseName=catalogosSat;integratedSecurity=false;","Sa","mypasswort")
   
    'if everything is right you will see this log: 
    Log(sql.ExecQuerySingleResult("SELECT nombre FROM cat_clientes WHERE id_Cliente = 1"))
   
End Sub
 
Upvote 0

Alexander Stolte

Expert
Licensed User
I have a another question. In the config file are the SQL COMMANDS. How look it, if I want to query:
B4X:
SELECT column_1 FROM Table_1

B4X:
#SQL COMMANDS
#sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
#     id INTEGER PRIMARY KEY AUTO_INCREMENT,\
#     name CHAR(30) NOT NULL,\
#     image BLOB)
#sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)
#sql.select_animal=SELECT name, image, id FROM animals
sql.create_table=CREATE TABLE article (col1 numeric(10,4) ,col2 text);
sql.select=select * from article
sql.insert=INSERT INTO article VALUES(?, ?)

look it so:

B4X:
sql.select=select column_1 from table_1

Or do I have something to add? It is about the principle that I have understood this. :)
 
Upvote 0

EnriqueGonzalez

Well-Known Member
Licensed User
Sorry for the long delay.

But after a lot of tests i was able to connect!

use this JDBC URL:
B4X:
JdbcUrl=jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseName=TEST;portNumber=1433


Why the other JDBC URL work correctly in a direct connection but not in a pooling one is beyond me.
 
Upvote 0
Top