Announcement Announcement Module
Collapse
No announcement yet.
Unable to use output parameter with jdbcTemplate.update / stored procedure... Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Unable to use output parameter with jdbcTemplate.update / stored procedure...

    I am trying to "approve" a timecard in our iseries SQL database, I have simplified our code here for test purposes/asking question here.

    JAVA code ...Dao.java:

    public char approve(String emplid, char out1)
    {
    JdbcTemplate jdbcTemplate=this.getJdbcTemplate();

    String sql = "CALL " + DATA_SOURCE + ".PROCEDURE(?,?) ";

    jdbcTemplate.update(sql, new Object[] { "01234", out1});

    return out1; // (This will be converted to an int eventually)
    }

    If I use only an input parameter (in this case I specified a number instead of using my parameter emplid) it works, but when I try to add the output parameter it generates an exception:

    O PreparedStatementCallback; uncategorized SQLException for SQL [CALL DATA_SOURCE.PROCEDURE(?,?) ]; SQL state [HY105]; error code [-99999]; Parameter type not valid.; nested exception is java.sql.SQLException: Parameter type not valid.

    I have spent actually a couple days googling, reading, trying to find an answer but have not been successful.

    I am thinking I somehow need to specify additional information about the output parameter? I was also reading something about needing variable names to match? Do I have to make sure that "out1" is the same name as used in my stored procedure?
    Last edited by Christopher.; Mar 20th, 2013, 04:57 PM.

  • #2
    Why not use SimpleJdbcCall or StoredProcedure classes? They give you ways to declare the type of the output parameter.

    Comment


    • #3
      Thank you, this is what I did that works:


      Code:
      public int AprroveTimecard(String EMPLID)
      {	
      	SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
      	.withoutProcedureColumnMetaDataAccess()
      	.withSchemaName("TESTDATA")
      	.withProcedureName("TESTPROC")
      	.declareParameters(				
      		new SqlParameter( "EMPLID", Types.CHAR ),
      		new SqlOutParameter("Errorcode", Types.CHAR ));
      		
      	Map<String, Object> result = new HashMap(2);
      		result.put("EMPLID", EMPLID);
      		// result.put("Errorcode", Errorcode);
      		
      	result = jdbcCall.execute(result);
      	
      	System.out.println("Errorcode: " + result.get("Errorcode"));
      
      	return 1;  // Will change this to return the Errorcode as an int...
      }
      It is working , but perhaps I might be doing some unnecessary steps, or my code may be non-optimal as I am new at this any further advice is welcomed.
      Last edited by Christopher.; Mar 21st, 2013, 03:10 PM.

      Comment

      Working...
      X