com.sap.engine.services.dbpool.exceptions.BaseSQLException: Connection is invalid

[mohamed_shabir]

Hi,

This is a strange issue. We are using EP 7.01.  In webdynpro Java, I am trying to execute some query from database. I am creating a databse connection via datasource. I set the connection in global variable, so same connection can be reused without recreating it. The code given below is how i establish DS connection.

//Code in component controller

InitialContext ctx = new InitialContext();

DataSource dsc = (DataSource) ctx.lookup("jdbc/DS_AC");

conn = dsc.getConnection(); // conn is set as global Conection Variable  in comp controller

Statement stmt = conn.createStatement();

When i try this in init(), and executes the query with stament, i get proper result. So no issue with datasource.  However, if on button action from view, call a method in Component controller, and in that method try to use the connection object  as given below, i get base SQL exception.

Statement stmt = conn.createStatement();

error from log :

Exception com.sap.engine.services.dbpool.exceptions.BaseSQLException: Connection is invalid.

at com.sap.engine.services.dbpool.cci.ConnectionHandle.checkIfInvalid(ConnectionHandle.java:525)

at com.sap.engine.services.dbpool.cci.ConnectionHandle.checkIfValid(ConnectionHandle.java:511)

at com.sap.engine.services.dbpool.cci.ConnectionHandle.createStatement(ConnectionHandle.java:63)

This works if i use direct connection (user name and password directly) even on button action from view, But if i use Datasource in place of direct connection, it doesnt work. Datasource connection works from init, but when the control goes to view and comes back via action, the connection set in component controller global variable gets invalid. The connection is not closed, and still active But not valid.

I increased "KeepAliveTimout" parameter of Databse, but still not helping. Any suggestion on solving this would  greatly help.

Thanks in advance

[Former Member]

Dear Shabir,

Please follow below document .

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d065610f-2cb8-2910-11b2-e3ecd2c3c...

Thanks,

Patralekha

[mohamed_shabir]

Hello Patralekha

This is a generic document on how to access database via Data source.  I am more or less doing the same thing. I am not facing problem for establishing connection or displaying result. It works first when i use the connection to initially load the DB connection and display its records. I am facing problem in using the same connection second time after the control goes to view controller by displaying view and comes back to component controller via method call.

Please note, to check whether the connection is being invalidated anywhere, i replace the connection with direct connection and it worked first and second time. But if i use the connection from Data source, it didn't work second time.

An suggestion would help

[Former Member]

Try this.

1)Create a method in ComponetController - mGetConnection() where wrote the code for Connections.

2)Call the method in the event of display button of view by using  wdThis.wdGet<>CompController().mGetConnection();

So every time the connection code will get called.

Check if it works.

Thanks,

Patralekha

[mohamed_shabir]

This is like creating new connection. If i create a new connection every time i run query, it works. if i instead re use existing connection set in global variable, it doesn't work. The strange part is, if i use direct connection i was able to place in global variable and use it when ever i need. But if i have data Source, it works only once, and second use the global variable connection gets invalid.

if you mean this method will return same connection without recreating, then it wont work for me as the connection is open but not valid.

Please advice. 

[Former Member]

JDBC Connector Service offers connection pooling to enable better performance of the system. Re-using connections is less time- and resource-consuming than creating new ones each time your application or client sends a request for connection.

You can manage the number of pooled connections, as well as their lifetime to achieve best performance by your application or client.

Please check following link & manage Maximum Time to Wait for Connection,Expiration,Connection Lifetime,Cleanup Thread etc  properties according to your need & check if it's helpful.

Managing Connection Pooling - Administration Manual - SAP Library

Thanks,

Patralekha

[mohamed_shabir]

Hello Patralekha,

Thanks for reply.  I had set connection timeout to 300 seconds, Still didnt help. it is like when i load the query in init method, it works, but if i run the same query with same connection after few seconds by triggering an action from view, it fails.

Any more suggestion would help

[Former Member]

ok.

Can you set  the connection into your global variable inside Component Controller DoBeforeNavigation() method & check?