Sharing JDBC connection between web service methods

[Former Member]

Hi

I have problem with sharing JDBC connection between web service methods.

In one method I get JDBC connection from data source, set auto-commit

mode to <i>false</i>, call UPDATE statement and store connection object into custom

session object. In next method I want to use this opened connection,

but SQL statement failed with exception:

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

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

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

at com.sap.engine.services.dbpool.cci.ConnectionHandle.prepareCall(ConnectionHandle.java:96)

...

In application server trace log I found yet another exception:

UNSAFE THREAD CONNECTION USAGE DETECTED. The Connection com.sap.engine.services.dbpool.cci.ConnectionHandle@6afa82 has been acquired in thread HTTP Worker [2] and used in thread HTTP Worker [0]. dumpStack(): java.lang.Exception: Tracing the call:

at com.sap.engine.services.dbpool.cci.ConnectionHandle.checkIfThreadSafe(ConnectionHandle.java:609)

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

at com.sap.engine.services.dbpool.cci.ConnectionHandle.prepareCall(ConnectionHandle.java:96)

...

I use SAP Netweaver J2EE Preview (2007).

Data source is defined as JDBC-1.x.

JDBC driver is from Oracle 10.2.0.1.

Web service works correctly on Sun Application Server and Oracle OC4J.

Why JDBC connection is invalid, when I does not call close() method?

Can any one help me to solve this problem?

Thanks

VP

[Former Member]

In my opinion you don't solve such problem in this way - WebServices should be treated as stateless objects. You may consider storing your data between subsequent WS methods calls in temporary database, and than as commit from user arrives to copy date from temporary db into your main db.

Maybe even serializing your Java objects into byte stream and storing in temporary DB as BLOB field would be sufficient for you. Read this for further details: http://java.sun.com/j2se/1.5.0/docs/api/java/io/ObjectOutputStream.html

[Former Member]

Hi,

Thank you for your advice, but this way involves too much changes in code of web service, which works correctly on another application servers. My idea is found data source configuration or piece of code, which informs SAP application server, that I need lock allocated database connection.

Thanks

V.Popelka

P.S.: Same problem I can have in servlets or JSP pages.

[Former Member]

You need to radically change your design.

The backend (database connection) should not at all be exposed to the front end (web service call).

You would need to call an EJB from the different web services calls and on final commit call commit all changes to the DB. The EJB would need to have session persistence and be embedded in the session. You could release / remove it from the session on commit or rollback.

However this is not conducive to a webservice as such as the different calls to the webservice should be stateless. (you could have some system do the calls and not a web browser and you'd be up S... creek).

You need to revise your Webservice and have a method that supplies all the information to allow a commit / rollback at the single call.

Enjoy

[Former Member]

Hi,

Try like after finishing your work on webservice1 you should set set auto-commit mode to true.

Kind Regards,

S.Saravanan.

[Former Member]

Hi,

Thank you for your answer, but it's not solution of my problem. Because when I set auto-commit mode back to <i>true</i>, changes are committed to database. I want to leave transaction alive across number of calls of web service, until client sends request to commit or rollback.

Regards

V.Popelka