call a Stored Procedure in MS SQL Server 2000?

[plucas]

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:

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

[trisberg]

Try putting the

          declareParameter(new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));

as the first parameter declaration.

[plucas]

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

[trisberg]

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.

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

[plucas]

Perfect - Thanks!

~plucas

[jalmahayni]

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