Announcement Announcement Module
Collapse
No announcement yet.
call a Stored Procedure in MS SQL Server 2000? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • call a Stored Procedure in MS SQL Server 2000?

    I found 3 useful articles on the subject:

    http://www.springframework.org/docs/...toredProcedure

    http://monkeymachine.co.uk/spring/xr...TestSuite.html

    http://forum.springframework.org/vie...103&highlight=


    I have come very close to implementing my own call to a SP... but get the following error:
    ResultSet returned from stored procedure but a corresponding SqlReturnResultSet parameter was not declared

    here is my code:

    Code:
    package com.pcpower.amss.jdbc;
    
    
    import java.sql.Types;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    
    import javax.sql.DataSource;
    
    import org.springframework.jdbc.core.SqlParameter;
    //import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.datasource.*;
    import org.springframework.jdbc.object.StoredProcedure;
    import org.springframework.jdbc.core.SqlReturnResultSet;
    import org.springframework.jdbc.core.RowCallbackHandler;
    import org.springframework.jdbc.core.RowMapper;
    
    
    
    public class SP_PRAZOS_DISPONIVEIS_VENDA {
    
        public static void main(String[] args)  {
            
            System.out.println("iniciar o teste de chamar o SP!");
            SP_PRAZOS_DISPONIVEIS_VENDA t = new SP_PRAZOS_DISPONIVEIS_VENDA();
            t.test();
            System.out.println("Done!");
            
        }
        
        void test() {
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName("net.sourceforge.jtds.jdbc.Driver");
            ds.setUrl("jdbc:jtds:sqlserver://10.100.0.81:1470/NEWCON_PLUS");
            ds.setUsername("sa");
            ds.setPassword("master");
    
            MyStoredProcedure sproc = new MyStoredProcedure(ds);
            Map res = sproc.execute();
            printMap(res);
                    
        }
    
        private class MyStoredProcedure extends StoredProcedure {
            public static final String SQL = "PRAZOS_DISPONIVEIS_VENDA";
            private int count = 0;
    
            public MyStoredProcedure(DataSource ds) {
                setDataSource(ds);
                //setFunction(true);
                setSql(SQL);
                
                declareParameter(new SqlParameter("Codigo_Tipo_Grupo", Types.CHAR));
                declareParameter(new SqlParameter("Codigo_Bem", Types.INTEGER));
                declareParameter(new SqlParameter("Data_Nascimento", Types.DATE));
                declareParameter(new SqlParameter("Listar_Apenas_Grupo_Exclusivo", Types.CHAR));
                declareParameter(new SqlParameter("Codigo_Representante", Types.VARCHAR));
                declareParameter(new SqlParameter("Situacao_Grupo", Types.CHAR));
                declareParameter(new SqlParameter("Pessoa", Types.CHAR));
                declareParameter(new SqlParameter("Ordem_Pesquisa", Types.CHAR));
                declareParameter(new SqlParameter("Codigo_Filail_Comercial", Types.VARCHAR));
                declareParameter(new SqlParameter("Rateia", Types.CHAR));
                
     			declareParameter(new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));
     	        compile();
            }
        	public int getCount() {
        		return count;
        	}
           
            private class RowCallbackHandlerImpl implements RowCallbackHandler {
            	public void processRow(ResultSet rs) throws SQLException {
            		count++;
            	}
            }
    
            public Map execute() {
            	Map in = new HashMap();
            	in.put("Codigo_Tipo_Grupo", "IM");
                in.put("Codigo_Bem", new Integer(1));
                in.put("Data_Nascimento", new Date("01/01/1980"));
                in.put("Listar_Apenas_Grupo_Exclusivo", "N");
                in.put("Codigo_Representante", "0002");
                in.put("Situacao_Grupo", "X");
                in.put("Pessoa", "F");
                in.put("Ordem_Pesquisa", "G");
                in.put("Codigo_Filail_Comercial", "001");
                in.put("Rateia", "S");
    
                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());  
            }
        }
    }
    I can't find out how to get a SqlReturnResultSet assigned to this SP. Any help/hints would be greatly appreciated!

    TIA,
    ~plucas

  • #2
    Try putting the
    Code:
              declareParameter(new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));
    as the first parameter declaration.

    Comment


    • #3
      Thank you, Thomas.

      That appears to be retrieving some kind of results without error now.

      I looked in the debugger to see what exactly was being returned, and it appears as if "rs" is not a ResultSet (or even an SqlReturnResultSet), but instead is a string with the value "ResultSet returned from stored procedure was processed."

      My goal is to retrieve the ResultSet returned from the Stored Procedure as an object that I can traverse and display results in a table.

      Thanks for your help so far!
      ~plucas

      Comment


      • #4
        The easiest way is to use a RowMapper that maps the columns for each row to a custom object. This will give "rs" a List of the custom objects with one entry for each row returned.

        Code:
        declareParameter(new SqlReturnResultSet("rs", new RowMapper() {
        			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        				MyObject o = new MyObject();
        				o.setXxxx(rs.getString(1));
        				// add more mappings here
        				return o;
        			}
        		}));

        Comment


        • #5
          Perfect - Thanks!

          ~plucas

          Comment


          • #6
            Hello
            I am trying to also call a Stored Procedure in MS SQL Server 2000 and tried to follow the above example as close as possible but I couldn't get it to run. My stored proc takes in one param as a varchar and returns a ResultSet. How exactly do I want to setup the inparam and get the resultset as an output to display to users. Please help.
            bellow is my example class:


            import java.sql.ResultSet;
            import java.sql.SQLException;
            import java.sql.Types;
            import java.util.HashMap;
            import java.util.Iterator;
            import java.util.Map;

            import javax.sql.DataSource;

            import org.springframework.jdbc.core.RowMapper;
            import org.springframework.jdbc.core.SqlParameter;
            import org.springframework.jdbc.core.SqlReturnResultSet;
            import org.springframework.jdbc.datasource.DriverManagerD ataSource;
            import org.springframework.jdbc.object.StoredProcedure;

            import com.canvas.itlm.domain.MyObject;

            public class OpenShipmentStoredProc{

            public static void main(String [] args){
            OpenShipmentStoredProc t = new OpenShipmentStoredProc();
            t.test();
            System.out.println("Done!");
            }

            void test(){
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName("net.sourceforge.jtds.jdbc.D river");
            ds.setUrl("jdbc:jtds:sqlserver://IBMT4441/JKITLM;");
            ds.setUsername("sa");
            ds.setPassword("master");

            MyStoredProcedure sproc = new MyStoredProcedure(ds);
            Map res = sproc.execute();
            printMap(res);
            }

            private class MyStoredProcedure extends StoredProcedure{
            public static final String SQL = "sp_OpenShipments";

            public MyStoredProcedure(DataSource ds){
            setDataSource(ds);
            setFunction(true);
            setSql(SQL);

            declareParameter(new SqlParameter("vendorID", Types.VARCHAR));
            declareParameter(new SqlReturnResultSet("rs", new RowMapper(){
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
            MyObject o = new MyObject();
            o.setXxxx(rs.getString(1));
            o.setYyyy(rs.getString(2));
            // add more mappings here
            return o;
            }
            }));

            compile();
            }

            public Map execute(){
            Map inParams = new HashMap();
            inParams.put("vendorID", "30");

            Map out = execute(inParams);
            return out;
            }
            }

            private static void printMap(Map r){
            Iterator i = r.entrySet().iterator();
            while(i.hasNext()){
            System.out.println(i.next().toString());
            }
            }
            }

            Comment

            Working...
            X