How to call stored procedures in Sybase correctly

Question

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.

score 0 · Answer 1 · answered Nov 10 '11 at 15:35

0

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.

answered Nov 10 '11 at 15:35

Dave

4,546
2
38
59

I'm more at home with MySQL and there functions and procs are very different, but in sybase I think all procs can return values and I'm not even sure there are functions. I think it is the same in Sybase. Anyway, with setFuncation(false) there is no return value at all. – Andreas Wederbrand Nov 10 '11 at 21:53

score 0 · Accepted Answer · answered Nov 11 '11 at 11:31

0

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.

answered Nov 11 '11 at 11:31

Andreas Wederbrand

38,065
11
68
78

How to call stored procedures in Sybase correctly

2 Answers2