Can't declare a Sybase Cursor for StoredProcedures using Spring

[Martijn Verburg]

Hi all,

I'm experimenting with the Spring 2.5.x StoredProcedure support but can't seem to be able to declare an SQL type that represents a Sybase Cursor. The examples in the documentation only cover the Oracle Specific case (where an Oracle specific value is used). My code below:

import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.datasource.*;
import org.springframework.jdbc.object.StoredProcedure;

/**
 * Prototype class to prove concept of calling a Stored Procedure in Sybase
 * using Spring
 * 
 * @author verbma
 */
public class StoredProcedureTest
{
    /**
     * @param args
     */
    public static void main(String[] args)
    {
        StoredProcedureTest storedProcedureTest = new StoredProcedureTest();
        storedProcedureTest.run();
    }

    /**
     * Run the StoredProcedureTest
     */
    private void run()
    {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.sybase.jdbc3.jdbc.SybDataSource");
        ds.setUrl("jdbc:sybase:Tds:svc-static01DBd:4100/MV_Static01EAI");
        ds.setUsername("$%^%^");
        ds.setPassword("$%^%$^$%");
        MyStoredProcedure sproc = new MyStoredProcedure(ds);
        Map results = sproc.execute();
        printMap(results);
    }

    /**
     * Private class that extends the StoredProcedure class from Spring
     * 
     * @author verbma
     */
    private class MyStoredProcedure extends StoredProcedure
    {
        /** Execute the sp_help stored proc in Sybase */
        private static final String SQL = "sp_help";

        /**
         * Constructor
         * @param ds
         */
        public MyStoredProcedure(DataSource ds)
        {
            setDataSource(ds);
            setFunction(true);
            setSql(SQL);
            // Problem is here, can't declare a type of Cursor so the RowMapper isn't invoked!
            declareParameter(new SqlOutParameter("result", Types.VARCHAR, new ResultRowMapper()));
            compile();
        }

        /**
         * Execute the Stored Proc
         * @return The result set
         */
        public Map execute()
        {
            // the 'sp_help' sproc has no input parameters, so an empty Map is supplied...
            return execute(new HashMap<Object, Object>());
        }
    }

    /**
     * Print out the results from the stored proc
     * @param results
     */
    private static void printMap(Map results)
    {
        Set entrySet = results.entrySet();
        for (Iterator it = entrySet.iterator(); it.hasNext();)
        {
            Object resultSet = it.next();
            System.out.println(resultSet);
        }
    }
}

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * A RowMapper for the sp_help result
 * 
 * @author verbma
 */
public class ResultRowMapper implements RowMapper
{
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException
    {
        System.out.println("Rowmapping");
        String result = new String(rs.getBytes(0));
        String result1 = new String(rs.getBytes(1));
        String result2 = new String(rs.getBytes(2));
        return result + result1 + result2;
    }
}

Results:

7
#result-set-3=[]
#result-set-2=[]
#result-set-1=[{Name=ijdbc_function_escapes, Owner=dbo, Object_type=user table}, {Name=jdbc_function_escapes, Owner=dbo, Object_type=user table}, {Name=monCachePool, Owner=dbo, Object_type=user table}, {Name=monCachedObject, Owner=dbo, Object_type=user table}, {Name=monCachedProcedures, Owner=dbo, Object_type=user table}, {Name=monDataCache, Owner=dbo, Object_type=user table}, {Name=monDeadLock, Owner=dbo, Object_type=user table}, {Name=monDeviceIO, Owner=dbo, Object_type=user table}, {Name=monEngine, Owner=dbo, Object_type=user table}, {Name=monErrorLog, Owner=dbo, Object_type=user table}, {Name=monIOQueue, Owner=dbo, Object_type=user table}, {Name=monLocks, Owner=dbo, Object_type=user table}, {Name=monNetworkIO, Owner=dbo, Object_type=user table}, {Name=monOpenDatabases, Owner=dbo, Object_type=user table}, {Name=monOpenObjectActivity, Owner=dbo, Object_type=user table}, {Name=monProcedureCache, Owner=dbo, Object_type=user table}, {Name=monProcess, Owner=dbo, Object_type=user table}, {Name=monProcessActivity, Owner=dbo, Object_type=user table}, {Name=monProcessLookup, Owner=dbo, Object_type=user table}, {Name=monProcessNetIO, Owner=dbo, Object_type=user table}, {Name=monProcessObject, Owner=dbo, Object_type=user table}, {Name=monProcessProcedures, Owner=dbo, Object_type=user table}, {Name=monProcessSQLText, Owner=dbo, Object_type=user table}, {Name=monProcessStatement, Owner=dbo, Object_type=user table}, {Name=monProcessWaits, Owner=dbo, Object_type=user table}, {Name=monProcessWorkerThread, Owner=dbo, Object_type=user table}, {Name=monState, Owner=dbo, Object_type=user table}, {Name=monSysPlanText, Owner=dbo, Object_type=user table}, {Name=monSysSQLText, Owner=dbo, Object_type=user table}, {Name=monSysStatement, Owner=dbo, Object_type=user table}, {Name=monSysWaits, Owner=dbo, Object_type=user table}, {Name=monSysWorkerThread, Owner=dbo, Object_type=user table}, {Name=monTableColumns, Owner=dbo, Object_type=user table}, {Name=monTableParameters, Owner=dbo, Object_type=user table}, {Name=monTables, Owner=dbo, Object_type=user table}, {Name=monWaitClassInfo, Owner=dbo, Object_type=user table}, {Name=monWaitEventInfo, Owner=dbo, Object_type=user table}, {Name=spt_ijdbc_conversion, Owner=dbo, Object_type=user table}, {Name=spt_ijdbc_mda, Owner=dbo, Object_type=user table}, {Name=spt_ijdbc_table_types, Owner=dbo, Object_type=user table}, {Name=spt_jdbc_conversion, Owner=dbo, Object_type=user table}, {Name=spt_jdbc_table_types, Owner=dbo, Object_type=user table}, {Name=spt_jtext, Owner=dbo, Object_type=user table}, {Name=spt_limit_types, Owner=dbo, Object_type=user table}, {Name=spt_mda, Owner=dbo, Object_type=user table}, {Name=spt_monitor, Owner=dbo, Object_type=user table}, {Name=spt_values, Owner=dbo, Object_type=user table}, {Name=syblicenseslog, Owner=dbo, Object_type=user table}, {Name=sysalternates, Owner=dbo, Object_type=system table}, {Name=sysattributes, Owner=dbo, Object_type=system table}, {Name=syscertificates, Owner=dbo, Object_type=system table}, {Name=syscharsets, Owner=dbo, Object_type=system table}, {Name=syscolumns, Owner=dbo, Object_type=system table}, {Name=syscomments, Owner=dbo, Object_type=system table}, {Name=sysconfigures, Owner=dbo, Object_type=system table}, {Name=sysconstraints, Owner=dbo, Object_type=system table}, {Name=syscurconfigs, Owner=dbo, Object_type=system table}, {Name=sysdatabases, Owner=dbo, Object_type=system table}, {Name=sysdepends, Owner=dbo, Object_type=system table}, {Name=sysdevices, Owner=dbo, Object_type=system table}, {Name=sysengines, Owner=dbo, Object_type=system table}, {Name=sysgams, Owner=dbo, Object_type=system table}, {Name=sysindexes, Owner=dbo, Object_type=system table}, {Name=sysjars, Owner=dbo, Object_type=system table}, {Name=syskeys, Owner=dbo, Object_type=system table}, {Name=syslanguages, Owner=dbo, Object_type=system table}, {Name=syslisteners, Owner=dbo, Object_type=system table}, {Name=syslocks, Owner=dbo, Object_type=system table}, {Name=sysloginroles, Owner=dbo, Object_type=system table}, {Name=syslogins, Owner=dbo, Object_type=system table}, {Name=syslogs, Owner=dbo, Object_type=system table}, {Name=syslogshold, Owner=dbo, Object_type=system table}, {Name=sysmessages, Owner=dbo, Object_type=system table}, {Name=sysmonitors, Owner=dbo, Object_type=system table}, {Name=sysobjects, Owner=dbo, Object_type=system table}, {Name=syspartitions, Owner=dbo, Object_type=system table}, {Name=sysprocedures, Owner=dbo, Object_type=system table}, {Name=sysprocesses, Owner=dbo, Object_type=system table}, {Name=sysprotects, Owner=dbo, Object_type=system table}, {Name=sysqueryplans, Owner=dbo, Object_type=system table}, {Name=sysreferences, Owner=dbo, Object_type=system table}, {Name=sysremotelogins, Owner=dbo, Object_type=system table}, {Name=sysresourcelimits, Owner=dbo, Object_type=system table}, {Name=sysroles, Owner=dbo, Object_type=system table}, {Name=syssecmechs, Owner=dbo, Object_type=system table}, {Name=syssegments, Owner=dbo, Object_type=system table}, {Name=sysservers, Owner=dbo, Object_type=system table}, {Name=syssessions, Owner=dbo, Object_type=system table}, {Name=syssrvroles, Owner=dbo, Object_type=system table}, {Name=sysstatistics, Owner=dbo, Object_type=system table}, {Name=systabstats, Owner=dbo, Object_type=system table}, {Name=systestlog, Owner=dbo, Object_type=system table}, {Name=systhresholds, Owner=dbo, Object_type=system table}, {Name=systimeranges, Owner=dbo, Object_type=system table}, {Name=systransactions, Owner=dbo, Object_type=system table}, {Name=systypes, Owner=dbo, Object_type=system table}, {Name=sysusages, Owner=dbo, Object_type=system table}, {Name=sysusermessages, Owner=dbo, Object_type=system table}, {Name=sysusers, Owner=dbo, Object_type=system table}, {Name=sysxtypes, Owner=dbo, Object_type=system table}, {Name=sp_aux_getsize, Owner=dbo, Object_type=stored procedure}, {Name=sp_configure, Owner=dbo, Object_type=stored procedure}, {Name=sp_dboption, Owner=dbo, Object_type=stored procedure}, {Name=sp_dbupgrade, Owner=dbo, Object_type=stored procedure}, {Name=sp_dropdevice, Owner=dbo, Object_type=stored procedure}, {Name=sp_getmessage, Owner=dbo, Object_type=stored procedure}, {Name=sp_loaddbupgrade, Owner=dbo, Object_type=stored procedure}, {Name=sp_probe_xact, Owner=dbo, Object_type=stored procedure}, {Name=sp_procxmode, Owner=dbo, Object_type=stored procedure}, {Name=sp_prtsybsysmsgs, Owner=dbo, Object_type=stored procedure}, {Name=sp_validlang, Owner=dbo, Object_type=stored procedure}]
#update-count-2=0
#update-count-1=1
#result-set-4=[]
result=0

Basically it all executes fine with the Type set to VARCHAR but it simply prints out the entire return stream from the Stored Proc as opposed to something 'nice' into my result variable. In debug mode or simply putting a System.out.println I can see that my ResultRowMapper.mapRow method is never called (Spring should do this automagically), I hope that made sense!

[Martijn Verburg]

Hmm, editing posts no longer seem to work, here the rest of the code:

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * A RowMapper for the sp_help result
 * 
 * @author verbma
 */
public class ResultRowMapper implements RowMapper
{
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException
    {
        System.out.println("Rowmapping");
        String result = new String(rs.getBytes(0));
        String result1 = new String(rs.getBytes(1));
        String result2 = new String(rs.getBytes(2));
        return result + result1 + result2;
    }
}

[Martijn Verburg]

X-posted to Spring forums.