Announcement Announcement Module
Collapse
No announcement yet.
Issues with SQL statement using MappingSqlQuery : please help.. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Issues with SQL statement using MappingSqlQuery : please help..

    When I send more than one value in IN bloc I am getting error.With one value it is fine.

    SQL :
    Code:
    select distinct b.major_dept from xs_vazwhse a, xs_vazitem b where a.item=b.item and a.whse in ('91') and a.vend = ? and b.major_dept = ? and b.minor_dept in (?) order by  b.major_dept
    Java :
    Code:
    	
    	for (Iterator iterator = minor.iterator(); iterator.hasNext();) {
    					Minor minorList = (Minor) iterator.next();
    					logger.info("Selected Minor ID [" + minorList.getSelectedMinorID()
    							+ "]");
    					
    					//minorBuilder.append("'");			
    					minorBuilder.append(minorList.getSelectedMinorID());
    					//minorBuilder.append("'");
    					
    					if(minor.size() != count) {
    						minorBuilder.append(",");
    					}
    					count++;
    				}
    output is : 14,15

    JDBC decalration :
    Code:
    private class GetMajorMinorItemQueryByVendorMajorMinor extends MappingSqlQuery {
    		
    		private GetMajorMinorItemQueryByVendorMajorMinor(DataSource dataSource, String sql){
    			setDataSource(dataSource);
    			setSql(sql);
    			declareParameter(new SqlParameter(Types.VARCHAR));
    			declareParameter(new SqlParameter(Types.VARCHAR));
    			declareParameter(new SqlParameter(Types.VARCHAR));
    			
    						
    			compile();
    		}
    
    		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                         // some code 
    			return majorMinorItems;			
    		}
    	}
    Method Call :
    Code:
    data =getMajorMinorItemQueryByVendorMajorMinor.execute(new Object[] { vendor,major,minorBuilder.toString() });
    With above code I am getting below exceptions :

    Exception getMajorMinorItems() : PreparedStatementCallback; uncategorized SQLException for SQL [select distinct a.vend, b.major_dept, b.minor_dept, a.item from xs_vazwhse a, xs_vazitem b where a.item=b.item and a.whse in ('91') and a.vend = ? and b.major_dept = ? and b.minor_dept in (?) order by a.vend, b.major_dept, b.minor_dept, a.item]; SQL state [IX000]; error code [-1213]; Character to numeric conversion error; nested exception is java.sql.SQLException: Character to numeric conversion error
    org.springframework.jdbc.UncategorizedSQLException :
    PreparedStatementCallback; uncategorized SQLException for SQL [select distinct a.vend, b.major_dept, b.minor_dept, a.item from xs_vazwhse a, xs_vazitem b where a.item=b.item and a.whse in ('91') and a.vend = ? and b.major_dept = ? and b.minor_dept in (?) order by a.vend, b.major_dept, b.minor_dept, a.item]; SQL state [IX000]; error code [-1213]; Character to numeric conversion error; nested exception is java.sql.SQLException: Character to numeric conversion error
    Caused by: java.sql.SQLException: Character to numeric conversion error
    at com.informix.util.IfxErrMsg.getSQLException(IfxErr Msg.java:355)
    at com.informix.jdbc.IfxSqli.addException(IfxSqli.jav a:3086)
    at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.jav a:3396)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java :2259)
    Last edited by akbspringuser; Jun 4th, 2010, 02:08 PM.
Working...
X