Android Question Help jdbc connection (RDC)

[dibesw]

HI everybody,
I use regularly RDC for connect to my access database (I use ucanaccess).
In some case (random) connection give this error (connection close):

I don't know why the connection is closed.
In this case I do a series of read and update of one access record.
I tried to do many test but is not resolved.
Who can help me?

Thanks in advance.

 

[Erel]

Add the following line to c3p0.properties file:

c3p0.testConnectionOnCheckout=true

Run the server and make sure that it shows testConnectionOnCheckout -> true when it starts.

 

[dibesw]

Hi Erel,
I tried it.
This is my c3p0.properties

c3p0.maxStatements=150
c3p0.maxIdleTime=1800
c3p0.idleConnectionTestPeriod=600
c3p0.checkoutTimeout=60000
c3p0.testConnectionOnCheckout=true

but the result is the same.

What else is?

 

[Erel]

You will need to send the request again.

 

[dibesw]

Please, I do not understand what do you mean?

 

[Erel]

The problem is most probably in the jdbc driver. So you need to manage the error.

If Job.Success is false then submit the request again.

 

[dibesw]

I tried it.
If I resubmit again the request, the message is the same perhaps because connection is closed.

 

[Erel]

Must you use MS Access as the database? Any other database engine will be faster and more reliable.

 

[dibesw]

Also access is fast.
I have access database and I work with it.
I use RDC (read, update, delete, join...) and works well except this case.
I saw that there are many c3p0 parameters but I have also trouble understanding well English.

 

[Erel]

Seems like the jdbc pool gets broken. This will not happen with SQLite or other real databases.

 

[MrKim]

Must you use MS Access as the database? Any other database engine will be faster and more reliable.

Erel, I know you, like a lot of people, are down on Access, but the reality is there are a LOT of Access databases out there that people would like to get at with wireless devices. When we started in the 90s there weren't a lot of choices at a reasonable price, and the reality is Access is still tough to beat in terms of ease of use and rapid development. I still write most of my queries in the Access query designer because it is simple, intuitive, and most important I don't have to worry about typos. I then paste the query in to SQL of B4A and modify as needed.

It also has the added benefit of a simple conversion to SQL Server.

We start all of our small customers on Access because all they have to do is compact the data once a week and back up the database file. Since it really just a file share it also runs quite nicely on a network storage drive for up to about 5 users - no server needed. It is extremely fast and very reliable for up to about 5-10 users and 300 megs of data, although we have had users with more if their network is extremely reliable.

If they get big enough we can use available free tools to move them to SQL and it only takes about 2 hours to do and they still run the same front end. The user sees no change. We have about 70 customers on Access and 15 on SQL and adding more every month. Since there is no tool that will convert our data from Access to MYSQL or SQLite converting 250 tables to one of these and modifying 300000 lines of code to the vagaries of a different engine becomes cost prohibitive.

So please cut us poor Access users a little slack. I have the RDC working beautifully with SQL but I need to get it running with Access as well.



Kim

 

[keirS]

One alternative is to see what happens if you use the JDBC-ODBC bridge driver. Just set up a System DSN for you Access DB in the ODBC Data Source Administrator. You don't need to copy a JAR file anywhere as the sun.jdbc.odbc.jdbcodbcdriver bridge driver is included in the Windows JRE. So your config properties should look like:

DriverClass=sun.jdbc.odbc.JdbcOdbcDriver
JdbcUrl=jdbc:odbc:MY_DSN

Where MY_DSN is the name of the DSN you created in the ODBC Data Source Administrator. If you are using 64bit windows then you need to run C:\Windows\SysWOW64\odbcad32.exe to set up your DSN and not the 64bit one from your control panel!

 

[MrKim]

One alternative is to see what happens if you use the JDBC-ODBC bridge driver. Just set up a System DSN for you Access DB in the ODBC Data Source Administrator. You don't need to copy a JAR file anywhere as the sun.jdbc.odbc.jdbcodbcdriver bridge driver is included in the Windows JRE. So your config properties should look like:

DriverClass=sun.jdbc.odbc.JdbcOdbcDriver
JdbcUrl=jdbc:odbc:MY_DSN

Where MY_DSN is the name of the DSN you created in the ODBC Data Source Administrator. If you are using 64bit windows then you need to run C:\Windows\SysWOW64\odbcad32.exe to set up your DSN and not the 64bit one from your control panel!

Thanks, I will give that a try. This doesn't need to particularly fast or efficient, just reliable.

 

[Erel]

So please cut us poor Access users a little slack. I have the RDC working beautifully with SQL but I need to get it running with Access as well.

I answered your question in the other thread.

 

[MrKim]

Well, I finally got the sun driver working ie:

DriverClass=sun.jdbc.odbc.JdbcOdbcDriver
JdbcUrl=jdbc:odbc:MY_DSN

But it throws an error I cannot figure out:

F:\Android\DatabaseConnection\RemoteDatabaseConnector-ACCESS>f:

F:\Android\DatabaseConnection\RemoteDatabaseConnector-ACCESS>cd F:\Android\Datab
aseConnection\RemoteDatabaseConnector-ACCESS

F:\Android\DatabaseConnection\RemoteDatabaseConnector-ACCESS>"C:\Program Files (
x86)\Java\jdk1.7.0_07\bin\java" -Xmx256m -cp .;libs\*;jdbc_driver\* anywheresoft
ware.b4a.remotedatabase.RemoteServer
B4A Remote Database Connecter (version 0.9)
loading: F:\Android\DatabaseConnection\RemoteDatabaseConnector-ACCESS\config.pro
perties
2014-08-13 14:14:31.916:INFO::jetty-7.4.2.v20110526
2014-08-13 14:14:31.941:INFO::started o.e.j.s.ServletContextHandler{/,null}
Aug 13, 2014 2:14:31 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Aug 13, 2014 2:14:32 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-08-13 14:14:32.139:INFO::Started SelectChannelConnector@0.0.0.0:17178 START
ING
Aug 13, 2014 2:14:36 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 -> 1hge148931m1v1ks1hxxvsg|17470d0, debugUnreturnedConnectionStackTraces ->
false, description -> null, driverClass -> sun.jdbc.odbc.JdbcOdbcDriver, factory
ClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken
-> 1hge148931m1v1ks1hxxvsg|17470d0, idleConnectionTestPeriod -> 600, initialPoo
lSize -> 3, jdbcUrl -> jdbc:odbc:PartsAndTimeForAccess, maxAdministrativeTaskTim
e -> 0, maxConnectionAge -> 0, maxIdleTime -> 180, maxIdleTimeExcessConnections
-> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, mi
nPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties ->
{}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnect
ionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTi
meout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
java.sql.SQLException: An SQLException was provoked by the following failure: ja
va.lang.UnsupportedOperationException
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:74)
        at com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPoole
dConnection.java:492)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.getParameterMetaDa
ta(NewProxyPreparedStatement.java:317)
        at anywheresoftware.b4a.remotedatabase.Servlet.createStatement(Servlet.j
ava:178)
        at anywheresoftware.b4a.remotedatabase.Servlet.executeQuery(Servlet.java
:140)
        at anywheresoftware.b4a.remotedatabase.Servlet.doGet(Servlet.java:78)
        at anywheresoftware.b4a.remotedatabase.Servlet.doPost(Servlet.java:52)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        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.content(HttpCo
nnection.java:1065)
        at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:823)
        at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:220)

        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:722)
Caused by: java.lang.UnsupportedOperationException
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.getParameterMetaData(JdbcOdbc
PreparedStatement.java:3468)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.getParameterMetaDa
ta(NewProxyPreparedStatement.java:303)
        ... 23 more

I pared my test query down to:

sql.GetEmployees=SELECT Emp_Num FROM Employees;

Still no luck.
Any help is appreciated.

By the way I gave up on the jackcess driver. I finally got it working and then found out they do not implement ANY Access file or record locking which means (and they say this on there web site) if you are writing records in a multi user environment using jackcess database corruption, eventually, is inevitable.

 

[Harris]

Seems like the jdbc pool gets broken. This will not happen with SQLite or other real databases.

Really people...

Must we drag this DOS legacy into 2014?

With all that Erel provides for us, should we ask him to further constrain his very limited time to fix a MS issue that has plagued the industry for many years with a RAM hogging, ill-conceived, complete abortion of a non-database record collector? Hells Bells!!! DBF, Paradox and DBISam where all available back then - all beat Access hands down (except for paradox with it's BDE which made it equally difficult)...

Rather than a problem, look upon it as an opportunity. Bring your clients into the modern age of computing. Even - go cloud. I, for one, will never write another desktop app again - with all of its' user and PC support burdens.

I don't wish to denigrate your concerns; rather offer some bias advice for your consideration. The quote above states the same.

Thanks