5

I am trying to limit the fetch size of my select query. Unfortunately the JTDS MS SQL driver still reads all rows.

I do not want a limit or offset select. I just want to save my memory such that I need less RAM. I do not need setMaxRows.

My sample code:

url="jdbc:jtds:sqlserver://myserver:1433;DatabaseName=myDb";
mySql="select * from myVeryLargeTable";
con.setAutoCommit(false);
batchSize=1000;
s =con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
s.setFetchSize(batchSize);
rs = s.executeQuery(mySql);
rs.setFetchSize(batchSize);
rs.next(); // here all rows are coming in over network but i only want 'batchSize'

How can I limit the fetch size?

Alex
  • 8,518
  • 4
  • 28
  • 40
  • 1
    Try appending `;useCursors=true` to your connection URL. – Gord Thompson Feb 09 '16 at 00:07
  • Are you looking for [`setMaxRows`](http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setMaxRows-int-) instead? – Mark Rotteveel Feb 09 '16 at 09:08
  • The proposed duplicate is about " Microsoft SQL Server 2005 JDBC Driver " i asked about JTDS driver. its not the same! – Alex Feb 09 '16 at 09:28
  • @MarkRotteveel i updated the question. – Alex Feb 09 '16 at 09:29
  • @axaluss That doesn't matter, most of the answers to that question describe the generic behavior that the JDBC specification requires from drivers, and so it also applies to other drivers (and for other databases) as well (maybe with the exception of MySQL Connector/J). However, given your question seems different now, I have reopened it. – Mark Rotteveel Feb 09 '16 at 09:35
  • the correct answer to put `;useCursors=true` into the url is not among those answers. – Alex Feb 09 '16 at 09:37

1 Answers1

5

The solution is to insert ;useCursors=true into the url.

 url="jdbc:jtds:sqlserver://myserver:1433;DatabaseName=myDb;useCursors=true";
Alex
  • 8,518
  • 4
  • 28
  • 40