title: Difference between fetching with server cursor vs streaming
body: <p>What's the difference, behaviour-wise, between fetching results using <em>server cursors</em> and fetching using <em>streaming</em>?</p>
  
  <p>The former (<em>server cursors</em>) can be activated:</p>
  
  <ul>
  <li><p>For every statement by setting <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html" rel="noreferrer"><code>useCursorFetch=true</code></a> and <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html" rel="noreferrer"><code>defaultFetchSize=N</code></a> in the connection properties. (Where <em>N</em> is a number greater than zero.)</p></li>
  <li><p>Or individual statements by initially setting <code>useCursorFetch=true</code> or <code>com.mysql.jdbc.Connection.setUseCursorFetch(true)</code> on the connection, followed by <code>java.sql.Statement.setFetchSize(N)</code> on the statement.</p></li>
  </ul>
  
  <p>The latter (<a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html" rel="noreferrer"><em>streaming</em></a>) can be activated:</p>
  
  <ul>
  <li><p>On individual statements by setting <code>java.sql.Statement.setFetchSize(Integer.MIN_VALUE)</code> or by calling <code>com.mysql.jdbc.Statement.enableStreamingResults()</code> on the statment.</p></li>
  <li><p>And probably on every statement by setting <code>defaultFetchSize=X</code> in the connection properties, where <em>X</em> is a number equal to <code>Integer.MIN_VALUE</code>.</p></li>
  </ul>
  
  <p><strong>What does one need to consider when developing with these alternative methods?</strong>  A good answer may touch on topics such as <em>performance</em>, <em>lock holding</em>, and <em>resource allocation (/ deallocation)</em>.</p>