MS-SQL-Server/sybase driver options.	Contents
	Schema definition reference
	JDBC Driver Options

Configuration details of MS-SQL/Sybase database connections using the jTDS driver.

For further details about the driver, please refer to the jTDS project page. The properties below must be added to the URL connection string.

The URL format for jTDS is:

    jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

where <server_type> is one of either 'sqlserver' or 'sybase' (their meaning is quite obvious), <port> is the port the database server is listening to (default is 1433 for SQL Server and 7100 for Sybase) and <database> is the database name -- JDBC term: catalog -- (if not specified, the user's default database is used). The set of properties supported by jTDS is:

The JTDS driver for MS-SQL-Server/Sybase supports the following options:

 

TDS (default - "8.0" for SQL Server; "5.0" for Sybase)

The version of TDS to be used. TDS (Tabular Data Stream) is the protocol used by Microsoft SQL Server and Sybase to communicate with database clients. jTDS can use TDS 4.2, 5.0, 7.0 and 8.0. Version 4.2 is used by SQL Server 6.5 and Sybase 10. Version 5.0 is used with Sybase 11 onwards. Version 7.0 is used by SQL Server 7.0; this protocol also works with SQL Server 2000. Version 8.0 is used by SQL Server 2000 and SQL Server 2005.
Newer database server versions usually understand older protocol versions. This means that SQL Server 7.0 can be used with TDS 4.2, but the limitations of the protocol apply regardless of the server version (e.g. when using TDS 4.2 VARCHARs are limited to 255 characters). As a conclusion, you must set this property to "4.2" when connecting to SQL Server 6.5 or Sybase. You should not set this value to "7.0" or "8.0") when connecting to any version of Sybase as these are SQL Server specific protocols. Further, you should not set this value to "5.0") when connecting to any version of SQL Server as this is a Sybase specific protocol.
Currently jTDS automatically falls back from 8.0 to 7.0 (if used with SQL Server 7.0) and from 5.0 to 4.2 (with Sybase 10) so specifying the value for this parameter is only necessary for SQL Server 6.5.

cachemetadata (default - false)

When used with prepareSQL=3, setting this property to true will cause the driver to cache column meta data for SELECT statements. Caching the meta data will reduce the processing overhead when reusing statements that return small result sets that have many columns but may lead to unexpected errors if the database schema changes after the statement has been prepared. Use with care. Only applicable to SQL Server (there is no prepareSQL=3 mode for Sybase).

charset (default - the character set the server was installed with)

Very important setting, determines the byte value to character mapping for CHAR/VARCHAR/TEXT values. Applies for characters from the extended set (codes 128-255). For NCHAR/NVARCHAR/NTEXT values doesn't have any effect since these are stored using Unicode.

domain

Specifies the Windows domain to authenticate in. If present and the user name and password are provided, jTDS uses Windows (NTLM) authentication instead of the usual SQL Server authentication (i.e. the user and password provided are the domain user and password). This allows non-Windows clients to log in to servers which are only configured to accept Windoes authentication.
If the domain parameter is present but no user name and password are provided, jTDS uses its native Single-Sign-On library and logs in with the logged Windows user's credentials (for this to work one would obviously need to be on Windows, logged into a domain, and also have the SSO library installed -- consult README.SSO in the distribution on how to do this).

instance

Named instance to connect to. SQL Server can run multiple so-called "named instances" (i.e. different server instances, running on different TCP ports) on the same machine. When using Microsoft tools, selecting one of these instances is made by using "<host_name>\<instance_name>" instead of the usual "<host_name>". With jTDS you will have to split the two and use the instance name as a property.

appName (default - "jTDS")

Application name. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.

progName (default - "jTDS")

Client library name. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.

wsid (default - the client host name)

Workstation ID. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.

macAddress (default - "000000000000")

Network interface card MAC address. It's displayed by Enterprise Manager or Profiler associated with the connection and is needed to resolve some issues regarding the number of clients allowed by the SQL Server license. The MAC address cannot be determined automatically from Java (i.e. without using native code) so you'll have to specify it yourself if you need it.

sendStringParametersAsUnicode (default - true)

Determines whether string parameters are sent to the SQL Server database in Unicode or in the default character encoding of the database. This seriously affects SQL Server 2000 performance since it does not automatically cast the types (as 7.0 does), meaning that if a index column is Unicode and the string is submitted using the default character encoding (or the other way around) SQLServer will perform an index scan instead of an index seek. For Sybase, determines if strings that cannot be encoded in the server's charset are sent as unicode strings. There is a performance hit for the encoding logic so set this option to false if unitext or univarchar data types are not in use or if charset is utf-8.

lastUpdateCount (default - true)

If true only the last update count will be returned by executeUpdate(). This is useful in case you are updating or inserting into tables that have triggers (such as replicated tables); there's no way to make the difference between an update count returned by a trigger and the actual update count but the actual update count is always the last as the triggers execute first. If false all update counts are returned; use getMoreResults() to loop through them.

prepareSQL (default - 3 for SQL Server, 1 for Sybase)

This parameter specifies the mechanism used for Prepared Statements.

Value	Description
0	SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower)
1	Temporary stored procedures are created for each unique SQL statement and parameter combination (faster)
2	sp_executesql is used (fast)
3	sp_prepare and sp_cursorprepare are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)

packetSize (default - 4096 for TDS 7.0/8.0; 512 for TDS 4.2/5.0)

The network packet size (a multiple of 512).

tcpNoDelay (default - true)

true to enable TCP_NODELAY on the socket; false to disable it.

lobBuffer (default - 32768)

The amount of LOB data to buffer in memory before caching to disk. The value is in bytes for Blob data and chars for Clob data.

maxStatements (default - 500)

The number of statement prepares each connection should cache. A value of 0 will disable statement caching. A value of Integer.MAX_VALUE (2147483647) will enable fast caching (uses less memory and has no overhead associated with removing statements); the cache will never release any cached statements, so although experience has shown that this is usually not a problem with most applications, use with care.

loginTimeout (default - 0)

The amount of time to wait (in seconds) for a successful connection before timing out.
If namedPipe is true and loginTimeout is non-zero, the value of loginTimeout is used for the retry timeout when "All pipe instances are busy" error messages are received while attempting to connect to the server. If namedPipe is true and loginTimeout is zero (the default), a value of 20 seconds is used for the named pipe retry timeout.

socketTimeout (default - 0)

The amount of time to wait (in seconds) for network activity before timing out.
Use with care! If a non zero value is supplied this must be greater than the maximum time that the server will take to answer any query. Once the timeout value is exceeded the network connection will be closed. This parameter may be useful for detecting dead network connections in a pooled environment.

namedPipe (default - false)

When set to true, named pipe communication is used to connect to the database instead of TCP/IP sockets. When the os.name system property starts with "windows" (case-insensitive), named pipes (both local and remote) are accessed through the Windows filesystem by opening a RandomAccessFile to the path. When the SQL Server and the client are on the same machine, a named pipe will usually have better performance than TCP/IP sockets since the network layer is eliminated. Otherwise the JCIFS library is used. JCIFS provides a pure Java named pipe implementation and uses NTLM authentication, so the domain parameter is required.
This feature supports the instance parameter (which changes the named pipe URL), but it does not currently support the named pipe at a location other than /sql/query on the server. The port parameter is ignored if set.

xaEmulation (default - true)

When set to true, emulate XA distributed transaction support, when set to false use experimental true distributed transaction support. True distributed transaction support is only available for SQL Server 2000 and requires the installation of an external stored procedure in the target server (see the README.XA file in the distribution for details).

ssl (default - off)

Specifies if and how to use SSL for secure communication.

Value	Description
off	SSL is not request or used; this is the default
request	SSL is requested; if the server does not support it then a plain connection is used
require	SSL is requested; if the server does not support it then an exception is thrown
authenticate	Same as require except the server's certificate must be signed by a trusted CA

batchSize (default - 0 for SQL Server; 1000 for Sybase)

Controls how many statements are sent to the server in a batch. The actual batch is broken up into pieces this large that are sent separately. The reason for this is to avoid Sybase "hangs" caused by running out of space with very large batches. The problem doesn't seem to occur with SQL Server, hence the default limit of 0 (unlimited) in this case.

useCursors (default - false)

Instructs jTDS to use server side cursors instead of direct selects (AKA firehose cursors) for forward-only read-only result sets (with other types of result sets server- or client-side cursors are always used).
With firehose cursors the SELECT query is sent and the server responds with all the resulting rows. This is the fastest approach but it means that the driver has to cache all results if another request needs to be made before all rows have been processed. So when using multiple Statements per Connection it is preferable to have server-side cursors instead; these will allow the driver to request only a limited number of rows at a time (controllable through the fetchSize property of a Statement). This means extra request-response cycles, but less caching by the driver.
With SQL Server a so called fast forward-only cursor will be created when this property is set to true. With Sybase a usual forward-only read-only cursor is created.

bufferMaxMemory (default - 1024)

Controls the global buffer memory limit for all connections (in kilobytes). When the amount of buffered server response packets reaches this limit additional packets are buffered to disk; there is however one exception: each Statement gets to buffer at least <bufferMinPackets> to memory before this limit is enforced. This means that this limit can and will usually be exceeded.
Server responses are buffered to disk only when a request is made on a Statement while another Statement belonging to the same Connection still hasn't processed all its results. These situations can be avoided in most cases by setting the useCursors property, but this will also affect performance.
See also bufferMinPackets.

bufferMinPackets (default - 8)

Controls the minimum number of packets per statement to buffer to memory. Each Statement will buffer at least this many packets before being forced to use a temporary file if the <bufferMaxMemory> is reached, to ensure good performance even when one Statement caches a very large amount of data.
Server responses are buffered to disk only when a request is made on a Statement while another Statement belonging to the same Connection still hasn't processed all its results. These situations can be avoided in most cases by setting the useCursors property, but this will also affect performance.
See also bufferMaxMemory.

useLOBs (default - true)

Controls whether large types (IMAGE and TEXT/NTEXT) should be mapped by default (when using getObject()) to LOBs or Java types (String and byte[]). The default JDBC type constant returned is also controlled by this property: Types.BLOB for IMAGE and Types.CLOB for TEXT/NTEXT when true, Types.LONGVARBINARY for IMAGE and Types.LONGVARCHAR for TEXT/NTEXT when false.
This is useful when printing out directly the values returned by getObject() (e.g. when using JSTL or other frameworks), as Blob and Clob don't implement toString() (both because it's not required and because it can easily lead to OutOfMemoryErrors in unexpected situations, such as when logging data). The default setting of true has the advantage that the amount of data that is cached in memory for a large object can be controlled via the lobBuffer property; a setting of false will still use the Blob and Clob implementations internally but the values will be materialized to memory when getObject() is called, possibly leading to memory issues.

JDBC Driver Options

Contents

Schema definition reference