0

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.

madhead
  • 31,729
  • 16
  • 153
  • 201
user2737399
  • 81
  • 1
  • 4
  • 13
  • Not sure what your question is. If your approach does what you need then it is correct. If it does not, you need to explain what it does and what you expect it to do. – mustaccio May 08 '14 at 11:30
  • Hi,I want to retrieve the GroupId, GroupName, GroupTIN from DB2 ResultSets with input as tin,maxRecords and startIndex of xml datatype to DB2 stored procedure.Don't know as how to pass the input as xml datatype through java. – user2737399 May 08 '14 at 11:42
  • Did you try [the manual](http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_c0023302.html)? – mustaccio May 08 '14 at 15:39
  • yes and now figured out the way to do it. Thankyou! – user2737399 May 14 '14 at 13:08

0 Answers0