java.sql.SQLException: The connection is closed: The connection is closed

java.sql.SQLException: The connection is closed: The connection is closed

  

Hi,

I am trying to implement a way to connect to external databases and switch between them during runtime. Specifically, I need to access the system tables from other servers which have the same schema (although different schema name). My goal is to be able to let the user enter the connection string during runtime and be able to fetch the system tables from that server. I followed the steps mentioned in the How-Tos about connecting to external databases. 


As I understand, I need to define one connection in Service Center first, then create an extension in which I need to import the required tables, configure in Service Center the connection my extension needs to use, and then use PlatformRuntime_API action DatabaseConnection_SetConnectionStringForSession to set the new connection string during runtime.


Suppose I defined a connection "Connection A" in Service Center, which has the following connection string: 

"jdbc:oracle:thin:Username/Password@10.0.0.1:1521/myService"


When I try to view the system tables in my application from the extensions I created, it is showing the data fine. But, when I use DatabaseConnection_SetConnectionStringForSession to change the connection string to another "Connection B": 

"jdbc:oracle:thin:Username2/Password2@10.0.0.2:1521/myService2"

and then refresh the aggregate and table, the following error is thrown:


The connection is closed: The connection is closed

java.sql.SQLException: The connection is closed: The connection is closed
at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:473)
at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:457)
at oracle.ucp.jdbc.proxy.JDBCConnectionProxyFactory.invoke(JDBCConnectionProxyFactory.java:285)
at oracle.ucp.jdbc.proxy.ConnectionProxyFactory.invoke(ConnectionProxyFactory.java:48)...


The full error stack is attached below.


For both Connection A and Connection B I tried defining connections in Service Center and configure them in the extension's Operation setting. I can view the data of whichever connection I set as default in the extension's settings, but the error pops up whenever I try to switch between them.

Also, I made sure that all the three conditions stated in the description of the API are satisfied.


What might be the problem here?


The platform version is 10.0.604.0, and this is an on-prem installation. I would be happy to provide any other details you might require.


Thank you

Hi Abhisek,

Where are you trying to do the switch?  According to the documentation extract below, it is not possible to switch the database in the same request . Take a look into this thread. Maybe this could be your issue here.

==================

Switches a Database Connection from one database to another at runtime and in the current Session.There are some conditions to which you have to pay attention to when using this action: - If there is a single query that uses the Database Connection with the old database before this action is executed, the switch to the new database will not happen until the end of the current request. Only in a next request the database switch becomes effective. - The Connection String must connect to a database with the same type (e.g. Oracle, SQL Server, MySQL) as the one configured in Service Center for the Database Connection. - Your license must include the Platform Extensibility APIs feature.

===========


https://www.outsystems.com/forums/discussion/15993/servicecenter-database-connection-programmatically-switch-database-connection/

Thank you for the reply Fábio


I went through that thread already. I have the switch in a simple action invoked by a button. After the switch I am redirecting the user to a different page where they can view the entities from the extension.


And yes, as I mentioned, I am making sure that the switch is not happening in the same request. All the three conditions are being satisfied.

I also have the same problem. Does anyone know what to do ?