 |
 |
Home | FAQ | Documentation | Links | License | Project Info | Download | News | Bugs | Forums
jTDS result set information
jTDS supports the following result set types on MS SQL Server.
| JDBC Type | SQL Server Cursor Type | Server Load | Description |
|---|---|---|---|
| TYPE_FORWARD_ONLY | Firehose cursor (direct select) when read-only | Light | Fast, but driver will have to read all data. Not recommended when using multiple result sets. Forward only. |
Fast forward-only (static) cursor when read-only and useCursors=true |
Heavy | Slower than firehose cursors (multiple fetch requests), driver doesn't have to read all data. Forward only. | |
| Forward-only dynamic cursor when updatable | Heavy | Others' updates, deletes and inserts visible. Forward only. | |
| TYPE_SCROLL_INSENSITIVE | Static cursor | Heavy | Only works with read-only concurrency (updatable is downgraded). SQL Server generates a temporary table, so changes made by others are not visible. Scrollable. |
| TYPE_SCROLL_SENSITIVE | Keyset cursor | Medium | Others' updates or deletes visible, but not others' inserts. Scrollable. |
| TYPE_SCROLL_SENSITIVE+1 | Dynamic cursor | Heavy | Others' updates, deletes and inserts visible. Scrollable. |
jTDS supports the following result set concurrencies on MS SQL Server.
| JDBC Concurrency | SQL Server Concurrency | Row Locks | Description |
|---|---|---|---|
| CONCUR_READ_ONLY | Read only | No | Read-only. |
| CONCUR_UPDATABLE | Optimistic concurrency, updatable | No | Row integrity checked using timestamp comparison or, when not available, value comparison (except text and image fields). |
| CONCUR_UPDATABLE+1 | Pessimistic concurrency, updatable | Yes | Row integrity is ensured by locking rows. |
| CONCUR_UPDATABLE+2 | Optimistic concurrency, updatable | No | Row integrity checked using value comparison (except text and image fields). |
jTDS supports the following result set types on Sybase.
| Result Set Type | Result Set Concurrency | Supported | Comments |
|---|---|---|---|
| TYPE_FORWARD_ONLY | CONCUR_READ_ONLY | Yes | Default result set using a direct select. |
| TYPE_FORWARD_ONLY | CONCUR_UPDATEABLE | Yes | Uses a client side cursor with optimistic locking. |
| TYPE_SCROLL_INSENSITIVE | CONCUR_READ_ONLY | Yes | Uses a client side cursor. |
| TYPE_SCROLL_INSENSITIVE | CONCUR_UPDATEABLE | Yes | Uses a client side cursor with optimistic locking. |
| TYPE_SCROLL_SENSITIVE | CONCUR_READ_ONLY | Yes | Uses a client side cursor. |
| TYPE_SCROLL_SENSITIVE | CONCUR_UPDATEABLE | Yes | Uses a client side cursor with optimistic locking. |
Notes:
- Updateable result sets can only be built from select statements containing one base table.
- Tables used to build CONCUR_UPDATEABLE and/or TYPE_SCROLL_SENSITIVE result sets should have primary keys.
- 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.
- 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.
Support for positioned updates:
- Positioned updates are supported on both SQL Server and Sybase using forward only server cursors.
- For named cursors to be used for positioned updates the fetch size defaults to 1.
- Use the Statement.setCursorName() method to set the cursor name.
- Use the ResultSet.getCursorName() method to obtain the cursor name for use in the "where current of" clause.
- 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.
|
Project hosted by |
Web Design Anca Sinpalean |