Announcement Announcement Module
Collapse
No announcement yet.
passing parameter to stored procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • passing parameter to stored procedure

    PHP Code:

    private static void byStoredProcedure(JdbcTemplate jt2) { 
            
    CallableStatementCallback cb = new CallableStatementCallback(){ 
                public 
    Object doInCallableStatement(CallableStatement csthrows SQLExceptionDataAccessException 
                    
    cs.execute(); 
                    return 
    null
                } 
                 
            }; 
             
            
    jt2.execute("{cdo_sp(?,?)}"cb); 
             
        } 
    Here i need to pass the parameter to SP, how can i pass it?

  • #2
    Why don't you using use the StoredProcedure class?
    http://www.springframework.org/docs/...toredProcedure
    Last edited by karldmoore; Aug 29th, 2007, 10:52 AM.

    Comment


    • #3
      Originally posted by karldmoore View Post
      Why don't you using use the StoredProcedure class?
      http://www.springframework.org/docs/...toredProcedure
      there is no option or example given for inputing the parameter. How can input the parameter here? given code in the example is:

      PHP Code:

      public Map execute() {
                  
      // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
                  
      return execute(new HashMap());
              } 
      Map map = new HashMap();
      map.put("name","abc");
      map.put("id","12345");

      where name and id is my table columns.
      is it just creating a hashmap and puting the input values as i shown above? is it a correct approach?
      Last edited by kasim; Jul 3rd, 2007, 09:42 AM.

      Comment


      • #4
        The TitlesAfterDateStoredProcedure at the end of that section has an example. The key is the name is the name of the input parameter.
        Last edited by karldmoore; Aug 29th, 2007, 10:52 AM.

        Comment


        • #5
          You can access the paramaters in the CallableStatementCallback just as you would using plain JDBC. Here is an example:
          Code:
              new CallableStatementCallback() {
                  public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                      cs.setInt(1, 123);
                      cs.registerOutParameter(2, java.sql.Types.VARCHAR);
                      cs.execute();
                      String returnValue = cs.getString(1);
                      return returnValue;
                  }
              });

          Comment


          • #6
            PHP Code:
            CallableStatementCallback cb = new CallableStatementCallback(){
                        public 
            Object doInCallableStatement(CallableStatement csthrows SQLExceptionDataAccessException {
                            
            cs.registerOutParameter(1java.sql.Types.VARCHAR);
                            
            cs.setInt(24455);                
                            
            cs.execute();
                            return 
            null;
                        }
                        
                    };
                    
                    
            jt2.execute("{cdo_sp(?,?)}"cb); 
            error msg is:
            PHP Code:


            org
            .springframework.jdbc.UncategorizedSQLExceptionCallableStatementCallbackuncategorized SQLException for SQL [{cdo_sp(?,?)}]; SQL state [null]; error code [17034]; Non supported SQL92 token at position7cdo_spnested exception is java.sql.SQLExceptionNon supported SQL92 token at position7cdo_sp
            Caused by
            java.sql.SQLExceptionNon supported SQL92 token at position7cdo_sp
                at oracle
            .jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
                
            at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
                
            at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
                
            at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java:201)
                
            at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:121)
                
            at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:69)
                
            at oracle.jdbc.driver.OracleConnection.nativeSQL(OracleConnection.java:1181)
                
            at oracle.jdbc.driver.OracleStatement.expandSqlEscapes(OracleStatement.java:6412)
                
            at oracle.jdbc.driver.OracleStatement.parseSqlKind(OracleStatement.java:6401)
                
            at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:152)
                
            at oracle.jdbc.driver.OracleCallableStatement.<init>(OracleCallableStatement.java:77)
                
            at oracle.jdbc.driver.OracleCallableStatement.<init>(OracleCallableStatement.java:48)
                
            at oracle.jdbc.driver.OracleConnection.privatePrepareCall(OracleConnection.java:1134)
                
            at oracle.jdbc.driver.OracleConnection.prepareCall(OracleConnection.java:988)
                
            at org.springframework.jdbc.core.JdbcTemplate$SimpleCallableStatementCreator.createCallableStatement(JdbcTemplate.java:1217)
                
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:870)
                
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:904)
                
            at com.spring.jdbc.test.TestJdbcTemplate.byStoredProcedure(TestJdbcTemplate.java:63)
                
            at com.spring.jdbc.test.TestJdbcTemplate.main(TestJdbcTemplate.java:44)
            Exception in thread "main" org.springframework.transaction.NoTransactionExceptionNo transaction aspect-managed TransactionStatus in scope
                at org
            .springframework.transaction.interceptor.TransactionAspectSupport.currentTransactionStatus(TransactionAspectSupport.java:106)
                
            at com.spring.jdbc.test.TestJdbcTemplate.main(TestJdbcTemplate.java:47

            Comment


            • #7
              PHP Code:
              private static void byStoredProcedure(JdbcTemplate jt2) {
                      
                      
              CallableStatementCallback cb = new CallableStatementCallback(){            
                          public 
              Object doInCallableStatement(CallableStatement csthrows SQLExceptionDataAccessException {                
                              
              cs.setString(1"jjoopp");
                              
              cs.setInt(24455);                
                              
              boolean res cs.execute("{cdo_sp(?,?)}");
                              
              System.out.println(res);
                              return 
              null;
                          }
                          
                      }; 
              i have changed the code as above error is gone now. But flow is not going inside the doInCallableStatement method. why?

              Comment


              • #8
                As far as I can see in the code, you create a CallableStatementCallback but you don't actually do anything with it.
                Last edited by karldmoore; Aug 29th, 2007, 10:52 AM.

                Comment


                • #9
                  You are not calling anything on the JdbcTemplate in the last examlpe, just declaring a callback. Go back to the previous version but change your stored procedure declaration string to include the word call, like:
                  Code:
                          jt2.execute("{call cdo_sp(?,?)}", cb);

                  Comment


                  • #10
                    when i use this
                    PHP Code:
                    jt2.execute("{call cdo_sp(?,?)}"cb); 
                    i am getting above exception

                    Comment


                    • #11
                      hi,
                      will this help?

                      here's SP
                      Code:
                      public class SpGetSqlParams extends StoredProcedure {
                      
                          Map  results = new HashMap();
                      
                          SpGetSqlParams(JdbcTemplate template,
                                        String name) {
                              super(template, name);
                      
                              declareParameter(new SqlReturnResultSet("rs", new RowMapper() {
                                  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                                      Map map = new HashMap();
                                      map.put(SPConstants.PARM_ID, rs.getString(1));
                                      map.put(SPConstants.PARM_NAM, rs.getObject(2));
                                      map.put(SPConstants.PARM_VAL, rs.getObject(3));
                                      map.put(SPConstants.DAT_TYP, rs.getObject(4));
                                      return map;
                                  }}));
                              declareParameter(new SqlParameter(SPConstants.FLOW_TASK_ID,
                                                                  Types.INTEGER));
                      
                              
                              compile();
                           }
                      
                          public Map execute(Map inParams) {
                              // populate inParams
                              return super.execute(inParams);
                          }
                      
                      }
                      and here's code that is calling it

                      Code:
                      StoredProcedure sp = spFactory.getStoredProcedure("spGetSqlParms");
                      Map map = new HashMap();
                      map.put(SPConstants.FLOW_TASK_ID, 1);
                      Map results = sp.execute(map);
                      System.err.println("RETURNED MAP IS:" + results);
                      there's an IN parameter as well as a result set returned...


                      hth
                      marco

                      Comment

                      Working...
                      X