I wasn't following this thread, so bear with me if someone already mentioned it...
I suppose that you're connection pool is thinking the connection to the database to be valid when in fact it isn't. You can setup a "ping query" with almost every ConnectionPool to let the pool check whether the connection is still valid before it delivers it to you're application. For a SimpleDataSource with iBATIS you can setup such thing under a Pool.PingQuery. Read the SQL Maps documentation (on page 68). Hope that helps. /Axel >>-----Ursprüngliche Nachricht----- >>Von: IBATIS [mailto:[EMAIL PROTECTED] >>Gesendet: Mittwoch, 5. März 2008 11:13 >>An: user-java@ibatis.apache.org >>Betreff: Re: Transaction Time Out and Stale Connection Exception when >>using IBatis SQLMapper >> >> >> >>Hi, >> >>Thanks for the advise. I would like to state here that I have not >>deliberately put my questions 6 times. It was happened due to >>my lack of >>knowledge to post the queries in this forum. Since my query was not >>appearing on the forum, I thought that I can post directly >>through mail. >>Anyways sorry about that. >> >>Coming back to the problem, I am not using any container managed >>persistence, I am using Bean managed persistence type of >>stateless session >>bean only. Hence in this case, I believe the JNDI Datasource >>should hold >>good (correct me if I am wrong). >> >>Clarification required regarding Stale Connection Exception. >> >>I am getting often this stale connection exception when my >>application is >>idle for 1 hour and for the very first request for database operation. >> >>I tried retrying for the very first db operation in case of >>stale connection >>exception. I could able to retry this but since this is >>happening within a >>single EJB's transaction, I am getting transaction rollback exception. >> >>Is there anyother way in which I can overcome this exception. >> >>See my below approach. >> >>1. My Ejb calls my dao. >>2. In my DAO, I am starting the sqlmapper transaction >>3. trying to invoke a db query >>4. comiting in case of successful db execution. >>5. In case of error, I am calling the same method from the >>exception block >>only once. >>6. In my finally block I am ending my sqlmapper transaction. >> >>Code is as below. >> >>MyEJB (Stateless Session EJB) >> public String validateUserID(String userID)throws Exception >> { >> new MYDAO().validateUser(userID); >> } >> >>MyDAO >> public String validateUser ( String userID) throws >>MYDAOException >> { >> try >> { >> HashMap hshUser = new HashMap(); >> hshUser.put("userID", userID); >> >> getSqlMapper().startTransaction(); >> String usrStatus = (String) >>getSqlMapper().queryForObject("validateUserID", >> hshUser); >> getSqlMapper().commitTransaction(); >> return usrStatus; >> >> } >> catch(Exception sqlMapEX) >> { >> >> if(isRetryAllowed()) >> { >> retryAllowed = false; >> return validateUser (userID); >> } >> >> throw new MyDAOException(sqlMapEX); >> } >> finally >> { >> try >> { >> getSqlMapper().endTransaction(); >> } >> catch(Exception ex) >> { >> MyLogger.logFatal("Error while >>ending Transaction "+ex.getMessage()); >> } >> } >> } >> >> >>In the above you can see that I am retrying only once. For >>the second time I >>am not getting any exception from the method validateDeviceID of MyDAO >>rather I am getting exception from Ejb container saying that >>transaction is >>rolledback. >> >>Please clarify what could be wrong. >> >>Thanks in advance. >> >>Regards >>Venkat >> >> >> >>Christopher Lamey wrote: >>> >>> First, some etiquette advise is in order. It is considered >>a faux pas to >>> send the same message to the list 6 times in a short amount of time. >>> Posting something more will not get you more or quicker >>responses. Also, >>> sending the same message directly to members of the list >>immediately after >>> posting to the list itself is not going to give you a boost >>in help. In >>> fact, it will probably make people ignore you. >>> >>> As to your problem, you currently have your JNDI DataSource >>setup as a >>> JDBC >>> source. Is this true or are you using a container managed >>DataSource? If >>> you are using something like a JTA provider through WAS, >>you will need >>> something like this: >>> >>> <transactionManager type="JTA" > >>> <property name="UserTransaction" >>value="java:/comp/UserTransaction"/> >>> <dataSource type="JNDI"> >>> <property name="DataSource" >>value="java:comp/env/jdbc/jpetstore"/> >>> </dataSource> >>> </transactionManager> >>> >>> Please see page 16 of the iBATIS PDF manual for details. >>> >>> Basically you're binding a DataSource into JNDI and you >>need to configure >>> the DataSource there, not in iBATIS. So if you're binding >>a one via DBCP, >>> you could configure the idle time or a ping query or whatever. >>> >>> Cheers, >>> topher >>> >>> On 2/15/08 6:38 AM, "[EMAIL PROTECTED]" >>> <[EMAIL PROTECTED]> wrote: >>> >>>> Hi, >>>> >>>> I am using WAS 6.0 Datasource inside my Ibatis sql mapper. >>The code in >>>> my sqlmapconfig.xml is as follows. >>>> >>>> <transactionManager type="JDBC" commitRequired="true"> >>>> <dataSource type="JNDI"> >>>> <property name="DataSource" >>>> value="jdbc/MyDataSrc" /> >>>> </dataSource> >>>> </transactionManager> >>>> >>>> I am calling the database operations from my EJB which is >>stateless and >>>> Bean Managed. The code inside my EJB is as follows. >>>> >>>> EJB:public MySOAPEnvelope processDownload ( MyReqEnvelope >>>> reqEnvelope)throws MySessionBeanException >>>> { >>>> try >>>> { >>>> MySQLConfig.getInstance().getSqlMapper().startTransaction(); >>>> >>>> //Invoke business logic classes MyBlogicClass >>>> >>MySQLConfig.getInstance().getSqlMapper().commitTransaction(); >>>> return object; >>>> } >>>> catch(Exception ex) >>>> { >>>> throw new MySessionBeanException(ex.getMessage()); >>>> } >>>> finally >>>> { >>>> try >>>> { >>>> MySQLConfig.getInstance().getSqlMapper().endTransaction(); } >>>> catch(Exception ex) >>>> { >>>> MyLogger.logError (" error while commiting and closing >>connection >>>> "+ex); >>>> } >>>> } >>>> >>>> } >>>> >>>> MyBlogicClass calls MyDAO inside this DAO class I am invoking the >>>> database query which are defined in the IBatis SQLMapper >>xml files and >>>> returned back the result to MyBlogicClass. Which inturn >>return to MyEJB. >>>> >>>> >>>> >>>> Since I am using IBatis for the first time in my project. >>I wanted to >>>> know whether the way in which I am doing is correct with respect >>>> datasource connection pool handling and other things. The >>reason for >>>> which I am asking is, at times I am getting transaction timed out >>>> exception while executing some queries which normally does >>not happens. >>>> Also When I am getting the connection for the very first >>time and not >>>> utilising it for long then I am getting the Stale >>connection exception. >>>> I think this is somewhat obvious but please let me know if >>I am doing >>>> something wrong due to which I am getting this exception. >>Also is there >>>> anyway by which we can retry while I am getting this stale >>connection >>>> exception. >>>> >>>> My Doubts are Clarifications are as follows. >>>> >>>> 1. Whether the Datasource settings described in my >>SQLMapConfig.xml is >>>> correct or is there any other properties I need to set in >>the same in >>>> order to properly commit or rollback and close the >>connection so that >>>> the usage Connection Object from the datasource connection pool is >>>> utilised minimal. >>>> >>>> 2. Whether the code which I have put in my ejb is OK. >>>> >>>> To conclude, I would like to know whether the problem behind this >>>> transaction timed out and stale connection exception is >>due to way in >>>> which I am handling the connection through IBatis or its >>due to database >>>> server or network issue. >>>> >>>> For your reference I am giving a sample log messages which >>I get from >>>> the IBatis (hoping that you could get some idea about the >>connection >>>> usage). >>>> >>>> 2008/02/13 12:40:21,156: <DEBUG> {conn-100000} Connection >>>> 2008/02/13 12:40:21,203: <DEBUG> {conn-100000} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:40:25,766: <DEBUG> {conn-100000} Preparing Statement: >>>> Query B >>>> 2008/02/13 12:40:43,031: <DEBUG> {conn-100000} Preparing Statement: >>>> Query C >>>> 2008/02/13 12:41:19,078: <DEBUG> {conn-100007} Connection >>>> 2008/02/13 12:41:19,094: <DEBUG> {conn-100007} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:41:19,812: <DEBUG> {conn-100007} Preparing Statement: >>>> Query B >>>> 2008/02/13 12:44:21,516: <DEBUG> {conn-100012} Connection >>>> 2008/02/13 12:44:21,516: <DEBUG> {conn-100012} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:44:21,812: <DEBUG> {conn-100012} Preparing Statement: >>>> Query B >>>> 2008/02/13 12:47:15,000: <DEBUG> {conn-100017} Connection >>>> 2008/02/13 12:47:15,000: <DEBUG> {conn-100017} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:47:15,281: <DEBUG> {conn-100017} Preparing Statement: >>>> Query B >>>> 2008/02/13 12:55:08,875: <DEBUG> {conn-100000} Connection >>>> 2008/02/13 12:55:08,891: <DEBUG> {conn-100000} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:55:09,406: <DEBUG> {conn-100000} Preparing Statement: >>>> Query B >>>> 2008/02/13 12:55:52,047: <DEBUG> {conn-100005} Connection >>>> 2008/02/13 12:55:52,047: <DEBUG> {conn-100005} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:55:52,344: <DEBUG> {conn-100005} Preparing Statement: >>>> Query B >>>> 2008/02/13 12:59:46,109: <DEBUG> {conn-100010} Connection >>>> 2008/02/13 12:59:46,109: <DEBUG> {conn-100010} Preparing Statement: >>>> Query A >>>> 2008/02/13 12:59:47,172: <DEBUG> {conn-100010} Preparing Statement: >>>> Query B >>>> >>>> >>>> >>>> Awating response at the earliest. >>>> >>>> Thanks in advance. >>>> Venkat >>>> >>>> >>>> The information contained in this electronic message and >>any attachments >>>> to >>>> this message are intended for the exclusive use of the >>addressee(s) and >>>> may >>>> contain proprietary, confidential or privileged >>information. If you are >>>> not >>>> the intended recipient, you should not disseminate, >>distribute or copy >>>> this >>>> e-mail. Please notify the sender immediately and destroy >>all copies of >>>> this >>>> message and any attachments. >>>> >>>> WARNING: Computer viruses can be transmitted via email. >>The recipient >>>> should >>>> check this email and any attachments for the presence of >>viruses. The >>>> company >>>> accepts no liability for any damage caused by any virus >>transmitted by >>>> this >>>> email. >>>> >>>> www.wipro.com >>>> >>> >>> >>> >> >> >>----- >>Venkat >>-- >>View this message in context: http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15846998.html Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
