如何使用服务器端游标对存储过程进行 sql server jdbc 调用？
我正在使用 JDBC 调用 MSSql 2005 中的存储过程。我希望结果集利用服务器端游标来限制客户端使用的内存量。 我可以使用简单的查询并在连接上设置 SelectMethod=cursor 轻松完成此操作。 当我将该查询放入存储过程中时，似乎返回了整个结果集，就像使用 SelectMethod=direct 创建连接一样。
有没有办法使存储过程调用的行为像一个简单的查询。
我已经使用 MS SQL JDBC 驱动程序 1.1 和 1.2 进行了测试。 在下面的示例中，totalMemory 的两个值之间的差异取决于未获取任何行的结果的大小。
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();
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();
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
到目前为止我唯一发现的是从表中的存储过程获取结果集，然后在表上使用服务器端游标并删除表......我希望我能找到比这更好的方法。
请告诉我是否可以使用服务器端游标通过 JDBC 进行存储过程调用。
谢谢，
萨亚利
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