sql driver.html,jTDS JDBC Driver

Frequently Asked Questions

Getting Started

Error Conditions

Incorrect Behavior

Memory Usage

TDS Protocol Version

Technical Support

Other

Getting Started

What are the class names of the Driver,

DataSource, ConnectionPoolDataSource, XADataSource and

ObjectFactory implementations?

InterfacejTDS Implementation

java.sql.Drivernet.sourceforge.jtds.jdbc.Driver

javax.sql.DataSourcenet.sourceforge.jtds.jdbcx.JtdsDataSource

javax.sql.ConnectionPoolDataSourcenet.sourceforge.jtds.jdbcx.JtdsDataSource

javax.sql.XADataSourcenet.sourceforge.jtds.jdbcx.JtdsDataSource

javax.naming.spi.ObjectFactorynet.sourceforge.jtds.jdbcx.JtdsObjectFactory

What is the URL format used

by jTDS?

The URL format for jTDS is:

jdbc:jtds:://[:][/][;=[;...]]

where is one of either 'sqlserver'

or 'sybase' (their meaning is quite obvious),

is the port the database server is listening to (default is 1433 for SQL

Server and 7100 for Sybase) and 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.

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

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

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 "\"

instead of the usual "". 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.

ValueDescription

0SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower)

1Temporary stored procedures are created for each unique SQL statement and parameter combination (faster)

2sp_executesql is used (fast)

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

ValueDescription

offSSL is not request or used; this is the default

requestSSL is requested; if the server does not support it then a plain connection is used

requireSSL is requested; if the server does not support it then an exception is thrown

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

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

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.

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.

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.

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 /jtds.jar

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.

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.

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:

The server name is misspelled or the port number is incorrect.

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

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 1433". Until telnet doesn't connect,

jTDS won't either. If you can't figure out why, ask your network

administrator for help.

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

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.

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.

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

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

Technical support

Is there any commercial

technical support available for jTDS?

For the moment you can get technical support from individual

developers of jTDS. Of course, we will keep

offering support on our SourceForge forums just as we did until now.

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.

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.