Exception in executing SimpleJdbcCall for fetching resultset/REF cursor

[Anadi Misra]

Hi,

I get null pointer exceptions while executing a SimpleJdbcCall(Spring 2.5.4) based implementation of calling stored procedures, posted the same query in sppring forums but haven't got any help yet so I am posting it here again.

Hi

I was trying a bit modified usage of this class for a procedure that accepts one input and returns all rows of the table in question. Some what similar to an example given for the same in spring reference the only difference being that the procedure i call has an input as well.

The stored procedure being called in my code is as follows

  
create or replace procedure "GET_EVENT_BY_TITLE"
(in_title IN VARCHAR2,
out_id OUT NUMBER,
out_title OUT VARCHAR2,
out_location OUT VARCHAR2,
out_date OUT DATE,
out_time OUT VARCHAR2)
is
begin
SELECT id, title, location, eventdate, time 
INTO out_id, out_title, out_location, out_date, out_time
FROM EVENT where title like in_title;
end;

I used the SimpleJdbcCall as follows

  
public class EventDaoImpl implements EventDao {
 
private SimpleJdbcTemplate simpleJdbcTemplate;
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall jdbcCall;

public EventDaoImpl() {}
 
public List<Event> getEventByTitle(String title) {
 
jdbcTemplate = new JdbcTemplate(getDataSource());
simpleJdbcTemplate = new SimpleJdbcTemplate(getDataSource());
 
jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName(
"GET_EVENT_BY_TITLE")
.useInParameterNames("IN_TITLE").returningResultSet("events",
ParameterizedBeanPropertyRowMapper.newInstance(Event.class));
 
SqlParameterSource in = new MapSqlParameterSource().addValue(
"IN_TITLE", title);
 
Map resultMap = jdbcCall.execute(in);
 
List<Event> result = (List<Event> :wink:  resultMap.get("events");
 
return result;
 
}
 
public static DataSource getDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
dataSource.setUrl("jdbc :-o	 racle:thin:@dev:1521:XE");
dataSource.setUsername("appuser");
dataSource.setPassword("password");
 
return dataSource;
}
 
}

I got the following exception when in run the class

 
Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GET_EVENT_BY_TITLE(?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPUSER.GET_EVENT_BY_TITLE", line 10
ORA-06512: at line 1
 
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:364)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:338)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:164)
at EventDaoImpl.getEventByTitle(EventDaoImpl.java:39)
at RunMain.main(RunMain.java:10)
Caused by: java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPUSER.GET_EVENT_BY_TITLE", line 10
ORA-06512: at line 1
 
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:987)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:936)
... 6 more

[Anadi Misra]

I changed the procedure definition as follows and then retried(am not that good with stored procedures )

  
create or replace procedure "GET_EVENT_BY_TITLE"
(in_title IN VARCHAR2,
out_id OUT NUMBER,
out_title OUT VARCHAR2,
out_location OUT VARCHAR2,
out_date OUT DATE,
out_time OUT VARCHAR2)
is
c_id NUMBER;
c_title VARCHAR2(100);
c_location VARCHAR2(100);
c_eventdate DATE; 
c_time CHAR(5);
begin
for c in (SELECT id, title, location, eventdate, time 
          INTO c_id, c_title, c_location, c_eventdate, c_time
          FROM EVENT where title like in_title)
LOOP
out_id := c_id;
out_title := c_title;
out_location := c_location;
out_date := c_eventdate;
out_time := c_time;
END LOOP;
end;

I modified the EventDaiImpl class to add print statements, the previous exception did not occur as expected but now i get Null pointer exception

  
private SimpleJdbcTemplate simpleJdbcTemplate;
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall jdbcCall;

public EventDaoImpl() {}
 
public List<Event> getEventByTitle(String title) {
 
jdbcTemplate = new JdbcTemplate(getDataSource());
simpleJdbcTemplate = new SimpleJdbcTemplate(getDataSource());
 
jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName(
"GET_EVENT_BY_TITLE")
.useInParameterNames("IN_TITLE").returningResultSet("events",
ParameterizedBeanPropertyRowMapper.newInstance(Event.class));
 
SqlParameterSource in = new MapSqlParameterSource().addValue(
"IN_TITLE", title);
 
Map resultMap = jdbcCall.execute(in);
 
List<Event> result = (List<Event> :wink:  resultMap.get("events");

//added this for loop; here i get the Null Pointer
for(int i = 0;i<result.size();i++)
System.out.println("Event Name :"+result.get(i).getTitle());
 
return result;
 
}
 
public static DataSource getDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
dataSource.setUrl("jdbc :-o	 racle:thin:@dev:1521:XE");
dataSource.setUsername("appuser");
dataSource.setPassword("password");
 
return dataSource;
}
 
}

the debug mode shows that the resultMap has only Null Values
an extract is as follows

 
 [OUT_ID=null,null,null,null,null]

Please point out whats done wrong here, something in the class or the procedure may be?

[Mark Spritzler]

Um, don't you have to tell it all the parameters, not just the first one. It needs to also be told the out parameters. I think.

Don't you need more useOutParameterNames?

.useInParameterNames("IN_TITLE").returningResultSet("events",ParameterizedBeanPropertyRowMapper.newInstance(Event.class));

Mark

[Anadi Misra]

Hi,

The SimpleJdbcCall class does not require that we explicitly tell all the parameters the code is like staright from the spring reference for version 2.5.5 the only difference is thatthey gave a nexample of a procedure with no input param and returning a result set/REF Cursor.

We have many procedures in our system which take a parameter or two and return a result set, so i was trying this modification to check how it works with this class

[Anadi Misra]

Hi,

This more strange than I had imagined, i did some small modifications in the code to figure out why is it going wrong.

  
public class EventDaoImpl implements EventDao {
 
private SimpleJdbcTemplate simpleJdbcTemplate;
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall jdbcCall;
 
public EventDaoImpl() {}
 
public List getEventByTitle(String title) {
jdbcTemplate = new JdbcTemplate(getDataSource());
simpleJdbcTemplate = new SimpleJdbcTemplate(getDataSource());
jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName(
"GET_EVENT_BY_TITLE").useInParameterNames("IN_TITLE")
.returningResultSet(
"events",
ParameterizedBeanPropertyRowMapper
.newInstance(Event.class));
 
SqlParameterSource in = new MapSqlParameterSource().addValue(
"IN_TITLE", title);
 
Map resultMap = jdbcCall.execute(in);
//return (List) resultMap.get("events");
        Set keySet = resultMap.keySet();
Object[] keys = keySet.toArray();
List result = new ArrayList();
int i = 0;
while (keySet.iterator().hasNext()) {

System.out.println(keys[i]+": ");
 
result.add((Event) resultMap.get(keys[i]));
i++;

}
return result;

I am totally confused now on what is happening here, i was expecting a sort of Map with ID as the key and the domain objects (Event) and values. What is happening is that all the out params in the procedure are keys and I end up getting the last row of the result only in this map.
[ August 29, 2008: Message edited by: Anadi Misra ]