I'm completely newbie to Stored Procedure. Got a requirement like the Input type to be passed is of XML datatype to DB2 stored procedure and output as ResultSet.
//Stored Procedure:
CREATE PROCEDURE GetGrpInfo (IN inpdoc XML)
DYNAMIC RESULT SETS 1
P1: BEGIN
Declare tin VARCHAR(9);
Declare max_records INT;
Declare start_index INT;
DECLARE rcount INT;
DECLARE total_count INT;
..........
..........
//Java Code:
private final static String myXML = "inpdoc";
private final static String SpOutput = "RESULT";
@Autowired
public void setDataSource(DataSource db2DataSource) {
String procedureName = "schemaname.GetGrpInfo";
this.db2DataSource = db2DataSource;
this.jdbcCall = new SimpleJdbcCall(this.db2DataSource)
.withProcedureName(procedureName)
.withoutProcedureColumnMetaDataAccess()
.useInParameterNames(myXML)
.declareParameters(new SqlParameter(myXML, Types.SQLXML))
.returningResultSet("GroupInfo", new BeanPropertyRowMapper<GroupInfo>(){
public GroupInfo mapRow(ResultSet rs, int rowNum)
throws SQLException {
GroupInfo gInfo = new GroupInfo();
gInfo.setGroupId(rs.getString("GroupID"));
gInfo.setGroupName(rs.getString("GroupName"));
gInfo.setGroupTaxId(rs.getString("GroupTIN"));
return gInfo;
}
});
}
String input1 = "<Input><TIN>"+tin+"</TIN><MaxRecords>"+max_records+" </MaxRecords><StartIndex>"+start_index+"</StartIndex></Input>";
SqlParameterSource in = new MapSqlParameterSource().addValue(myXML, input1);
Map result = jdbcCall.execute(in);
return (String)result.get("GroupInfo");
Not sure whether use of returningResultSet is good at here and also Types.SQLXML. Tried with various formats but not sure whether my approach is correct.