How do you make sql server jdbc calls to stored procedure use a server side cursor?

Question

I am using JDBC to call a stored procedure in MSSql 2005. I would like to have the result set make use of server side cursors to limit the amount of memory used by the client. I am able to do this easily using a simple query and setting the SelectMethod=cursor on the connection. When I put that query in a stored procedure, it appears that the entire result set is being returned as if the connection were created with SelectMethod=direct.

Is there a way to make the stored procedure call behave like a simple query.

I have tested with MS SQL JDBC driver 1.1 and 1.2. In the sample below, the difference between the two values of totalMemory is dependent on the size of the results without fetching any of the rows.

long totalMemoryUsed = java.lang.Runtime.getRuntime().totalMemory();
String sql = "{call ap_Select(?, ?, ?, ?)}";

CallableStatement cstmt = conn.prepareCall(sql,ResultSet.TYPE_FORWARD_ONLY,  
                           ResultSet.CONCUR_READ_ONLY);  
cstmt.setString(1,readAlldbnames );
cstmt.setString(2,readPublicDbnames);
cstmt.setString(3, whereClause);
cstmt.setString(4, " order by key5 ");

ResultSet a_resultSet = cstmt.executeQuery();
long totalMemoryUsedAfterQuery = java.lang.Runtime.getRuntime().totalMemory();

Answer 1

The only thing I have found till now is to get the resultset from the stored procedure in a table, then use a serverside cursor on the table and drop the table... I wish I can find a better way than this.

Please let me know if it is possible to use serverside cursors for stored procedure calls using JDBC.

Thanks,
Sayali