Search code examples
javasql-serverjdbcjtds

jtds ignores setFetchSize. How can I limit the fetch size?

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?

Solution

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

     url="jdbc:jtds:sqlserver://myserver:1433;DatabaseName=myDb;useCursors=true";