Announcement Announcement Module
No announcement yet.
Strange issue with a stored procedure call Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Strange issue with a stored procedure call

    Hi all,

    I am using Spring to access two different stored procedures from Intersystems Cache database.

    One of the stored procedure returns return value and a out parameter. The second stored procedure returns only out parameter.

    The call to first stored procedure works fine by returning data in the out parameter.
    But the second procedure does not work - It returns empty out parameter though there is data in the database. This is proved by making a direct jdbc connection (with out using Spring) and calling this stored procedure - it shows data in the out parameter.

    I tried using Spring stored procedure as well as simplejdbccall approachs but in vain. I tried out different methods related to the return value and/or out parameter but in vain.

    It is baffling as why it works thru straight jdbc connection and not thru Spring api.

    Any input is HIGHLY appreciated.

    Thanks in advance.

    Below are the two spring api approaches that are failing.

    Spring storedproc approach:

        class XYZStoredProcedure extends StoredProcedure
          private static final String IN_PARAM = "InParams";
          public XYZStoredProcedure(DataSource dataSource, String procName)
            super(dataSource, procName);
            declareParameter(new SqlReturnResultSet("XYZData", new RowMapper() {
              public Object mapRow(ResultSet resultSet, int i) throws SQLException {
              Map data = new HashMap(1);
              data.put("XYZData", resultSet.getObject(1));
              return data;
            declareParameter(new SqlParameter(IN_PARAM, Types.VARCHAR));
            declareParameter(new SqlOutParameter("XYZData", Types.VARCHAR));
          public Map execute(String inParams)
            Map inMap = new HashMap();
            inMap.put(IN_PARAM, inParams);
            return super.execute(inMap);
    //calling the stored proc
    String inParams = x1;
          XYZStoredProcedure proc = new XYZStoredProcedure(xyzDataSource, "storedproc_name");
          Map out = proc.execute( inParams );
          ArrayList resultList = ( ArrayList )out.get( "#result-set-1" );
          Map resultMap = ( Map )resultList.get( 0 );
          String result = ( String )resultMap.get( "XYZColumn" );
    Spring simplejdbccall approach:

           SimpleJdbcCall proc = new SimpleJdbcCall( xyzDataSource ).withProcedureName( "storedproc_name" )
                            .withoutProcedureColumnMetaDataAccess().useInParameterNames( "InParams" ).declareParameters(
                                            new SqlParameter( "InParams", Types.VARCHAR ) );
            String inParams = x1;
            MapSqlParameterSource in = new MapSqlParameterSource();
            in.addValue( "InParams", inParams );
            in.addValue("_sp_ret_val", "test");
            proc.setAccessCallParameterMetaData( false );
            //proc.setReturnValueRequired( true );
            Map out = proc.execute( in );
            ArrayList resultList = ( ArrayList )out.get( "#result-set-1" );
            Map resultlMap = ( Map )resultList.get( 0 );
            String result = ( String )resultMap.get( "XYZColumn" );