Driver
, DataSource
, ConnectionPoolDataSource
, XADataSource
and ObjectFactory
implementations?Interface | jTDS Implementation |
---|---|
java.sql.Driver | net.sourceforge.jtds.jdbc.Driver |
javax.sql.DataSource | net.sourceforge.jtds.jdbcx.JtdsDataSource |
javax.sql.ConnectionPoolDataSource | net.sourceforge.jtds.jdbcx.JtdsDataSource |
javax.sql.XADataSource | net.sourceforge.jtds.jdbcx.JtdsDataSource |
javax.naming.spi.ObjectFactory | net.sourceforge.jtds.jdbcx.JtdsObjectFactory |
<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'
)autoCommit
(default - 'true'
)batchSize
(default - 0
for SQL Server; 1000
for Sybase)bindAddress
(default - determined by the Java implementation; requires Java 1.4 or later)namedPipe
).bufferDir
(default - System.getProperty('java.io.tmpdir')
)bufferMaxMemory
and bufferMinPackets
.bufferMaxMemory
(default - 1024
)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.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.bufferMinPackets
.bufferMinPackets
(default - 8
)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.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.bufferMaxMemory
.cacheMetaData
(default - false
)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)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
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
lastUpdateCount
(default - true
)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
)Blob
data and chars for Clob
data.loginTimeout
(default - 0
for TCP/IP connections or 20
for named pipe connections)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
.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'
)maxStatements
(default - 500
)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
)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.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)password
(required)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)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'
)processId
(default - 123
)sendStringParametersAsUnicode
(default - true
)socketTimeout
(default - 0
)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
)ssl
(default - off
)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)VARCHAR
s 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.useCursors
(default - false
)Statement
s 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.true
. With Sybase a usual forward-only read-only cursor is created.useJCIFS
(default - false
)namedPipe
.useLOBs
(default - true
)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
.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 OutOfMemoryError
s 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)true
to send LMv2/NTLMv2 responses when using Windows authenticationuser
(required)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)xaEmulation
(default - true
)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
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).DataSource
implementation (which also implements ConnectionPoolDataSource
and XADataSource
). Features such as generated keys retrieval, named stored procedure parameters, and save points are also implemented.Connection
object, and multi threaded access to Statement
s is discouraged (except for issuing cancels).Connection
with multiple Statement
s, 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. 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 withjtds.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).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.java.sql.SQLException: 'No suitable driver'
when trying to get a connection?'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:..'
).'jdbc:jtds:sqlserver://server/db;TDS=7.0;'
is wrong!). java.sql.SQLException: 'Network error IOException: Connection refused: connect'
when trying to get a connection?'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:'jdbc:jtds:sqlserver://hostinstance:port/database'
gives an SQLException with the message 'Logon failed'. 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). java.sql.SQLException: 'Unable to get information from SQL Server'
when trying to connect to an SQL Server instance?';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.'
.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.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.execute()
anytime you run queries returning more than one result even if the particular driver you are using allows you to do otherwise.SET NOCOUNT ON
'.java.sql.SQLException: 'ResultSet may only be accessed in a forward direction'
or 'ResultSet is read only'
when using a scrollable/updateable ResultSet
.ResultSet
with the appropriate type and concurrency in the first place):ResultSet
is automatically downgraded to scroll insensitive/read-only by the server. Use the scroll sensitive/updateable combination and it should work.SELECT
. See the SQL Server Documentation on cursor types for more information.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.SQLException
s thrown by jTDS seem to have associated meaningful SQL state values.batchSize
parameter; setting it to a non-zero value will break up batches into smaller pieces on execution, hopefully avoiding the problem. CallableStatement
output parameter getter throws java.sql.SQLException: 'Parameter X has not been set.'
.ResultSet
s and update counts first, output parameters and return value last. jTDS only caches update counts , not ResultSet
s because they could easily cause OutOfMemoryError
s or add important performance overheads if cached to disk (the response would have to be parsed twice, for example). 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 ResultSet
s you will have to loop, consuming all results before reading the output parameters (i.e. until getMoreResults()
returns false AND getUpdateCount()
returns -1).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.PreparedStatement.executeUpdate()
returns an incorrect update count.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.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.UPDATE
s/INSERT
s/DELETE
s 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.java.sql.SQLException: 'Output parameter not allowed as argument list prevents use of RPC.'
when calling a stored procedure?PreparedStatements
. PreparedStatement
s 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.maxStatements
parameter; see the jTDS URL format for more information.getConnection()
.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
).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.IMAGE
data.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.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.