Cannot get new screen to work properly; similar screen was recently added successfully

Alaska Cowboy
Alaska Cowboy used Ask the Experts™
on
I recently added a screen to a small application, everything worked out fine. The Oracle data looks like this:

#                  Errors Description
755              MED_COV Dup Found
453              MEM_MEMB_TEFRA Dup Found
6                  MEM_MEMB_WHCR Dup Found

So my next screen is to drill down on one of the above rows to display all "755 errors", with some additional detail.

The error I keep getting is "Invalid Column Name", but the two screens are using (virtually) the same sql, etc. So the sql is not the issue, but obviously something is. Here's the error:
Error 500: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select et.srce_sys_cd, trunc(et.load_dt) as date_loaded, et.fld_nm, et.mtrcs_errs_linkg_id from stage3i.err_tbl et where et.mtrcs_errs_linkg_id = to_number(4413) order by et.fld_nm, et.ent_key_id ]; nested exception is java.sql.SQLException: Invalid column name

I think the spring-servlet file is wrong, but I really don't understand how the pieces fit in the spring-mvc file.

I "re-use" some pieces of the code but obviously I'm tripped up somewhere.

I've also provided all the components for the working screen ("summary") and the screen with the error ("detail"). Since I had just wrestled with the first screen (and got it working), I don't think it's my screen, I thing it's the spring-mvc file.


// Code snippet from spring-mvc file ******************************************** 
   <!-- *********** DATA SOURCE ************* -->   
    <bean id="errorListDao" class="com.carefirst.edw.dao.ErrorListSummaryDAOImpl">   
        <property name="dataSource" ref="dataSource"/>   
    </bean>   
  
    <bean id="errorListDetailDao" class="com.carefirst.edw.dao.ErrorListDetailDAOImpl">   
        <property name="dataSource" ref="dataSource"/>   
    </bean>   
  
    <!-- ***************** WEB CONTROLLERS ******************** -->   
    <bean name="/error_summary.htm" class="com.carefirst.edw.web.ErrorListSummaryController">   
        <property name="errorListDAO" ref="errorListDao"/>   
    </bean>   
       
    <bean name="/error_detail.htm" class="com.carefirst.edw.web.ErrorListDetailController">   
        <property name="errorListDAO" ref="errorListDetailDao"/>   
    </bean>  


// The next two components are shared by both screens *************************
/******** DAO Interface ***********/  
package com.carefirst.edw.dao;   
  
import java.util.List;   
  
public interface ErrorListDAO {   
    public List getLoadList(String errorsId);   
}   
  
  
/******** Domain *******************/  
package com.carefirst.edw.domain;   
  
import java.util.Date;   
  
public class ErrorListItem {   
       
    private String sourceCd;   
    private Date loadDate;   
    private Integer errorCount;   
    private String errorDescr;   
    private Integer errorsId;   
    private String entityKey;      
    private String stageSeqno;   
    private String fieldValue;   
  
    public String getSourceCd() {   
        return sourceCd;   
    }   
    public void setSourceCd(String sourceCd) {   
        this.sourceCd = sourceCd;   
    }      
    public Date getLoadDate() {   
        return loadDate;   
    }   
    public void setLoadDate(Date loadDate) {   
        this.loadDate = loadDate;   
    }   
    public Integer getErrorCount() {   
        return errorCount;   
    }   
    public void setErrorCount(Integer errorCount) {   
        this.errorCount = errorCount;   
    }   
    public String getErrorDescr() {   
        return errorDescr;   
    }   
    public void setErrorDescr(String errorDescr) {   
        this.errorDescr = errorDescr;   
    }      
    public Integer getErrorsId() {   
        return errorsId;   
    }   
    public void setErrorsId(Integer errorsId) {   
        this.errorsId = errorsId;   
    }   
    public String getEntityKey() {   
        return entityKey;   
    }   
    public void setEntityKey(String entityKey) {   
        this.entityKey = entityKey;   
    }      
    public String getStageSeqno() {   
        return stageSeqno;   
    }   
    public void setStageSeqno(String stageSeqno) {   
        this.stageSeqno = stageSeqno;   
    }      
    public String getFieldValue() {   
        return fieldValue;   
    }   
    public void setFieldValue(String fieldValue) {   
        this.fieldValue = fieldValue;   
    }          
  
}  

// The next three components are from the screen that works *************************

/********** DAO Impl ************/  
package com.carefirst.edw.dao;   
  
import java.sql.ResultSet;   
import java.sql.SQLException;   
import java.sql.Types;   
import java.util.List;   
  
import org.springframework.jdbc.core.RowMapper;   
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;   
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;   
  
import com.carefirst.edw.domain.ErrorListItem;   
  
public class ErrorListSummaryDAOImpl extends NamedParameterJdbcDaoSupport implements ErrorListDAO {   
    private static final String LIST_SQL = "" +   
    "select et.srce_sys_cd, trunc(et.load_dt) as date_loaded, count(*) as num_errors, et.fld_nm, " +   
    "et.mtrcs_errs_linkg_id  " +   
    "from stage3i.err_tbl et where et.mtrcs_errs_linkg_id = to_number(:linkingId) " +    
    "group by et.srce_sys_cd, trunc(et.load_dt), et.mtrcs_errs_linkg_id, et.fld_nm, " +   
    "et.err_cd, et.err_sqlcode order by et.fld_nm ";   
       
    public List getLoadList(String errorsId) {   
           
        MapSqlParameterSource map = new MapSqlParameterSource();   
        map.addValue("linkingId", errorsId, Types.VARCHAR);   
           
        List history = getNamedParameterJdbcTemplate().query(LIST_SQL, map, new ListRowMapper());    
           
        return history;   
    }   
  
    private class ListRowMapper implements RowMapper {   
        public Object mapRow(ResultSet rs, int arg1) throws SQLException {   
            ErrorListItem item = new ErrorListItem();   
            item.setSourceCd(rs.getString("srce_sys_cd"));   
            item.setLoadDate(rs.getDate("date_loaded"));   
            item.setErrorCount(rs.getInt("num_errors"));   
            item.setErrorDescr(rs.getString("fld_nm"));   
            item.setErrorsId(rs.getInt("mtrcs_errs_linkg_id"));   
            return item;   
        }   
    }   
}   
  
/*********** Controller **************/  
package com.carefirst.edw.web;   
  
import java.util.Date;   
import java.util.HashMap;   
import java.util.List;   
import java.util.Map;   
  
import javax.servlet.http.HttpServletRequest;   
import javax.servlet.http.HttpServletResponse;   
  
import org.apache.commons.logging.Log;   
import org.apache.commons.logging.LogFactory;   
import org.springframework.web.servlet.ModelAndView;   
import org.springframework.web.servlet.mvc.Controller;   
  
import com.carefirst.edw.dao.ErrorListDAO;   
  
public class ErrorListSummaryController implements Controller {   
    protected final Log logger = LogFactory.getLog(getClass());   
    private ErrorListDAO dao = null;   
       
    public ModelAndView handleRequest(HttpServletRequest request,   
            HttpServletResponse response) throws Exception {   
        String errorId = request.getParameter("ERRORID");   
           
        List list = dao.getLoadList(errorId);   
           
        Map model = new HashMap();   
        model.put("list", list);   
        model.put("errorsId", errorId);   
           
        return new ModelAndView("error_summary", "model", model);   
    }   
  
    public void setErrorListDAO(ErrorListDAO dao) {   
        this.dao = dao;   
    }      
}   
  
/************ JSP ***********************/  
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"    
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">   
      
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>   
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>   
  
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">   
<head>   
    <title>Error summary for load # [add # here] </title>   
    <link rel="stylesheet" href="css/tablesorter.css" type="text/css" id="" media="print, projection, screen" />   
    <link rel="stylesheet" href="css/thickbox.css" type="text/css" media="print,screen" />   
    <link rel="stylesheet" href="css/edw.css" type="text/css" media="print,screen" />   
  
    <script type="text/javascript" src="js/jquery-latest.js"></script>    
    <script type="text/javascript" src="js/jquery.tablesorter.js"></script>    
    <script type="text/javascript" src="js/thickbox.js"></script>    
    <script type="text/javascript" id="js">   
    $(document).ready(function()    
            {            
            $("#statusEDW").tablesorter();     
            }    
    );    
  
    </script>   
</head>   
<body>   
  
  <c:forEach var="errors" items="${model.list}" begin="0" end="0">   
  <h2>Error summary for <br />source ${errors.sourceCd} on <fmt:formatDate value="${errors.loadDate}" pattern="E, MMM dd"/>,   
load id = ${errors.errorsId } </h2>   
  </c:forEach>   
  <a class="cancel"  rel="nofollow">   
    <span class="btn_lbg">   
      <span class="btn_rbg">Close</span>   
    </span>   
  </a>   
<table class="tablesorter" id="statusEDW">   
<thead>   
<tr>     
   <th># errors</th>   
   <th>Description</th>   
</tr>   
</thead>   
<tbody>   
<c:forEach var="errors" items="${model.list}">   
  <tr>   
    <td ><fmt:formatNumber type="number"> ${errors.errorCount}</fmt:formatNumber></td>   
    <td >${errors.errorDescr}</td>   
  </tr>   
</c:forEach>   
</tbody>   
</table>   
</body>   
</html>  

// The next three components are from the screen that doesn't work *************************
/********** DAO Impl ************/  
package com.carefirst.edw.dao;   
  
import java.sql.ResultSet;   
import java.sql.SQLException;   
import java.sql.Types;   
import java.util.List;   
  
import org.springframework.jdbc.core.RowMapper;   
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;   
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;   
  
import com.carefirst.edw.domain.ErrorListItem;   
  
public class ErrorListDetailDAOImpl extends NamedParameterJdbcDaoSupport implements ErrorListDAO {   
    private static final String LIST_SQL = "" +   
    "select et.srce_sys_cd, trunc(et.load_dt) as date_loaded, et.fld_nm, " +   
    "et.mtrcs_errs_linkg_id  " +   
    " from stage3i.err_tbl et where et.mtrcs_errs_linkg_id = to_number(4413) " +    
    " order by et.fld_nm, et.ent_key_id ";   
       
    public List getLoadList(String errorsId) {   
           
        MapSqlParameterSource map = new MapSqlParameterSource();   
        map.addValue("linkingId", errorsId, Types.VARCHAR);   
           
        List history = getNamedParameterJdbcTemplate().query(LIST_SQL, map, new ListRowMapper());    
           
        return history;   
    }   
  
    private class ListRowMapper implements RowMapper {   
        public Object mapRow(ResultSet rs, int arg1) throws SQLException {   
            ErrorListItem item = new ErrorListItem();   
            item.setSourceCd(rs.getString("srce_sys_cd"));   
            item.setLoadDate(rs.getDate("date_loaded"));   
            item.setErrorCount(rs.getInt("num_errors"));   
            item.setErrorDescr(rs.getString("fld_nm"));   
            item.setErrorsId(rs.getInt("mtrcs_errs_linkg_id"));   
            return item;   
        }   
    }   
}   
  
/*********** Controller **************/  
package com.carefirst.edw.web;   
  
import java.util.Date;   
import java.util.HashMap;   
import java.util.List;   
import java.util.Map;   
  
import javax.servlet.http.HttpServletRequest;   
import javax.servlet.http.HttpServletResponse;   
  
import org.apache.commons.logging.Log;   
import org.apache.commons.logging.LogFactory;   
import org.springframework.web.servlet.ModelAndView;   
import org.springframework.web.servlet.mvc.Controller;   
  
import com.carefirst.edw.dao.ErrorListDAO;   
  
public class ErrorListDetailController implements Controller {   
    protected final Log logger = LogFactory.getLog(getClass());   
    private ErrorListDAO dao = null;   
       
    public ModelAndView handleRequest(HttpServletRequest request,   
            HttpServletResponse response) throws Exception {   
        String errorId = request.getParameter("ERRORID");   
           
        List list = dao.getLoadList(errorId);   
           
        Map model = new HashMap();   
        model.put("list", list);   
        model.put("errorsId", errorId);   
           
        return new ModelAndView("error_detail", "model", model);   
    }   
  
    public void setErrorListDAO(ErrorListDAO dao) {   
        this.dao = dao;   
    }      
}   
  
  
/************ JSP ***********************/  
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"    
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">   
      
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>   
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>   
  
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">   
<head>   
    <title>Error details for load # [add # here] </title>   
    <link rel="stylesheet" href="css/tablesorter.css" type="text/css" id="" media="print, projection, screen" />   
    <link rel="stylesheet" href="css/thickbox.css" type="text/css" media="print,screen" />   
    <link rel="stylesheet" href="css/edw.css" type="text/css" media="print,screen" />   
  
    <script type="text/javascript" src="js/jquery-latest.js"></script>    
    <script type="text/javascript" src="js/jquery.tablesorter.js"></script>    
    <script type="text/javascript" src="js/thickbox.js"></script>    
    <script type="text/javascript" id="js">   
    $(document).ready(function()    
            {            
            $("#statusEDW").tablesorter();     
            }    
    );    
    </script>   
</head>   
<body>   
  
<h2>Error details </h2>   
  <a class="cancel"  rel="nofollow">   
    <span class="btn_lbg">   
      <span class="btn_rbg">Close</span>   
    </span>   
  </a>   
<table class="tablesorter" id="statusEDW">   
<thead>   
<tr>     
   <th># errors</th>   
   <th>Description</th>   
</tr>   
</thead>   
<tbody>   
<c:forEach var="errors" items="${model.list}">   
  <tr>   
    <td ><fmt:formatNumber type="number"> ${errors.errorCount}</fmt:formatNumber></td>   
    <td >${errors.errorDescr}</td>   
  </tr>   
</c:forEach>   
</tbody>   
</table>   
</body>   
</html>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you have pblm here
stage3i.err_tbl et
Can you tell why you mention .(dot)  after the stage3i
Can you give your err_tbl desc?

Author

Commented:
stage3i is the owner of the table, so it's needed to identify the table
err_tbl description

SRCE_SYS_CD          VARCHAR2(3)
ENT_KEY_ID                VARCHAR2(30)
STG_SEQNO                NUMBER(15)
FLD_NM                        VARCHAR2(30)
FLD_VAL                        VARCHAR2(30)
ERR_CD                      NUMBER(3)
ERR_SQLCODE         VARCHAR2(200)
LOAD_DT                     DATE Y SYSDATE
MTRCS_ERRS_LINKG_ID NUMBER(12)
 
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I re-built it and it worked, not sure what was wrong the first time, some typo that I wasn't able to discern.

Author

Commented:
dravid, thanks for the offer, I get jumpy and want to post things, but I didn't really solve my problem, but it doesn't exist anymore. I just re-built the screen with new files. I started by creating a 2nd screen just like the first, got it to work, then changed it one thing at a time.
I'm sure I'll be back.
:-) gd work .....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial