Need Servoy Developer to check the connection

[stephan]

I'm developing a solution and the repository_server, log_server (etc..) is behind the firewall.

The problem is that after 5 minutes of inactivity the firewall closes the socket connection.

In Servoy Developer that means that a jdbc connection object exists but it has lost the connection and the application (Servoy Developer) doesn't know it. Therefore when the applicatin tries to use the connection a SQLException occurs.

I have seen that Servoy Server has options to check for the validity of a connection before using it. I would need the same functionality for Servoy Developer.

Do you know if this can be done with Servoy Developer? How?

Thanks.

[sebster]

Go to the Servoy Admin web page, then to the DB Server section. You can then fill in the type of connection validation you want for your application. In you case you can try meta data validation or query validation. If you use query validation use a cheap query. There is an explanation on DB Servers page. (These features will be added to the preferences panel soon).

[stephan]

I thought that these web setting where valid only for the clients connected to the Server and not for the developer working into Servoy Developer.

Anyway I did what you suggested:
I opened the developer, checked in perferences that the check box "Start data and http service in this developer" is checked.
I restarted the application and in my browser went to http://127.0.0.1:8080/servoy-admin/dbservers. I chose to query validation from every connections and wrote the following queries:
for mysql: select 1
for oracle: select 1 from dual

Then I restarted the application, opened a solution and waited 10 minutes.

I test if it was working I tried to write a label in a form and ... error:

Cannot create label

Code: Select all

java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Connection reset

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Connection reset

STACKTRACE:

java.net.SocketException: Connection reset

   at java.net.SocketInputStream.read(Unknown Source)

   at java.io.BufferedInputStream.fill(Unknown Source)

   at java.io.BufferedInputStream.read1(Unknown Source)

   at java.io.BufferedInputStream.read(Unknown Source)

   at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1385)

   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1532)

   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1923)

   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)

   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)

   at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)

   at com.mysql.jdbc.Connection.execSQL(Connection.java:2233)

   at com.mysql.jdbc.Connection.execSQL(Connection.java:2193)

   at com.mysql.jdbc.Connection.execSQL(Connection.java:2174)

   at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:536)

   at com.servoy.j2db.persistence.datasource.k.setAutoCommit(Unknown Source)

   at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)

   at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)

   at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)

   at com.servoy.j2db.persistence.datasource.TransactionConnection.startTransaction(Unknown Source)

   at com.servoy.j2db.persistence.Repository.getSequence(Unknown Source)

   at com.servoy.j2db.persistence.Repository.a(Unknown Source)

   at com.servoy.j2db.persistence.Form.createNewGraphicalComponent(Unknown Source)

   at com.servoy.j2db.develop.c.ak.doIt(Unknown Source)

   at com.servoy.j2db.cmd.b$2.run(Unknown Source)

   at java.awt.event.InvocationEvent.dispatch(Unknown Source)

   at java.awt.EventQueue.dispatchEvent(Unknown Source)

   at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

   at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

   at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

   at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

   at java.awt.EventDispatchThread.run(Unknown Source)



** END NESTED EXCEPTION **


On the log this is what I see:

Code: Select all


java.sql.SQLException: no transaction in progress
java.sql.SQLException: no transaction in progress
        at com.servoy.j2db.persistence.datasource.TransactionConnection.rollback(Unknown Source)
        at com.servoy.j2db.util.Utils.rollback(Unknown Source)
        at com.servoy.j2db.persistence.Repository.getSequence(Unknown Source)
        at com.servoy.j2db.persistence.Repository.a(Unknown Source)
        at com.servoy.j2db.persistence.Form.createNewGraphicalComponent(Unknown Source)
        at com.servoy.j2db.develop.c.ak.doIt(Unknown Source)
        at com.servoy.j2db.cmd.b$2.run(Unknown Source)
        at java.awt.event.InvocationEvent.dispatch(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
passivateObject: java.sql.SQLException: No operations allowed after connection closed.

Connection was closed due to the following exception:

** BEGIN NESTED EXCEPTION **

java.sql.SQLException
MESSAGE: Communication link failure: java.net.SocketException, underlying cause: Connection reset

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Connection reset

STACKTRACE:

java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(Unknown Source)
        at java.io.BufferedInputStream.fill(Unknown Source)
        at java.io.BufferedInputStream.read1(Unknown Source)
        at java.io.BufferedInputStream.read(Unknown Source)
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1385)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1532)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1923)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
        at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1218)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2233)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2193)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2174)
        at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:536)
        at com.servoy.j2db.persistence.datasource.k.setAutoCommit(Unknown Source)
        at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)
        at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)
        at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)
        at com.servoy.j2db.persistence.datasource.TransactionConnection.startTransaction(Unknown Source)
        at com.servoy.j2db.persistence.Repository.getSequence(Unknown Source)
        at com.servoy.j2db.persistence.Repository.a(Unknown Source)
        at com.servoy.j2db.persistence.Form.createNewGraphicalComponent(Unknown Source)
        at com.servoy.j2db.develop.c.ak.doIt(Unknown Source)
        at com.servoy.j2db.cmd.b$2.run(Unknown Source)
        at java.awt.event.InvocationEvent.dispatch(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)


** END NESTED EXCEPTION **

... and it continues like that



[sebster]

When developing a Servoy Solution a single transaction is used until the solution is saved, and this transaction is kept open for a longer period of time.

The connection validation is used when getting connections from the connection pool, but this only applies to connections that are returned to the pool... In this case, since the transaction is still busy, that's not the case.

This is an issue that we will not be able to solve in Servoy. Basically what Servoy requires is that you can keep a transaction going while developing on the solution.

There are several solutions: 1) talk to the firewall administrators and ask if they can change the policy of just blasting connections if they haven't been exchanging data for a bit (which is quite a crude policy), 2) develop on your local machine and user export/import cycles to move the solution to the remote location, 3) leave the designer using ^L when you leave the computer, or make sure that you save your solution before you leave.

[stephan]

Thank you very much for this interesting explanation.