call a Stored Procedure in MS SQL Server 2000?

[plucas]

I found 3 useful articles on the subject:

http://www.springframework.org/docs/reference/jdbc.html#jdbc-StoredProcedure

http://monkeymachine.co.uk/spring/xref-test/org/springframework/jdbc/object/StoredProcedureTestSuite.html

http://forum.springframework.org/viewtopic.php?p=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.Driver");
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());
}
}
}