<html>
<head>
<title>jTDS Support for JDBC result sets</title>
<link rel="stylesheet" href="style.css" type="text/css">
</head>
<body>
<table class="mainTable" cellpadding="0" cellspacing="0">
  <tr>
    <td colspan="3" class="logoWrapper">
      <table cellpadding="0" cellspacing="0" class="logo">
        <tr>
          <td class="logo"><img src="images/logo.gif" alt="The jTDS Project" class="logo"></td>
          <td class="logo"><img src="images/logoBg.gif" class="logoBg"></td>
        </tr>
      </table>
    </td>
  </tr>
  <tr>
    <td colspan="3">&nbsp;</td>
  </tr>
  <tr>
    <td width="9" height="9"><img src="images/maintl.gif" width="9" height="9"></td>
    <td height="9" background="images/maint.gif"><img src="images/spacer.gif"></td>
    <td width="9" height="9"><img src="images/maintr.gif" width="9" height="9"></td>
  </tr>
  <tr>
    <td width="9" background="images/mainl.gif"><img src="images/spacer.gif"></td>
    <td class="content">
      <p class="links">
        <a href="index.html">Home</a>
        | <a href="faq.html">FAQ</a>
        | <a href="doc.html">Documentation</a>
        | <a href="links.html">Links</a>
        | <a href="license.html">License</a>
        | <a href="http://sourceforge.net/projects/jtds">Project Info</a>
        | <a href="http://sourceforge.net/projects/jtds/files">Download</a>
        | <a href="http://sourceforge.net/p/jtds/news">News</a>
        | <a href="http://sourceforge.net/p/jtds/bugs">Bugs</a>
        | <a href="http://sourceforge.net/p/jtds/discussion">Forums</a>
      </p>


<!--
==================================
  Content starts here
==================================
-->


<p class="title">jTDS result set information</p>

<p>jTDS supports the following result set types on MS SQL Server.</p>
<table class="inner" cellspacing="0">
  <tr>
    <th align="left">JDBC Type</th>
    <th align="left">SQL Server Cursor Type</th>
    <th align="left">Server Load</th>
    <th align="left">Description</th>
  </tr>
  <tr>
    <td rowspan="3">TYPE_FORWARD_ONLY</td>
    <td>Firehose cursor (direct select) when read-only</td>
    <td>Light</td>
    <td>Fast, but driver will have to read all data. Not recommended when using multiple result sets. Forward only.</td>
  </tr>
  <tr>
    <td>Fast forward-only (static) cursor when read-only and <code>useCursors=true</code></td>
    <td>Heavy</td>
    <td>Slower than firehose cursors (multiple fetch requests), driver doesn't have to read all data. Forward only.</td>
  </tr>
  <tr>
    <td>Forward-only dynamic cursor when updatable</td>
    <td>Heavy</td>
    <td>Others' updates, deletes and inserts visible. Forward only.</td>
  </tr>
  <tr>
    <td>TYPE_SCROLL_INSENSITIVE</td>
    <td>Static cursor</td>
    <td>Heavy</td>
    <td>Only works with read-only concurrency (updatable is downgraded). SQL Server generates a temporary table, so changes made by others are not visible. Scrollable.</td>
  </tr>
  <tr>
    <td>TYPE_SCROLL_SENSITIVE</td>
    <td>Keyset cursor</td>
    <td>Medium</td>
    <td>Others' updates or deletes visible, but not others' inserts. Scrollable.</td>
  </tr>
  <tr>
    <td>TYPE_SCROLL_SENSITIVE+1</td>
    <td>Dynamic cursor</td>
    <td>Heavy</td>
    <td>Others' updates, deletes and inserts visible. Scrollable.</td>
  </tr>
</table>

<p>jTDS supports the following result set concurrencies on MS SQL Server.</p>
<table class="inner" cellspacing="0">
  <tr>
    <th align="left">JDBC Concurrency</th>
    <th align="left">SQL Server Concurrency</th>
    <th align="left">Row Locks</th>
    <th align="left">Description</th>
  </tr>
  <tr>
    <td>CONCUR_READ_ONLY</td>
    <td>Read only</td>
    <td>No</td>
    <td>Read-only.</td>
  </tr>
  <tr>
    <td>CONCUR_UPDATABLE</td>
    <td>Optimistic concurrency, updatable</td>
    <td>No</td>
    <td>Row integrity checked using timestamp comparison or, when not available, value comparison (except text and image fields).</td>
  </tr>
  <tr>
    <td>CONCUR_UPDATABLE+1</td>
    <td>Pessimistic concurrency, updatable</td>
    <td>Yes</td>
    <td>Row integrity is ensured by locking rows.</td>
  </tr>
  <tr>
    <td>CONCUR_UPDATABLE+2</td>
    <td>Optimistic concurrency, updatable</td>
    <td>No</td>
    <td>Row integrity checked using value comparison (except text and image fields).</td>
  </tr>
</table>

<p>jTDS supports the following result set types on Sybase.</p>
<table class="inner" cellspacing="0" width="80%">
<tr>
<th align="left">Result Set Type</th><th align="left">Result Set Concurrency</th><th align="left">Supported</th><th align="left">Comments</th>
</tr>
<tr>
<td>&nbsp;&nbsp;TYPE_FORWARD_ONLY</td><td>CONCUR_READ_ONLY</td><td>Yes</td><td>Default result set using a direct select.</td>
</tr>
<tr>
<td>&nbsp;&nbsp;TYPE_FORWARD_ONLY</td><td>CONCUR_UPDATEABLE</td><td>Yes</td><td>Uses a client side cursor with optimistic locking.</td>
</tr>
<tr>
<td>&nbsp;&nbsp;TYPE_SCROLL_INSENSITIVE</td><td>CONCUR_READ_ONLY</td><td>Yes</td><td>Uses a client side cursor.</td>
</tr>
<tr>
<td>&nbsp;&nbsp;TYPE_SCROLL_INSENSITIVE</td><td>CONCUR_UPDATEABLE</td><td>Yes</td><td>Uses a client side cursor with optimistic locking.</td>
</tr>
<tr>
<td>&nbsp;&nbsp;TYPE_SCROLL_SENSITIVE</td><td>CONCUR_READ_ONLY</td><td>Yes</td><td>Uses a client side cursor.</td>
</tr>
<tr>
<td>&nbsp;&nbsp;TYPE_SCROLL_SENSITIVE</td><td>CONCUR_UPDATEABLE</td><td>Yes</td><td>Uses a client side cursor with optimistic locking.</td>
</tr>
</table>
<p>Notes:</p>
<ol>
<li>Updateable result sets can only be built from select statements containing one base table.</li>
<li>Tables used to build CONCUR_UPDATEABLE and/or TYPE_SCROLL_SENSITIVE result sets should have primary keys.</li>
<li>Use the Statement.getWarnings() and ResultSet.getWarnings() methods to check that the actual result set
returned has not been downgraded, for example from updateable to read only.</li>
<li>For SQL Server cursors the fetch size defaults to 100 rows while for Sybase the entire result set is cached in the
client. Therefore care must be taken with Sybase to limit the size of result sets to avoid "out of memory" errors.
</ol>
<p>Support for positioned updates:</p>
<ol>
<li>Positioned updates are supported on both SQL Server and Sybase using forward only server cursors.</li>
<li>For named cursors to be used for positioned updates the fetch size defaults to 1.
<li>Use the Statement.setCursorName() method to set the cursor name.</li>
<li>Use the ResultSet.getCursorName() method to obtain the cursor name for use in the "where current of" clause.</li>
<li>If there is no intention to actually update the cursor then the ResultSet.setFetchSize() method can be used to
increase the fetch size from it's default of 1. This mode allows efficient sequential access to large result sets
without the risk of the driver caching large amounts of data.</li>
</ol>


<!--
==================================
  Content ends here
==================================
-->

    </td>
    <td width="9" background="images/mainr.gif"><img src="images/spacer.gif"></td>
  </tr>
  <tr>
    <td width="9" height="9"><img src="images/mainbl.gif" width="9" height="9"></td>
    <td height="9" background="images/mainb.gif"><img src="images/spacer.gif"></td>
    <td width="9" height="9"><img src="images/mainbr.gif" width="9" height="9"></td>
  </tr>
  <tr>
    <td colspan="3">
      <table border="0" cellspacing="0" width="100%" class="outer">
        <tr>
          <td class="copyright" width="150">
            Project hosted by<br><br>
            <A href="http://sourceforge.net/projects/jtds" target="_top"><IMG src="http://sourceforge.net/sflogo.php?group_id=33291&amp;type=2" border="0" alt="SourceForge Logo" class="otherLogo"></A><br>
          </td>
          <td width="500"/>
          <td class="copyright" width="150">
            Web Design<br><br>
            <a href="http://anca13.go.ro" class="outer">Anca Sinpalean</a>
          </td>
        </tr>
      </table>
    </td>
  </tr>
</table>
</body>
</html>
