jTDS JDBC Driver Connection Properties and Defaults

Q

What is connection properties are supported by jTDS JDBC Driver? What are their default propertie values?

✍: FYIcenter.com

A

Connection properties supported by jTDS JDBC driver and property default values are listed below:

  • user (required) - User name to use for login. When using getConnection(String url, String user, String password) it's not required to set this property as it is passed as parameter, but you will have to set it when using getConnection(String url, Properties info) or JtdsDataSource.
  • password (required) - Password to use for login. When using getConnection(String url, String user, String password) it's not required to set this property as it is passed as parameter, but you will have to set it when using getConnection(String url, Properties info) or JtdsDataSource.
  • appName (default - "jTDS") - Application name. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.
  • autoCommit (default - "true") - Auto Commit. The jTDS driver enables auto commit by default. Use this option to disable auto commit for newly created connections.
  • 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.
  • bindAddress (default - determined by the Java implementation) - Specifies the local IP address to bind to for outgoing TCP/IP connections to the database. Useful for multi-homed systems (those with more than one external IP address) where the default IP address picked by Java will not connect to the database. Currently has no effect when using named pipes to connect to a database (see namedPipe).
  • bufferDir (default - System.getProperty("java.io.tmpdir")) - Controls the destination where data is buffered to disk. See also bufferMaxMemory and bufferMinPackets.
  • 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. 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. See also bufferMaxMemory.
  • 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.
  • 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.
  • 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.
  • 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.
  • loginTimeout (default - 0 for TCP/IP connections or 20 for named pipe connections) - The amount of time to wait (in seconds) for a successful connection before timing out.
  • macAddress (default - real MAC address if available, otherwise "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 is determined automatically from the network interface used to connect the server (or a random non-loopback network interface if using named pipes to connect to a database).
  • 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.
  • 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.
  • packetSize (default - 4096 for TDS 7.0/8.0; 512 for TDS 4.2/5.0) - The network packet size (a multiple of 512).
  • prepareSQL (default - 3 for SQL Server, 1 for Sybase) - This parameter specifies the mechanism used for Prepared Statements.
  • progName (default - "jTDS") - Client library name. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.
  • processId (default - 123) - The client process ID associated with the connection. Must be an integer value or the string "compute" to let jTDS choose a process ID.
  • 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.
  • socketTimeout (default - 0) - The amount of time to wait (in seconds) for a server response 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 or named pipe connection will be closed. This parameter may be useful for detecting dead network connections in a pooled environment. See also loginTimeout. If using named pipes via JCIFS the timeout cannot be disabled completely. A timeout of about 25 days (2^31 ms) is applied instead.
  • socketKeepAlive (default - false) - true to enable TCP/IP keep-alive messages
  • ssl (default - off) - Specifies if and how to use SSL for secure communication.
  • tcpNoDelay (default - true) - true to enable TCP_NODELAY on the socket; false to disable it.
  • 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.
  • 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).
  • useJCIFS (default - false) - Controls whether the jCIFS library will be used instead of the local file system with named pipe connections on the Windows operating system. (The jCIFS library will always be used with named pipes when the operating system is not Windows.) Useful when connecting via named pipes to a server that is located in a different domain than the client. See also namedPipe.
  • 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.
  • useNTLMv2 (default - false) - Set to true to send LMv2/NTLMv2 responses when using Windows authentication.
  • useKerberos (unsupported in jTDS 1.2.x, default - false) - Set to true to use Kerberos authentication.
  • wsid (default - the client host name) - Workstation ID. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.
  • 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).

 

Examples for jTDS - JDBC Driver for SQL Server

⇒⇒FAQ for jTDS - JDBC Driver for SQL Server

2017-02-03, 791👍, 0💬