Re: nested iteration problem

Fri, 19 May 2006 09:18:46 -0700

Jeff,
 
Right now I am making the sql-map-config.xml to read the database properties from a property file, but now my question is how should I access the database configurations i set in the server rather than reading from a property file. If you have some examples please send across. Thanks
 
Rahesh
 
 
On 5/13/06, Jeff Butler <[EMAIL PROTECTED] > wrote:
I don't think this relates to any of the iterate bugs in iBATIS - it doesn't look like any of the issues that I'm aware of.  It would be helpful for you to turn on logging and see the generated statement.  My guess is that adding <isNotEmpty> tags around the first two iterates, and changing with the "prepends" might solve the problem - but it is hard to tell without more detail.  Again, statement logging will help you diagnose this.  Please see the WIKI pages for information on how to turn on logging.
 
Jeff Butler

 
On 5/12/06, Rahesh Kumar <[EMAIL PROTECTED]> wrote:
Jeff,
 
I have one more query.As you said this is not nested iteration problem. But I have one more problem why is multiple iteration tags not working?
 
for eg I have query like
 

select prj.group_name Group_name, prj.PROJECT_NAME PROJECT_NAME , prj.status Status, EXECUTION_TYPE ,Project_Estimated_Cost,

actual_projectcost(prj.project_id) actual_cost, (Project_Estimated_Cost - actual_projectcost(prj.project_id)) Difference_cost

from PROJECTS prj,Contractor_projects contprj

<dynamic prepend="WHERE">

prj.project_id = contprj.project_id AND contprj.vendor_id = #selectedVendor#

<iterate prepend="AND" property="selectedStatus" open="(" close=")" conjunction="OR">

prj.status = #selectedStatus[]#

</iterate>

<iterate prepend="AND" property="selectedLocation" open="(" close=")" conjunction="OR">

execution_type = #selectedLocation[]#

</iterate>

<isNotEmpty prepend="AND" property="selectedProjects">

<iterate prepend="AND" property="selectedProjects" open="(" close=")" conjunction="OR">prj.project_id = #selectedProjects[]#</iterate>

</isNotEmpty>

<isNotEmpty prepend="AND" property="selectedProjectGroups">

<iterate prepend="AND" property="selectedProjectGroups" open="(" close=")" conjunction="OR">group_id = #selectedProjectGroups[]#</iterate>

</isNotEmpty>

</dynamic>

group by prj.group_name , prj.PROJECT_NAME , prj.status , EXECUTION_TYPE ,Project_Estimated_Cost ,prj.project_id

order by prj.group_name , prj.PROJECT_NAME

 

when i execute this it gives me an error saying that the statement is not constructed properly. but when i run the same query with only one iterate tag its working fine. Any suggestions are welcome.I need it urgently.Do we have any patches released for this. Thanks

Rahesh



 
On 5/12/06, Jeff Butler <[EMAIL PROTECTED] > wrote:
This is somewhat convoluted because of using maps...and I'm just typing, so there may be errors.  But this is the general idea:
 
List sundaysList = new ArrayList();
 
Map sundaysMap = new HashMap();
sundaysMap.put("firstSunday", some_date);
sundaysMap.put("lastSunday", some_date);
sundaysList.add(sundaysMap);
 
sundaysMap = new HashMap();
sundaysMap.put("firstSunday", some_date);
sundaysMap.put("lastSunday", some_date);
sundaysList.add(sundaysMap);

Map parms = new HashMap();
parms.put("sundaysList", sundaysList);
 
queryForList("statement_id", parms);
 
Then you can do this in the SQL map:
 
<iterate property="sundaysList" ...>
   ...
   where actionDate between #sundaysList[].firstSunday# and #sundaysList[].lastSunday#
</iterate>
 
Jeff Butler
 
On 5/11/06, Rahesh Kumar <[EMAIL PROTECTED]> wrote:
Hi jeff,
 
Thanks for your solutions.can you provide me some example implementation as I am new to IBATIS, I am finding it bit difficult to use your solution directly.
 
Thanks in advance
Rahesh

 
On 5/11/06, Jeff Butler <[EMAIL PROTECTED] > wrote:
This isn't nested iteration.
 
What you should do is have a map property called something like "sundaysList" that returns a List.  That list should contain other maps with firstSunday and lastSunday properties.  Then iterate over the "sundays" property and specify the values like "sundaysList[].firstSunday" and "sundaysList[].lastSunday".
 
Jeff Butler

 
On 5/11/06, Rahesh Kumar <[EMAIL PROTECTED]> wrote:
Hi,
 
Need your help in solving this below mentioned problem, I am using IBatis 2.0 version ,I am facing problem as given below
 
 
I have a statement like one below
 
 
<select id="getVendorTSStatusList" resultMap="vendorTimesheetStatusResult">

select

( select group_name projectgroupname from project_groups where group_id =

(select group_id from projects where project_id = cp.project_id)

) projectgroupname , project_name , contractor_name, timesheet_approver_name, working_from, regular_rate,

offshore_regular_rate, contractor_upi, project_id from CONTRACTOR_PROJECTS cp where ROWID IN

(select MAX(rowid) from CONTRACTOR_PROJECTS GROUP BY contractor_upi) and vendor_id = #selectedVendors#

<dynamic>

<isNotNull prepend="AND" property="firstSundayList">

<iterate prepend="AND" property="firstSundayList" open="(" close=")" conjunction="OR">

contractor_upi not in (select user_upi from user_profile_delete_log

where action_date <![CDATA[<]]> (TO_DATE(#firstSundayList[]#, 'MM/DD/YYYY')-6))

or contractor_upi in (select user_upi from user_profile_delete_log where action_date

between (TO_DATE(#firstSundayList[]#, 'MM/DD/YYYY')-6) and (TO_DATE(#lastSundayList[]#, 'MM/DD/YYYY') + 1))

or contractor_upi in (select user_upi from user_profile_delete_log where

(user_upi,action_date) in (select user_upi, max(action_date) as action_date

from user_profile_delete_log group by user_upi )

and action_date <![CDATA[<]]> (TO_DATE(#firstSundayList[]#, 'MM/DD/YYYY')-6) and action = '')

</iterate>

</isNotNull>

</dynamic>

<!-- avoid contractor duplication -->

group by project_name , contractor_name, timesheet_approver_name, working_from, regular_rate,

offshore_regular_rate, contractor_upi, project_id order by contractor_name , projectgroupname , project_name

</select>

 

and I am passing the values via a HashMap , the value as set in HashMap are selectedVendors = '0000000001' , firstSundayList = [07/03/2005] and lastSundayList = [07/24/2005] . when I pass this to the database , I am getting the number format exception.

I am attching the stack trace for your kind reference.Please advice me where the issue is.If u have any patch for this, Please send it across.Thanks

 

com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred in ctrs/persistence/sql/Reports.xml.

--- The error occurred while preparing the mapped statement for execution.

--- Check the getVendorTSStatusList.

--- Check the parameter map.

--- Cause: com.ibatis.common.beans.ProbeException: Error getting ordinal list from JavaBean. Cause java.lang.NumberFormatException:

Caused by: java.lang.NumberFormatException:

Caused by: com.ibatis.common.beans.ProbeException: Error getting ordinal list from JavaBean. Cause java.lang.NumberFormatException:

Caused by: java.lang.NumberFormatException:

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at ctrs.persistence.dao.sqlmapdao.ReportsSqlMapDao.getVendorTSStatusList(ReportsSqlMapDao.java:46)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at java.lang.reflect.Method.invoke(Native Method)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:64)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at $Proxy8.getVendorTSStatusList(Unknown Source)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at ctrs.domain.logic.ReportsLogic.getVendorTSStatusList(ReportsLogic.java:145)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at ctrs.actions.TimesheetsAction.generateVendorReport(TimesheetsAction.java:1335)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at java.lang.reflect.Method.invoke(Native Method)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:948)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:610)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:431)

[5/10/06 10:09:03:865 IST] 59fea7ad SystemErr R at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)

 

 

Thanks & Regards,

Raheshkumar.R

 







Reply via email to