How to call stored procedures in Sybase correctly

4.7k Views Asked by At

I'm trying to call some legacy stored procedures on a Sybase Database (ASE 15) using Spring JDBC.

I've got it working pretty good but I can't figure out how to get the returned value from the proc.

This is the code I got:

@Repository
public class SybaseDao extends StoredProcedure {
    private static final String SQL = "db..proc_name";
    private static final String RETURN_VALUE = "rc";
    private static final String IN_DATA = "in_data";

    @Autowired
    public UpdateSybaseDao(DataSource dataSource) {
        super(dataSource, SQL);
        setFunction(true);
        declareParameter(new SqlOutParameter(RETURN_VALUE, NUMERIC));
        declareParameter(new SqlParameter(IN_DATA, NUMERIC));
        compile();
    }

    public void update(Integer inData) {
        Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(IN_DATA, inData);
        Map<String, Object> results = execute(inputs);

        Integer returnValue = (Integer) results.get(RETURN_VALUE);
        if (returnValue > 0) {
            // handle this error
        }
    }
}

The stored procedure isn't very important but it does an update and always returns 0. If I run the same query using some db tool I get 0 correctly.

The update works and the table is changed the return value is 3. To me that looks totally random.

Am I doing this the right way? Is there something I'm missing?

I'm using Sybase's proprietary driver jConnect 7 if that matters.

Thanks.

2

There are 2 best solutions below

0
On BEST ANSWER

Answering my own question.

I was in fact doing it right. What fooled me was the return value from another tool was wrong. I never thought that and it really didn't make sense.

Anyway... seems my approach works.

1
On

I'm not familiar enough with Spring to know if I'm right or not, but what happens if you change setFunction to false? Functions and stored procedures are two different animals, but I don't know if Spring differentiates between them.