Announcement Announcement Module
Collapse
No announcement yet.
How to call a stored procedure that with in/out parameters? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to call a stored procedure that with in/out parameters?

    http://forum.springframework.org/vie...+sql+procedure

    Follwing the way of the this post, I had have a try of the Stored procedure. My DB is also MS SQL . But I come up with an exception.

    java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]过程 'PocketSpring_Procedure' 需要参数 '@userid',但未提供该参数。(The chinese part said that -----
    'PocketSpring_Procedure' needs argument '@userid', but this argument is not provided.


    Procedure


    Code:
    CREATE PROCEDURE [dbo].[PocketSpring_Procedure] 
    	@userid 	VARCHAR(20)
    AS
    
    
    BEGIN
    SELECT id  from Book WHERE id = @userid
    END
    GO

    Test Code

    Code:
    package pocketexample.spring;
    
    import org.springframework.jdbc.core.RowCallbackHandler;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.jdbc.object.StoredProcedure;
    
    import javax.sql.DataSource;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Types;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    
    public class PocketSpring_StoredProcedure
    {
        public static void main(String[] args)
        {
            PocketSpring_StoredProcedure t = new PocketSpring_StoredProcedure();
            t.test();
            System.out.println("Done!");
        }
    
        void test()
        {
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            ds.setUrl("jdbc:microsoft:sqlserver://localhost;DatabaseName=voice;SelectMethod=cursor");
            ds.setUsername("sa");
            ds.setPassword("");
            PocketStoredProcedure sproc = new PocketStoredProcedure(ds);
            Map res = sproc.execute();
            printMap(res);
        }
    
        private class PocketStoredProcedure extends StoredProcedure
        {
            public  final String SQL = "dbo.PocketSpring_Procedure";
    
            public PocketStoredProcedure(DataSource ds)
            {
                setDataSource(ds);
                setFunction(true);
                setSql(SQL);
    
                declareParameter(new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));
                declareParameter(new SqlParameter("userid", Types.VARCHAR));
    
                compile();
            }
    
            private class RowCallbackHandlerImpl implements RowCallbackHandler {
               public void processRow(ResultSet rs) throws SQLException {
            //      count++;
               }
            }
    
            public Map execute()
            {
                Map in = new HashMap();
                in.put("userid", "111111");
                Map out = execute(in);
                return out;
            }
        }
    
        private static void printMap(Map r)
        {
            Iterator i = r.entrySet().iterator();
    
            while (i.hasNext())
            {
                System.out.println((String) i.next().toString());
            }
        }
    }
    If I move the argument in the stored procedure.Every thing goes well. It will print such an info in the console.
    "rs=ResultSet returned from stored procedure was processed."
    How to call a stored procedure that with in/out parameters?
    Thx in advance.
Working...
X