-
Notifications
You must be signed in to change notification settings - Fork 70
/
Copy pathresultSets.html
215 lines (205 loc) · 8.45 KB
/
resultSets.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
<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"> </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/project/showfiles.php?group_id=33291">Download</a>
| <a href="http://sourceforge.net/news/?group_id=33291">News</a>
| <a href="http://sourceforge.net/tracker/?group_id=33291&atid=407762">Bugs</a>
| <a href="http://sourceforge.net/forum/?group_id=33291">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> TYPE_FORWARD_ONLY</td><td>CONCUR_READ_ONLY</td><td>Yes</td><td>Default result set using a direct select.</td>
</tr>
<tr>
<td> TYPE_FORWARD_ONLY</td><td>CONCUR_UPDATEABLE</td><td>Yes</td><td>Uses a client side cursor with optimistic locking.</td>
</tr>
<tr>
<td> TYPE_SCROLL_INSENSITIVE</td><td>CONCUR_READ_ONLY</td><td>Yes</td><td>Uses a client side cursor.</td>
</tr>
<tr>
<td> TYPE_SCROLL_INSENSITIVE</td><td>CONCUR_UPDATEABLE</td><td>Yes</td><td>Uses a client side cursor with optimistic locking.</td>
</tr>
<tr>
<td> TYPE_SCROLL_SENSITIVE</td><td>CONCUR_READ_ONLY</td><td>Yes</td><td>Uses a client side cursor.</td>
</tr>
<tr>
<td> 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&type=2" border="0" alt="SourceForge Logo" class="otherLogo"></A><br>
</td>
<td class="copyright" width="500">WebRing member<br><br>
<script language=javascript src="http://ss.webring.com/navbar?f=j;y=alin_sinpalean;u=10063174"></script>
<noscript><center><table bgcolor=gray cellspacing=0 border=2 bordercolor=red>
<tr><td><table cellpadding=2 cellspacing=0 border=0><tr><td align=center>
<font face=arial size=-1>This site is a member of WebRing.
To browse visit <a href="http://ss.webring.com/navbar?f=l;y=alin_sinpalean;u=10063174">
here</a>.</font></td></tr></table></td></tr></table></center></noscript>
</td>
<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>