passing parameter to stored procedure

[kasim]

private static void byStoredProcedure(JdbcTemplate jt2) { 
        CallableStatementCallback cb = new CallableStatementCallback(){ 
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { 
                cs.execute(); 
                return null; 
            } 
             
        }; 
         
        jt2.execute("{cdo_sp(?,?)}", cb); 
         
    } 

Here i need to pass the parameter to SP, how can i pass it?

[karldmoore]

Why don't you using use the StoredProcedure class?
http://www.springframework.org/docs/...toredProcedure

[kasim]

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:

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?

[karldmoore]

The TitlesAfterDateStoredProcedure at the end of that section has an example. The key is the name is the name of the input parameter.

[trisberg]

You can access the paramaters in the CallableStatementCallback just as you would using plain JDBC. Here is an example:

    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;
        }
    });

[kasim]

CallableStatementCallback cb = new CallableStatementCallback(){
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                cs.registerOutParameter(1, java.sql.Types.VARCHAR);
                cs.setInt(2, 4455);                
                cs.execute();
                return null;
            }
            
        };
        
        jt2.execute("{cdo_sp(?,?)}", cb); 

error msg is:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{cdo_sp(?,?)}]; SQL state [null]; error code [17034]; Non supported SQL92 token at position: 7: cdo_sp; nested exception is java.sql.SQLException: Non supported SQL92 token at position: 7: cdo_sp
Caused by: java.sql.SQLException: Non supported SQL92 token at position: 7: cdo_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.NoTransactionException: No 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) 

[kasim]

private static void byStoredProcedure(JdbcTemplate jt2) {
        
        CallableStatementCallback cb = new CallableStatementCallback(){            
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {                
                cs.setString(1, "jjoopp");
                cs.setInt(2, 4455);                
                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?

[karldmoore]

As far as I can see in the code, you create a CallableStatementCallback but you don't actually do anything with it.

[trisberg]

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:

        jt2.execute("{call cdo_sp(?,?)}", cb);

[kasim]

when i use this

jt2.execute("{call cdo_sp(?,?)}", cb); 

i am getting above exception