Home | FAQ | Documentation | Links | License | Project Info | Download | News | Bugs | Forums

Frequently Asked Questions

Getting Started

• What are the class names of the Driver, DataSource, ConnectionPoolDataSource, XADataSource and ObjectFactory implementations?
• What is the URL format used by jTDS?
• jTDS is supposed to be the fastest JDBC driver around. Have you got any figures to prove that? Or even better, a benchmark I can run myself?
• Which JDBC features are and which features are not supported by jTDS?
• Can jTDS be used in a multi threaded application?

Error Conditions

• Why do I get a ClassNotFoundError when calling Class.forName("net.sourceforge.jtds.jdbc.Driver")?
• Why do I get a java.sql.SQLException: "No suitable driver" when trying to get a connection?
• Why do I get java.sql.SQLException: "Network error IOException: Connection refused: connect" when trying to get a connection?
• Where does one place an instance name in the connect string? Connecting with "jdbc:jtds:sqlserver://host\instance:port/database" gives an SQLException with the message "Logon failed".
• Why do I get a java.sql.SQLException: "Unable to get information from SQL Server" when trying to connect to an SQL Server instance?
• I was hoping that appending ";domain=X" to the URL would be enough on a workstation that was already logged into domain X. Why do I still need to provide a username and password?
• executeQuery() throws java.sql.SQLException: "The executeQuery method must return a result set.".
• I get java.sql.SQLException: "ResultSet may only be accessed in a forward direction" or "ResultSet is read only" when using a scrollable and/or updateable ResultSet.
• Not all SQLExceptions thrown by jTDS seem to have associated meaningful SQL state values.
• Batch processing with executeBatch() hangs or is unreliable on Sybase.

Incorrect Behavior

• CallableStatement output parameter getter throws java.sql.SQLException: "Parameter X has not been set.".
• PreparedStatement.executeUpdate() returns an incorrect update count.
• Why do I get java.sql.SQLException: "Output parameter not allowed as argument list prevents use of RPC." when calling a stored procedure?

Memory Usage

• Memory usage keeps increasing when using generated PreparedStatements.

TDS Protocol Version

• I'm trying to connect to SQL Server 6.5, but it hangs when calling getConnection().
• Why do column names more than 30 characters long, get chopped off at 30 characters?
• jTDS fetches only the first 4Kb of IMAGE data.
• What do i have to do to connect to Netcool Omnibus (or any derived Product).

Technical Support

• Is there any commercial technical support available for jTDS?

Other

• I didn't find the answer to my problem in this FAQ. What should I do?

Getting Started

What are the class names of the Driver, DataSource, ConnectionPoolDataSource, XADataSource and ObjectFactory implementations?

^ top ^

What is the URL format used by jTDS?

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:

appName (default - "jTDS")

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

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; requires Java 1.4 or later)

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). Since after jTDS-1.2.

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.
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.

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.

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.
If a TCP/IP connection is used to connect to the database and Java 1.4 or newer is being used, the loginTimeout parameter is used to set the initial connection timeout when initially opening a new socket. A value of zero (the default) causes the connection to wait indefinitely, e.g.,until a connection is established or an error occurs. See also socketTimeout.
If a named pipe connection is used (namedPipe is true) and loginTimeout is greater than zero, the value of loginTimeout is used for the length of the retry period when "All pipe instances are busy" error messages are received while attempting to connect to the server. If loginTimeout is zero (the default), a value of 20 seconds is used for the named pipe retry period.

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.

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.
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.

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

The network packet size (a multiple of 512).

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.

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)

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.

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

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. 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.

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.

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. Since after jTDS-1.2.

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.

useNTLMv2 (default - false)

Set to true to send LMv2/NTLMv2 responses when using Windows authentication

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.

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).

Properties can be passed to jTDS in one of three ways: in the URL, in the Properties object passed to getConnection() or by using the JtdsDataSource's setters (if connections are obtained through a DataSource rather than using the DriverManager). Because there is no URL when using the JtdsDataSource there are three other properties (with setters and getters) to take the place of those items that are part of the URL's syntax: serverName, portNumber and databaseName (their meaning should be quite clear).

^ top ^

jTDS is supposed to be the fastest JDBC driver around. Have you got any figures to prove that? Or even better, a benchmark I can run myself?

Actually we do have benchmark results from two different benchmarks, both developed by large commercial SQL Server JDBC driver vendors to demonstrate the performance of their own drivers. In our oppinion this is better than having a benchmark of our own, which could be designed in such a way as to give jTDS the edge. These benchmarks are free to download so we encourage you to do it and run them yourself.

We have benchmarked jTDS against the two most used commercial drivers and of course the Microsoft driver and the JDBC-ODBC bridge, using these benchmarks: i-net software's BenchTest 2.1 for MS SQL Server and JNetDirect's JDBC Performance Benchmark. Here are the results of the i-net test: BenchTest 2.1 for MS SQL Server. The JNetDirect license precludes publication of performance test results. However, you can run any of the benchmarks yourself, the effort is minimal.

^ top ^

Which JDBC features are and which features are not supported by jTDS?

jTDS offers full support for all JDBC 3.0 features: forward-only and scrollable/updateable result sets, batch updates, prepared and callable statements, unlimited number of statements per connection, complete database and result set meta data, and a DataSource implementation (which also implements ConnectionPoolDataSource and XADataSource). Features such as generated keys retrieval, named stored procedure parameters, and save points are also implemented.

The only major features missing from jTDS are connection pooling and row sets; the reason for leaving these out is that there are free implementations available, probably much better than anything we could come up with. Check out the jTDS feature matrix for more details.

^ top ^

Can jTDS be used in a multi threaded application?

As a general principle we try and keep synchronization to a minimum both for performance and deadlock reasons. The only part of jTDS we guarantee is thread safe is the Connection object, and multi threaded access to Statements is discouraged (except for issuing cancels).

As a conclusion the only safe multithreading scenarios are these: (i) one Connection with multiple Statements, each Statement used by a single thread and (ii) a Statement used by one thread and cancelled by some other thread. Scenario (i), while it does work, is not necessarily a good solution because it requires a lot of locking and waiting on the same network connection plus (last but not least) a lot of caching.

^ top ^

Error Conditions

Why do I get a ClassNotFoundError when calling Class.forName("net.sourceforge.jtds.jdbc.Driver")?

ClassNotFoundError is thrown by the classloader when it can not find a certain class. In this case it's the net.sourceforge.jtds.jdbc.Driver class, which means that jtds.jar is not in the classpath. If you are getting this in an application, start your application with

    java -cp <path_to_jtds>/jtds.jar <main_class>

If you experience this problem in a servlet/JSP you will have to add jtds.jar to your web application's class path (e.g. by editing the application's web.xml file or by copying the jar into the application's/server's /lib directory).

Note: The name of the jTDS jar file may be jtds-1.2.jar or something similar. If that's the case, replace jtds.jar in the above example with jtds-1.2.jar or whatever your specific file name is.

^ top ^

Why do I get a java.sql.SQLException: "No suitable driver" when trying to get a connection?

The "No suitable driver" exception is thrown by the DriverManager when none of the registered Driver implementations recognizes the supplied URL. This means that you either did not register jTDS with the DriverManager first (by calling Class.forName("net.sourceforge.jtds.jdbc.Driver")) or you mistyped the URL (e.g. "jbdc:jtds:..." instead of "jdbc:jtds:...").

A common mistake is to append a semicolon (";") to the end of the URL (e.g. "jdbc:jtds:sqlserver://server/db;TDS=7.0;" is wrong!).

For more information about URL format and the properties that may be passed to jTDS have a look at the jTDS URL format.

^ top ^

Why do I get java.sql.SQLException: "Network error IOException: Connection refused: connect" when trying to get a connection?

The "Connection refused" exception is thrown by jTDS when it is unable to connect to the server. There may be a number of reasons why this could happen:

1. The server name is misspelled or the port number is incorrect.
2. SQL Server is not configured to use TCP/IP. Either enable TCP/IP from SQL Server's Network Utility app or have jTDS connect via named pipes (see the URL format for information on how to do this).
3. There is a firewall blocking port 1433 on the server.

To check whether TCP/IP is enabled and the port is not blocked you can use "telnet <server_host> 1433". Until telnet doesn't connect, jTDS won't either. If you can't figure out why, ask your network administrator for help.

^ top ^

Where does one place an instance name in the connect string? Connecting with "jdbc:jtds:sqlserver://host\instance:port/database" gives an SQLException with the message "Logon failed".

You will have to use the instance property (either append it to the URL or place it into the Properties you supply to getConnection) instead of doing it the Microsoft way. Sorry, but jTDS (and its ancestor FreeTDS) existed a long time before named instances so the URL could not be changed (and using it this way confuses the URL parser).

^ top ^

Why do I get a java.sql.SQLException: "Unable to get information from SQL Server" when trying to connect to an SQL Server instance?

The exception you are getting is usually caused by a timeout. When connecting to named instances jTDS needs to connect via UDP to port 1434 to get information about available SQL Server instances. While doing this it times out, throwing the exception you see (which means that jTDS was not able to get information about the running instances).

Connection timeouts occur when there is no server listening on the port (BTW, are you sure your SQL Server is configured to use TCP/IP and that you actually use named instances?).

On SQL Server 2005 the SQL Browser service must be running on the server host as the instance name lookup port UDP 1434 is hosted by this service on SQL Server 2005 rather than the SQL Server itself. The default install does not configure the SQL Browser service to start automatically so you must do it manually.

^ top ^

I was hoping that appending ";domain=X" to the URL would be enough on a workstation that was already logged into domain X. Why do I still need to provide a username and password?

jTDS is a type 4 (pure Java) JDBC driver. This means (among other things) that it cannot access platform-specific features, such as determining the currently logged user and his credentials. jTDS is distributed with a native library (DLL) for Single-Sign-On support, but that only works on Windows (please consult README.SSO in the distribution package for information on how to install it).

Without the native SSO library installed you still have to provide the username and password because otherwise jTDS cannot determine user credentials. There is a good side to this: users on non-Windows platforms are able to log in using Windows credentials by providing them in the URL, along with the domain name.

^ top ^

executeQuery() throws java.sql.SQLException: "The executeQuery method must return a result set.".

The JDBC spec and API documentation for Statement both state that executeQuery() is intended to be used with queries that return a ResultSet, typically SELECT statements. On the the other hand, executeUpdate() is intended for INSERT, UPDATE, DELETE or DDL statements that return update counts. Both of these (ResultSets and update counts) are considered by JDBC to be "results". For queries that return multiple results the JDBC spec requires execute() to be used.

If you'll look at the query you are trying to execute, you'll see that it probably returns an update count first, followed by a ResultSet. So according to the JDBC spec you should use execute() to run it, call getMoreResults() to skip the update count and then call getResultSet() to obtain the ResultSet you want.

As a sidenote, The Microsoft driver (and probably the others, too) "optimize" this behavior by skipping over the update count, which is wrong according to the JDBC spec. So, for maximum compatibility, it is recommended that you use execute() anytime you run queries returning more than one result even if the particular driver you are using allows you to do otherwise.

The same thing happens with the ODBC SQL driver and other connectors. A common solution rather than the inelegant execute() and then cycling through multiple result sets, is to supress the update counts for statements you are uninterested in. This is common if say you are querying a stored procedure that creates a temp table, runs updates against it and then returns the table as a result set. You can suppress all the extra "query results" by specifying "SET NOCOUNT ON".

^ top ^

I get java.sql.SQLException: "ResultSet may only be accessed in a forward direction" or "ResultSet is read only" when using a scrollable/updateable ResultSet.

There are three possible causes to this (if we exclude not creating the ResultSet with the appropriate type and concurrency in the first place):

1. The executed query must be a single SELECT statement or a call to a procedure that consists of a single SELECT statement (even a SET or PRINT will cause the resulting ResultSet to be forward only read only). This is a SQL Server limitation and there's not much jTDS can do about it.
2. The scroll insensitive/updateable combination is not supported by SQL Server, so such a ResultSet is automatically downgraded to scroll insensitive/read-only by the server. Use the scroll sensitive/updateable combination and it should work.
3. The other possible cause is that the cursor is keyset-based and either the table you are selecting from does not have a unique primary key or that primary key is not included in your SELECT. See the SQL Server Documentation on cursor types for more information.

In both cases if you call Statement.getWarnings() right after calling executeQuery() you'll get a warning about the ResultSet being downgraded. Also, please take a look at our ResultSet support page for some additional information and tips.

^ top ^

Not all SQLExceptions thrown by jTDS seem to have associated meaningful SQL state values.

Indeed, some SQLExceptions are reported with a correct state code and some not. As the server itself does not return an SQL state code, the SQLMessage class associates native error numbers with SQL state codes (actually there's a very comprehensive map there); SQL Server originated errors and warnings that are not found in this map are given a default code of 'S1000', respectively '01000'. This is what happens with SQL Server-returned errors and warnings and is consistent with how other drivers handle exceptions. We have done our best to map as many errors and warnings, however if you find some case in which other JDBC or ODBC drivers return specific SQL states and jTDS doesn't or jTDS returned SQL states that are obviously wrong, please let us know. We will make the necessary changes.

See the SQL 92 spec for the complete list and description of SQL state codes.

^ top ^

Batch processing using executeBatch() hangs or is unreliable on Sybase.

When executing large batches on Sybase the client application may hang or the response time may vary considerably. The client may be suspended if the system log fills so check that the log space is sufficient for the batch you are trying to load. You can control the real batch size using the batchSize parameter; setting it to a non-zero value will break up batches into smaller pieces on execution, hopefully avoiding the problem.

It is also recommended that you execute each batch in a transaction (i.e. with auto commit set to false).

^ top ^

Incorrect Behavior

CallableStatement output parameter getter throws java.sql.SQLException: "Parameter X has not been set.".

When executing a stored procedure (or any query, for that matter) the response from the SQL Server is serialized in the same order it is executed in: for stored procedures this means ResultSets and update counts first, output parameters and return value last. jTDS only caches update counts , not ResultSets because they could easily cause OutOfMemoryErrors or add important performance overheads if cached to disk (the response would have to be parsed twice, for example).

As a conclusion, when executing stored procedures that only return update counts output parameters will be available immediately, as jTDS will process and cache the whole response. But if the stored procedure returns at least one ResultSet jTDS will only cache the update counts up to the first ResultSet and you will have to call getMoreResults() to process all the results. So for procedures returning ResultSets you will have to loop, consuming all results before reading the output parameters (i.e. until getMoreResults() returns false AND getUpdateCount() returns -1).

In case you are wondering if this is right, this is a quote from the CallableStatement API documentation: "For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters". Although this means that a "good" driver could "fix" this behavior, fixing it would imply caching the whole server response, equaling a huge performance drop.

The exception is thrown because it's better to warn the user that the output parameters are not yet set instead of leaving the impression that all is ok and cause other, harder to detect problems.

^ top ^

PreparedStatement.executeUpdate() returns an incorrect update count.

This is caused by triggers; replication is also implemented through triggers, so you will encounter this issue on replicated databases too. The root cause is that triggers also return update counts and jTDS can't make the difference between these update counts and the "real" one (neither could any other SQL Server client). However, the update count you need is the last of them (because the actual UPDATE/INSERT/DELETE gets executed only after the triggers) and there is luckily a parameter you can specify in the URL (or the connection Properties or in the DataSource) in order for jTDS to return only this last update count (and ignore the rest of them). This parameter is lastUpdateCount and you can find out more about it here.

Please note that setting lastUpdateCount to true could cause problems if you use queries that do actually return more than one update count (such as queries consisting of multiple updates/inserts), because it will also return only the last of these update counts. Although queries returning multiple results are not very common, it is the reason why this behavior is customizable from the URL. Please note that this flag only alters the behavior of executeUpdate(); execute() will still return all update counts.

If you have both queries consisting of multiple UPDATEs/INSERTs/DELETEs and triggers you won't be able to set lastUpdateCount to true if you want all the update counts, so you will have to skip over the irrelevant update counts by calling getMoreResults() yourself, when needed.

^ top ^

Why do I get java.sql.SQLException: "Output parameter not allowed as argument list prevents use of RPC." when calling a stored procedure?

When calling a stored procedure that has output parameters, the driver has to call the procedure using a remote procedure call (RPC). Stored procedures should be invoked using the special JDBC call escape syntax. For example, {call sp_example(?,?)}. In this case the driver will be able to use an RPC succesfully as all the parameters are represented by parameter markers (?). If however parameters are supplied as a mixture of parameter markers and literals, for example {call sp_example('test',?)}, then the driver is unable to use an RPC and therefore cannot return output parameters. In these circumstances the driver raises an exception and execution fails.

It is possible to use mixed parameter lists to call stored procedures that do not have output parameters. In this case the driver will substitute the parameters locally and use a normal "execute procedure" SQL call; however, this mode of execution is less efficient than an RPC.

^ top ^

Memory Usage

Memory usage keeps increasing when using generated PreparedStatements.

Normally, with a JDBC driver, PreparedStatements are precompiled on creation, which gives increased performance if they are used a sufficient number of times. jTDS takes this one step further: when you create a PreparedStatement, jTDS caches it internally and keeps it there even after you close it so that every time you create it again it doesn't need to be recompiled, it's just fetched from the cache. This gives jTDS a 2x performance increase over the next fastest driver in some (not so uncommon) cases. For example, if you have a method that inserts a row into a table by creating a PreparedStatement, using it once and then closing it, your statement will be compiled only once for each Connection, no matter how many times you call that method.

The number of statements that are kept open simultaneously can be controlled with the maxStatements parameter; see the jTDS URL format for more information.

Future versions of jTDS will allow the type of cache (LRU, FIFO, etc) to be selected.

^ top ^

TDS Protocol Version

I'm trying to connect to SQL Server 6.5, but it hangs when calling getConnection().

jTDS uses by default TDS 8.0 (which is SQL Server 2000's protocol) to communicate with the database server. When jTDS sends the 8.0 login request SQL Server 6.5 doesn't understand it so it returns a 4.2 error (Login failed). But because jTDS is expecting a TDS 8.0 packet as a response (not a TDS 4.2 packet) it doesn't properly understand the response packet and it hangs waiting for more data from the server. The solution is to set the "TDS" property to "4.2" (through the URL or Properties object passed to getConnection() or using the appropriate setter of JtdsDataSource, if you are using the JtdsDataSource).

For more information about URL format and the properties that may be passed to jTDS have a look at the jTDS URL format.

^ top ^

Why do column names more than 30 characters long, get chopped off at 30 characters?

You are very probably using TDS 4.2 to communicate with the SQL Server. TDS 4.2 is the protocol used by SQL Server 6.5 and it has the limitations of SQL Server 6.5 (among which a maximum column name size of 30 characters). jTDS 0.2 and earlier use TDS 4.2 by default. In order to change that you have to specify it explicitly in the Properties or the URL you supply when you create the connection. If you experience this problem with versions 0.3 or later, then you have specified (in the connection properties or URL) that TDS 4.2 should be used; remove that setting.

For more information about URL format and the properties that may be passed to jTDS have a look at the jTDS URL format.

^ top ^

jTDS fetches only the first 4Kb of IMAGE data.

You are very probably using TDS 4.2 to communicate with the SQL Server. TDS 4.2 is the protocol used by SQL Server 6.5 and it has the limitations of SQL Server 6.5 (among which a maximum size for IMAGE data of 4Kb). jTDS 0.2 and earlier use TDS 4.2 by default. In order to change that you have to specify it explicitly in the Properties or the URL you supply when you create the connection. If you experience this problem with versions 0.3 or later, then you have specified (in the connection properties or URL) that TDS 4.2 should be used; remove that setting.

For more information about URL format and the properties that may be passed to jTDS have a look at the jTDS URL format.

What do i have to do to connect to Netcool Omnibus (or any derived Product).

You are required to use TDS 5.0 to communicate with the Omnibus Server and set a default charset to avoid SQL errors, so your URL will look like: jdbc:jtds:sybase://NCO:4100;TDS=5.0;charset=iso_1. Since the Omnibus Server understands only a limited subset of SQL, you cannot use prepared Statements.

^ top ^

Technical support

Is there any commercial technical support available for jTDS?

For the moment you can get official technical support from individual developers of jTDS; contact any of us and we'll be happy to provide you with our tech support terms and conditions. Of course, we will keep offering support on our SourceForge forums just as we did until now; we are only trying to encourage adoption of jTDS, not make a fortune out of it.

As for where the technical support money will go, part of them will be retained by the developer and part of them will go back into jTDS. Currently the aim is to raise US $2000, to pay for JDBC certification. We're 100% sure jTDS will pass the certification test suite, we just need the money for it. To this end, we also accept donations.

^ top ^

Other

I didn't find the answer to my problem in this FAQ. What should I do?

If you need help with any other jTDS-related issue, search the Help forum first and if you still don't find anything, post a question. It will usually get answered within 24 hours. One thing, though: please register as a SourceForge member and log in if you haven't done so yet, so you can be automatically notified of updates to the questions you post.

If you encountered an issue that you have tested and retested and you're sure it's a bug, use the Bugs link on top of the page. Again, make sure you are a SourceForge member AND that you are logged in when you post.

^ top ^