You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by IBATIS <ve...@wipro.com> on 2008/02/15 09:13:47 UTC
Transaction Time Out and Stale Connection Exception when using
IBatis SQLMapper
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
{
SafetracSQLConfig.getInstance().getSqlMapper().startTransaction();
//Invoke business logic classes MyBlogicClass
SafetracSQLConfig.getInstance().getSqlMapper().commitTransaction();
return object;
}
catch(Exception ex)
{
throw new MySessionBeanException(ex.getMessage());
}
finally
{
try
{
SafetracSQLConfig.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:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:40:25,766: <DEBUG> {conn-100000} Preparing Statement:
select F.AP_CDE as AP_CDE , F.OP_AL_CDE as OP_AL_CDE,
decode(
to_char(F.FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(F.FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(F.FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(F.FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, decode(
to_char(F.FLT_GND_DTM,'yyyy-mm-dd')||'T'||to_char(F.FLT_GND_DTM,'HH24:MI:SS'),'T',null,to_char(F.FLT_GND_DTM,'yyyy-mm-dd')||'T'||to_char(F.FLT_GND_DTM,'HH24:MI:SS'))
FLT_GND_DTM, F.WF_STATS_CDE WF_STATS_CDE, decode(
to_char(F.FLT_EST_DTM,'yyyy-mm-dd')||'T'||to_char(F.FLT_EST_DTM,'HH24:MI:SS'),'T',null,to_char(F.FLT_EST_DTM,'yyyy-mm-dd')||'T'||to_char(F.FLT_EST_DTM,'HH24:MI:SS'))
FLT_EST_DTM, F.OP_FLT_NUM as OP_FLT_NUM, F.ARR_DPTR_CDE as
ARR_DPTR_CDE, I1.LEG_SEQ_NUM as LEG_SEQ_NUM, f.AC_NOSE_NUM as
AC_NOSE_NUM, I1.ORIG_DEST_IND as ORIG_DEST_IND,
NVL(D.IS_CLZ_IND,'N') PRE_CLOSE , AI.LD_COMPLT_IND IS_CLOSE,
I1.LEG_AP_CDE FLT_DEST_CDE, I2.LEG_AP_CDE FLT_FNL_DEST_CDE,
F.GATE_CDE as GATE_CDE, ( SELECT
decode(count(PLT.pos_num),0,'N','Y') FROM t_pllt_pos PLT where
PLT.ac_nose_num = F.ac_nose_num ) IS_WIDE_BODY, A.INTL_CTY_IND
INTL_CTY_IND, decode(
to_char(AI.UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(AI.UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(AI.UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(AI.UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, ? as IS_NEW_REQUEST from t_flt F,
t_flt_leg I1, t_flt_leg I2, t_flt_add_info AI, t_ap
A, T_DVC_REG D WHERE F.op_flt_num = ? AND
to_char(F.FLT_SCHED_DTM,'yyyy-mm-dd') = ? AND F.ap_cde
= ? AND TRIM(F.OP_AL_CDE) = TRIM(?) AND F.arr_dptr_cde
= 'D' AND F.op_al_cde = I1.op_al_cde AND
F.op_flt_num = I1.op_flt_num AND F.flt_sched_dtm =
I1.flt_sched_dtm AND F.ap_cde = I1.ap_cde AND
F.arr_dptr_cde = I1.arr_dptr_cde AND F.op_al_cde =
I2.op_al_cde AND F.op_flt_num = I2.op_flt_num AND
F.flt_sched_dtm = I2.flt_sched_dtm AND F.ap_cde
= I2.ap_cde AND F.arr_dptr_cde = I2.arr_dptr_cde AND
F.op_al_cde = AI.op_al_cde AND F.op_flt_num =
AI.op_flt_num AND F.flt_sched_dtm = AI.flt_sched_dtm AND
F.ap_cde = AI.ap_cde AND I1.leg_ap_cde(+) =
A.ap_cde AND I1.leg_seq_num(+) = 0 AND I2.leg_seq_num
= ( SELECT max(leg_seq_num) FROM t_flt_leg WHERE op_al_cde
= F.op_al_cde AND op_flt_num = F.op_flt_num AND
flt_sched_dtm = F.flt_sched_dtm AND ap_cde = F.ap_cde
AND arr_dptr_cde = F.arr_dptr_cde ) AND F.OP_AL_CDE =
D.OP_AL_CDE(+) AND F.OP_FLT_NUM=D.OP_FLT_NUM(+) AND
F.AP_CDE=D.AP_CDE(+) AND F.FLT_SCHED_DTM=D.FLT_SCHED_DTM(+) AND
D.REG_IND(+)='Y' AND ? IN('Y','N')
2008/02/13 12:40:43,031: <DEBUG> {conn-100000} Preparing Statement:
select count(*) from T_DVC_REG where dvc_id=? and trim(op_al_cde)=trim(?)
and ap_cde=? and op_flt_num=? and to_char(flt_sched_dtm,'yyyy-mm-dd
hh24:mi:ss')=?
2008/02/13 12:41:19,078: <DEBUG> {conn-100007} Connection
2008/02/13 12:41:19,094: <DEBUG> {conn-100007} Preparing Statement:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:41:19,812: <DEBUG> {conn-100007} Preparing Statement:
select OP_AL_CDE, AP_CDE, BAG_CRTN_TYP_CDE, BAG_ID,
BAG_PSGR_SEQ_NUM, decode(
to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'))
BAG_TAG_CRTN_DTM, BAG_TYP_CDE, BAG_WT_LB, decode(
to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'))
BSM_CRTN_DTM, BSM_DEL_IND, CBN_CL_CDE, CNTNR_ID, CNTNR_SEQ_NUM,
DEST_AP_CDE, DMGD_IND, decode(
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, LD_AUTH_IND, LD_SEQ_NUM, LD_STATS_CDE, decode(
to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, OFLD_IND, OP_FLT_NUM, POS_NUM, RLBL_IND, decode(
to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'),'T',null,to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'))
SCANNED_TIME, SCRTY_STATS_CDE, SHRT_CONN_FLT_IND, TRKG_IND,
XPDT_IND, decode(
to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'),'T',null,to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'))
ALRT_TMS from T_BAG_TAG where OP_FLT_NUM=? AND
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')=? AND AP_CDE=? AND TRIM(OP_AL_CDE)=
TRIM(?) AND ? IN('Y','N')
2008/02/13 12:44:21,516: <DEBUG> {conn-100012} Connection
2008/02/13 12:44:21,516: <DEBUG> {conn-100012} Preparing Statement:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:44:21,812: <DEBUG> {conn-100012} Preparing Statement:
select OP_AL_CDE, AP_CDE, BAG_CRTN_TYP_CDE, BAG_ID,
BAG_PSGR_SEQ_NUM, decode(
to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'))
BAG_TAG_CRTN_DTM, BAG_TYP_CDE, BAG_WT_LB, decode(
to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'))
BSM_CRTN_DTM, BSM_DEL_IND, CBN_CL_CDE, CNTNR_ID, CNTNR_SEQ_NUM,
DEST_AP_CDE, DMGD_IND, decode(
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, LD_AUTH_IND, LD_SEQ_NUM, LD_STATS_CDE, decode(
to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, OFLD_IND, OP_FLT_NUM, POS_NUM, RLBL_IND, decode(
to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'),'T',null,to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'))
SCANNED_TIME, SCRTY_STATS_CDE, SHRT_CONN_FLT_IND, TRKG_IND,
XPDT_IND, decode(
to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'),'T',null,to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'))
ALRT_TMS from T_BAG_TAG where OP_FLT_NUM=? AND
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')=? AND AP_CDE=? AND TRIM(OP_AL_CDE)=
TRIM(?) AND ? IN('Y','N')
2008/02/13 12:47:15,000: <DEBUG> {conn-100017} Connection
2008/02/13 12:47:15,000: <DEBUG> {conn-100017} Preparing Statement:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:47:15,281: <DEBUG> {conn-100017} Preparing Statement:
select OP_AL_CDE, AP_CDE, BAG_CRTN_TYP_CDE, BAG_ID,
BAG_PSGR_SEQ_NUM, decode(
to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'))
BAG_TAG_CRTN_DTM, BAG_TYP_CDE, BAG_WT_LB, decode(
to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'))
BSM_CRTN_DTM, BSM_DEL_IND, CBN_CL_CDE, CNTNR_ID, CNTNR_SEQ_NUM,
DEST_AP_CDE, DMGD_IND, decode(
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, LD_AUTH_IND, LD_SEQ_NUM, LD_STATS_CDE, decode(
to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, OFLD_IND, OP_FLT_NUM, POS_NUM, RLBL_IND, decode(
to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'),'T',null,to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'))
SCANNED_TIME, SCRTY_STATS_CDE, SHRT_CONN_FLT_IND, TRKG_IND,
XPDT_IND, decode(
to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'),'T',null,to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'))
ALRT_TMS from T_BAG_TAG where OP_FLT_NUM=? AND
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')=? AND AP_CDE=? AND TRIM(OP_AL_CDE)=
TRIM(?) AND ? IN('Y','N')
2008/02/13 12:55:08,875: <DEBUG> {conn-100000} Connection
2008/02/13 12:55:08,891: <DEBUG> {conn-100000} Preparing Statement:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:55:09,406: <DEBUG> {conn-100000} Preparing Statement:
select AP_CDE, ARR_DPTR_CDE, AVG_ITM_WT_LB,
AWB_NUM, CMDTY_TYP_CDE, CNTNR_SEQ_NUM, DEST_AP_CDE,
DNGR_FRGT_IND, decode(
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, FNL_DEST_AP_CDE, FRGT_STG_LOC_CDE, ITM_CNT,
ITM_WT_LB, LOAD_IND, LOAD_SEQ_NUM, decode(
to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, OP_AL_CDE, OP_FLT_NUM, POS_NUM,
PREV_FRGT_STG_LOC_CDE, PREV_POS_NUM, decode(
to_char(SCN_DTM,'yyyy-mm-dd')||'T'||to_char(SCN_DTM,'HH24:MI:SS'),'T',null,to_char(SCN_DTM,'yyyy-mm-dd')||'T'||to_char(SCN_DTM,'HH24:MI:SS'))
SCN_DTM, SCN_IND, THRU_IND, WF_SCRN_LIN_ITM_NUM, XPDT_IND,
LD_FLT_NUM, decode(
to_char(LD_FLT_DPTR_DTM,'yyyy-mm-dd')||'T'||to_char(LD_FLT_DPTR_DTM,'HH24:MI:SS'),'T',null,to_char(LD_FLT_DPTR_DTM,'yyyy-mm-dd')||'T'||to_char(LD_FLT_DPTR_DTM,'HH24:MI:SS'))
LD_FLT_DPTR_DTM, LD_AL_CDE, SND_TO_CARDL_IND from T_FRGT
where OP_FLT_NUM=? AND to_char(FLT_SCHED_DTM,'yyyy-mm-dd')=? AND
AP_CDE=? AND TRIM(OP_AL_CDE)=TRIM(?) AND ? IN('Y','N')
2008/02/13 12:55:52,047: <DEBUG> {conn-100005} Connection
2008/02/13 12:55:52,047: <DEBUG> {conn-100005} Preparing Statement:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:55:52,344: <DEBUG> {conn-100005} Preparing Statement:
select OP_AL_CDE, AP_CDE, BAG_CRTN_TYP_CDE, BAG_ID,
BAG_PSGR_SEQ_NUM, decode(
to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'))
BAG_TAG_CRTN_DTM, BAG_TYP_CDE, BAG_WT_LB, decode(
to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'))
BSM_CRTN_DTM, BSM_DEL_IND, CBN_CL_CDE, CNTNR_ID, CNTNR_SEQ_NUM,
DEST_AP_CDE, DMGD_IND, decode(
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, LD_AUTH_IND, LD_SEQ_NUM, LD_STATS_CDE, decode(
to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, OFLD_IND, OP_FLT_NUM, POS_NUM, RLBL_IND, decode(
to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'),'T',null,to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'))
SCANNED_TIME, SCRTY_STATS_CDE, SHRT_CONN_FLT_IND, TRKG_IND,
XPDT_IND, decode(
to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'),'T',null,to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'))
ALRT_TMS from T_BAG_TAG where OP_FLT_NUM=? AND
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')=? AND AP_CDE=? AND TRIM(OP_AL_CDE)=
TRIM(?) AND ? IN('Y','N')
2008/02/13 12:59:46,109: <DEBUG> {conn-100010} Connection
2008/02/13 12:59:46,109: <DEBUG> {conn-100010} Preparing Statement:
SELECT ACTY_CDE FROM T_SEM_DVC_LIST WHERE TRIM(DVC_ID)=? AND
TRIM(AP_CDE)=?
2008/02/13 12:59:47,172: <DEBUG> {conn-100010} Preparing Statement:
select OP_AL_CDE, AP_CDE, BAG_CRTN_TYP_CDE, BAG_ID,
BAG_PSGR_SEQ_NUM, decode(
to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BAG_TAG_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BAG_TAG_CRTN_DTM,'HH24:MI:SS'))
BAG_TAG_CRTN_DTM, BAG_TYP_CDE, BAG_WT_LB, decode(
to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'),'T',null,to_char(BSM_CRTN_DTM,'yyyy-mm-dd')||'T'||to_char(BSM_CRTN_DTM,'HH24:MI:SS'))
BSM_CRTN_DTM, BSM_DEL_IND, CBN_CL_CDE, CNTNR_ID, CNTNR_SEQ_NUM,
DEST_AP_CDE, DMGD_IND, decode(
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'),'T',null,to_char(FLT_SCHED_DTM,'yyyy-mm-dd')||'T'||to_char(FLT_SCHED_DTM,'HH24:MI:SS'))
FLT_SCHED_DTM, LD_AUTH_IND, LD_SEQ_NUM, LD_STATS_CDE, decode(
to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'),'T',null,to_char(UPDT_TMS,'yyyy-mm-dd')||'T'||to_char(UPDT_TMS,'HH24:MI:SS'))
MODIFIED_TIME, OFLD_IND, OP_FLT_NUM, POS_NUM, RLBL_IND, decode(
to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'),'T',null,to_char(SCN_TMS,'yyyy-mm-dd')||'T'||to_char(SCN_TMS,'HH24:MI:SS'))
SCANNED_TIME, SCRTY_STATS_CDE, SHRT_CONN_FLT_IND, TRKG_IND,
XPDT_IND, decode(
to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'),'T',null,to_char(ALRT_TMS,'yyyy-mm-dd')||'T'||to_char(ALRT_TMS,'HH24:MI:SS'))
ALRT_TMS from T_BAG_TAG where OP_FLT_NUM=? AND
to_char(FLT_SCHED_DTM,'yyyy-mm-dd')=? AND AP_CDE=? AND TRIM(OP_AL_CDE)=
TRIM(?) AND ? IN('Y','N')
Awating response at the earliest.
Thanks in advance.
Venkat
--
View this message in context: http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15496139.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Transaction Time Out and Stale Connection Exception when using
IBatis SQLMapper
Posted by IBATIS <ve...@wipro.com>.
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, "venkatesh.madhavan@wipro.com"
> <ve...@wipro.com> 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.
Re: AW: Transaction Time Out and Stale Connection Exception when
using IBatis SQLMapper
Posted by IBATIS <ve...@wipro.com>.
Hi,
I configured the same in the websphere datasource properties. I am waiting
for the connection to become stale. Hopefully this should work.
Could you please let me know whether doing this shall decrease the
performance of my application.
Thanks
Venkat
Jeff Butler-2 wrote:
>
> 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 <ve...@wipro.com>
> 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:venkatesh.madhavan@wipro.com]
>> >>>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, "venkatesh.madhavan@wipro.com"
>> >>>> <ve...@wipro.com> 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/>
>> .
>>
>>
>
>
-----
Venkat
--
View this message in context: http://www.nabble.com/Transaction-Time-Out-and-Stale-Connection-Exception-when-using-IBatis-SQLMapper-tp15496139p15849418.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: AW: Transaction Time Out and Stale Connection Exception when using IBatis SQLMapper
Posted by Jeff Butler <je...@gmail.com>.
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 <ve...@wipro.com> 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:venkatesh.madhavan@wipro.com]
> >>>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, "venkatesh.madhavan@wipro.com"
> >>>> <ve...@wipro.com> 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/>
> .
>
>
Re: AW: Transaction Time Out and Stale Connection Exception when
using IBatis SQLMapper
Posted by IBATIS <ve...@wipro.com>.
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:venkatesh.madhavan@wipro.com]
>>>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, "venkatesh.madhavan@wipro.com"
>>>> <ve...@wipro.com> 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.