You have to configure the ping quesry in WebSphere - not in iBATIS. Here's a link to the WebSphere documentation that shows how to do it:
http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html Use the search string "pretest sql string" in the info center if the link doesn't work. Jeff Butler On Wed, Mar 5, 2008 at 5:03 AM, IBATIS <[EMAIL PROTECTED]> wrote: > > 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<http://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<http://nabble.com/> > . > >
