JdbcTemplate failed to get data from StoredProc by using column alias [SPR-7506]

[spring-projects-issues]

Liu, Yinwei David opened SPR-7506 and commented

Hi,

Here is a bug in JdbcTemplate which cannot get data from StoredProc by using column alias.

To reproduce the issue:
#1 Create a sybase stored proc:
CREATE PROCEDURE dbo.sp_test_column_alias
AS
BEGIN
select id as test_id, title as test_name from movies
END

#2 Run java code below
String sql = "sp_test_column_alias";
ResultSet rs = ((ResultSetWrappingSqlRowSet) jdbc.queryForRowSet(sql)).getResultSet();
rs.next();
System.out.println(rs.getString("test_name"));

#3 we will get an exception Invalid column name , it should use column label to get data instead of column name according to the method: ResultSet.getString(String columnLabel)

The issue is that Spring JDBCTemplate internal uses com.sun.rowset.CachedRowSetImpl to hold all data from a sp query, however, it fail to use label to get data.

David

---

Affects: 3.0.4

Referenced from: commits 1247d20, 90636f6

[spring-projects-issues]

Chris Beams commented

Take a look at this forum issue - http://forum.springsource.org/showthread.php?t=57537. Thomas, perhaps you can provide more information as well?

[spring-projects-issues]

Chris Beams commented

Reassigning - @Thomas, see my previous comment.

[spring-projects-issues]

Thomas Risberg commented

Well, the problem is really in the ResultSet implementation of the CachedRowSetImpl. It has not been updated to use the column label. Since JDBC 4.0 it has been clarified that any methods using a String to identify the column should be using the column label, but it looks like they forgot to fix the CachedRowSetImpl.

I'm thinking of working around this by modifying the ResultSetWrappingSqlRowSet to look up column index based on the label using the ResultSetMetaData. That would make this work as long as you access the row data through the SqlRowSet instead of retrieving the ResultSet and using that directly.

[spring-projects-issues]

Thomas Risberg commented

Added metadata lookup for column index based on column label. You can now do this:

String sql = "sp_test_column_alias";
SqlRowSet srs = jdbcTemplate.queryForRowSet(sql);
while (srs.next()) {
  System.out.println(srs.getString("test_name"));
}