Hi Axel, Thank you very much for your reply. Is the ping query property is only applicable for SimpleDatasource? I am using Websphere's datasource as per my project requirement. Could you please let me know if I can make use of the ping query in my sqlmapconfig even in this case.
Leucht, Axel wrote: > > 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: [email protected] >>>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. > > > ----- Venkat -- View this message in context: http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15847799.html Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
