Manual for the MS SQL Server JDBC type 4 drivers UNA, SPRINTA, OPTA and MERLIA

Version: 8.08
Last Modified: 22. Mar 2017

Table of Contents

1. Installation

1.1 Using the Driver in a Java Application

The zip file of the driver includes a jar file. The jar file is named like the driver (una.jar, sprinta.jar, opta.jar or merlia.jar). You need to add this jar file to your classpath or extract the jar file in the directory of the application.

1.2 Using the Driver in an Applet

Add the driver jar file to the archive attribute of the applet tag, e.g.:

<APPLET CODE="..." Codebase="..." ARCHIVE="Merlia.jar" WIDTH="100%" HEIGHT="100%">
...
</APPLET>

or extract the jar in the directory of the applet.

2. Getting Started

2.1 SQL Server, Java and JDBC Versions

Product minimum required JDK version JDBC Version
UNA 1.1 1.22
SPRINTA 1.2 2.0
OPTA 1.2 2.0 + Optional Package
MERLIA 1.4 3.0
4.0 (with Java 6.0)

All drivers support the SQL Server versions 6.5 - 2016 with all Service Packs (32 bit / 64 bit).

2.2 Implemented Features

UNA SPRINTA OPTA MERLIA
JDBC API 1.22 2.0 2.0+ 4.0
PreparedStatement pooling Yes Yes Yes Yes
Scrollable and Updatable ResultSets - Yes Yes Yes
Connection Pooling - - Yes Yes
DataSource - - Yes Yes
Pooled Driver with Internal Pooling - - Yes Yes
Pool Manager i-net PLEXA - - Yes Yes
JDBCRowSet - - Yes Yes
Distributed Transactions (XA) - - Yes Yes
SSL - - Yes Yes
Savepoints on Transactions - - - Yes
Multiple Open ResultSets per Statement - - - Yes
ResultSet Holdability - - - Yes

A more complete list can be found at at: http://www.inetsoftware.de/products/jdbc-driver/ms-sql/features

2.3. Check Host Name and Port Number of Your Server

This driver works with Microsoft SQL Servers configured to use the TCP/IP networking protocol. Please verify that your server is currently listening on a TCP/IP port.

If you know that your SQL Server is listening on a TCP/IP port and you know the host name of your SQL Server and the port number you can go to the next chapter. To check or enable the TCP/IP Sockets protocol follow these steps:

For Microsoft SQL Server 6.5:

Click -SQL Setup- in the MS SQL Server program group. If not selected, select -Change Network Support-, select -TCP/IP- and enter the port number you want to use (default port: 1433). If -Change Network Support- is selected, then cancel the setup.

For Microsoft SQL Server 7.0:

Click -SQL Server Network Utility- in the Microsoft SQL Server 7.0 program group. On the general property sheet, click -Add- and select -TCP/IP- under Network libraries. Enter the port number and the proxy address (if nesessary) and click OK.

The default port number for the Microsoft SQL Server is usually 1433. However servers can be configured to listen on any port number. To make sure that the RDBMS server is listening on the machine name and port number you specified use:

telnet <hostname or ip address> <port number>

If the connection is refused then the hostname or the port number is incorrect.

2.4 Login Types

The SQL Server supports three authentication types. These are:

  • SQL Server authentication
  • Windows NT authentication (trusted connection)
  • Mixed (or both) authentications

Windows authentication will only work from a Windows client from the same domain. It is also required that your program runs with a user account that has access to the SQL Server. If you want to connect from another client or account then you’ll need to use an SQL Server login, in which case you’ll need to enable “SQL Server authentication” or “Mixed authentication”. To verify the authentication type:

  • Select the SQL Server in the Enterprise Manager
  • Open the properties dialog
  • Select the security tab

If you want to use “Windows NT authentication” with i-net software’s drivers then the username must be empty. This only works with SQL Server 7.0 or higher and from a Windows client. Together with Named Pipes connections you can use Windows NT authentication since Java version 1.1.7. Together with TCP/IP connections, you can use it since Java version 1.4.2_03 and driver version 7.01.

2.5 Driver Class Name

The class name of the driver is: com.inet.tds.TdsDriver

The name of the pooled driver is: com.inet.pool.PoolDriver
The pooled driver is only part of OPTA and MERLIA.

The following DataSources are only available in OPTA and MERLIA

com.inet.tds.TdsDataSource This is a simple DataSource. Also named JDBC 1 DataSource.
com.inet.tds.PDataSource This is pooled DataSource.
com.inet.tds.DTCDataSource This is an XADataSource. It works in conjunction with the MS DTC (Distributed transaction Coordinator). You need to install the the DTCProxy on the MS SQL Server side. You should use XA only if you it need. Using XA may result in a considerable performance decrease.

For a list of DataSource Properties look in the API documentation.

2.6 JDBC URL Subprotocols

jdbc:inetdae7: …. support of the SQL Server 7.0 (or higher) feature with unicode data types nText, nVarchar and nChar (the same as …&sql7=true)
jdbc:inetdae7a: …. support of the SQL Server 7.0 (or higher) feature with ASCII data types Text, Varchar and Char. Use this protocol if using i-net MERLIA and JDBC 4.0 API compliance is required. see character converting JDBC 4.0 API
jdbc:inetdae6: …. SQL Server 6.5 compatible mode (passwords are transfered plainly)
jdbc:inetdae: …. only for compatibility with older versions
jdbc:inetpool: …. pooled driver (only OPTA and MERLIA)

2.7 JDBC URL Syntax of the driver

jdbc:inetdae7:hostname:portnumber
jdbc:inetdae7:hostname –> with default port 1433
jdbc:inetdae7:hostname:portnumber?database=MyDb&language=us_english –> with properties
jdbc:inetdae7:hostname/instancename –> with named instance of MS SQL Server 2000 and 2005, required the UDP port 1434
jdbc:inetdae7:servername/pipe/pipename | –> with named pipes | | jdbc:inetdae7:servername/pipe/MSSQL$instancename/sql/query –> named instance

2.8 JDBC URL Syntax of the Pooled Driver

The pooled driver is only part of OPTA and MERLIA.

  • jdbc:inetpool:<subprotocol with parameter>
  • jdbc:inetpool:jdbc:<subprotocol with parameter>

Examples:

  • jdbc:inetpool:inetdae7:www.inetsoftware.de:1433
  • jdbc:inetpool:inetdae7:localHost:1433
  • jdbc:inetdae7:210.1.164.19:1433
  • jdbc:inetdae7:[2002:d201:a413::d201:a413]:1433
  • jdbc:inetpool:inetdae7://MyServer/pipe/sql/query
  • jdbc:inetpool:jdbc:inetdae7:www.inetsoftware.de:1433
  • jdbc:inetpool:jdbc:inetdae7:localHost:1433
  • jdbc:inetpool:jdbc:inetdae7://MyServer/pipe/sql/query

2.9 Implemented Driver Properties

database default is the user default database
language default is ““
”” –>SQL Server default language
user the database user, if empty the current windows user is used
password password of the database user
charset see Character Converting
initSQL This expression is executed once per connection.
nowarnings “true” getWarnings() returns null
logging default is “false”
If the value is “true” and the JDBC logging is not enabled then the driver will set it to System.out.
sql7 default is “false” with “inetdae”
“true” the new datatypes are supported
this property works only with the classic subprotocol “inetdae”
secureLevel default is 1; set the level of security with SSL.
prepare default is “true”, PreparedStatements are prepared on the SQL Server,
“false” the driver sends for every call of execute() all data
prepareLiveTime default is 60
Set the time in seconds that an unused PreparedStatement handle is live before it is unprepared. A value of 0 means an unlimited live time.
lastUpdateCount default is “false”
If the value is “true” then the method executeUpdate() should return the last update count. This property is helpful if you use PreparedStatements on tables with triggers. In this case the first update count is the count of the trigger. With this property you can receive the last update count which is ever the count of your UPDATE or INSERT expression. This property make problems with expression that return multiple update counts. In this case you receive also only the last update count.
loginTimeout override the value from DriverManager.getLoginTimeout()
queryTimeout default is 5 * loginTimeout. Set the default query timeout for all Statements.
JAVA_OBJECT default is “true”
The method getObject() converts valid Java serialized data in IMAGE columns to a Java Object. This is required to implement the type JAVA_OBJECT but it can be fatal if you implement your own Object serialization. If you use your own Java serialization then the best solution is to use getBytes() and not getObject().
appname Application name (only Application and Enterprise licenses)
WSID Workstation ID. The default value is the host name of the local computer. With the WSID you can set the return value of the transact SQL method HOST_NAME().
useCursorsAlways default is “false”
“true” the method executeQuery always uses server cursors.
resultSetType default is “1003” (ResultSet.TYPE_FORWARD_ONLY)
A list of valid values can find in chapter Scrolling Cursor Types.
useInsteadOfTrigger default is “false”
“true” - in a query with multiple tables all are updated in one step
with updateRow(). You need a INSTEAD OF trigger.
impltran with Driver the default is “true” for compatibility with the JDBC-ODBC bridge.
with DataSource the default is “false” to reduce deadlock problems
If the value is true then the method setTransactionIsolation() will call setAutoCommit( false ) implicitly and start a transaction.
fulltran default is “true”, creates always a transaction with setAutoCommit( false );
“false” only if needed With the option fulltran=true the method setAutoCommit() uses “BEGIN TRAN” and “COMMIT TRAN”. If the flag auto commit is set then there will always be a running transaction.With fulltran=false the method setAutoCommit() uses “SET IMPLICIT_TRANSACTIONS ON” and “SET IMPLICIT_TRANSACTIONS OFF”. If the flag auto commit is set then the transactions will only start if needed.More information on these SQL expressions and their behavior can be found in the MSSQL Server Online Manual.
dstsave default is true
Effects the way timezone offset is calculated. The ways differ in speed and safety. If true the slower way is used, but this way is bug free. If false, the faster way is used (2-3 times faster), but this way has a Java bug for some time zones at the day the day saving time switches. The bug there is a time displacement of one hour for some hours of the day.
statusRequest Set the mode with which the API requests the status of a connection. There are 2 possible modes: * safe (default) - an additional round trip to the database is executed. * fast - the current value of an internal variable is returned. Currently this has only an effect for getTransactionIsolationLevel()
fastbatch default is true
Control if executeBatch() should use one round trip or execute every command as a single command. If fastbatch=true then the resulting update counts can be wrong if every single command does not produce a single count. This can occur with triggers. If you need an exact update count then you should use fastbatch=false.
fetchsize i-net UNA only, the method executeQuery() always uses server cursors with this fetch size. The other drivers can use Statement.setFetchSize().
host override the value in the JDBC URL. This is the host name of the SQL Server. But it can be also a VNN (virtual network name) if you use also the property “instance”.
instance override the value in the JDBC URL, required the UDP port 1434
port override the value in the JDBC URL
failover see chapter “Failover on getConnection()”
mars Enables (“true”) or disables (“false”) the MARS (multiple active resultsets) mode. In this mode the data of ResultSets will be fetched on demand and not alltogether. This property can be used when connecting to SQL Server 2005 only.
packetSize Sets the network packet size. The default value is 8000. The value can be between 512 and 32767 or 0. A value of 0 means to use the server default value which is by default 4096.
mode Only for DataSources. The value is equals to the JDBC sub protocol. See the API documentation for more details.
typeMapping default is jdbc4
Which data type constant should be used from the driver. Available options are jdbc3, jdbc4 and odbc. Since driver version 7.0 jdbc4 is the default value. Before it was odbc. The difference between the options are the new data type of JDBC4 like NCHAR and NVARCHAR.
sqlxml default is false, if true then getObject(x) returns an instance of SQLXML if the data type is XML.
applicationIntent Declares the application workload type when connecting to a database in an SQL Server Availability Group. Possible values are ReadWrite and ReadOnly.

There are three ways to put the properties to the driver:

  1. append the properties to the URL like jdbc:inetdae:hostname:portnumber?database=MyDb&language=deutsch
  2. call method getConnection(String url, Properties info) from the driver manager.
  3. create a DataSource and set the properties with the set methods.

2.10 Connections to the MSDE 2000 Release A

You need to enable “Mixed Authentification” and enable the TCP/IP network protocol if you do not want to use named pipes. For example, you can use the following line for the setup of the MSDE:

setup SAPWD=“password” SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0

Comments of the switches:

SAPWD=“password”
Set the password for the sa account. This is required for Windows Server 2003.

SECURITYMODE=SQL
Set the Authentfication to “Mixed Mode”. This is required to connect via TCP/IP with a non Windows program like Java.

DISABLENETWORKPROTOCOLS=0
Enable the TCP/IP protocol

2.11 Connection Examples

Eight examples are included in the download file.

ClassicDriver.java

An example to demonstrate how to connect and use the driver with the JDBC 1.22 interface.
(This driver requires the JDBC 2.0 interface and a JDK 1.2 or higher)

PooledDriver.java

This example shows the use of the PoolDriver. A driver with internal
connection pooling.

WithDataSource.java

How to create a connection with a datasource.

WithPoolDataSourece.java

How to use the PooledConnection of the driver.

WithPoolManager.java

This demonstrates the use of the PoolManager i-net PLEXA™. i-net PLEXA™ is an example of a pool manager. i-net PLEXAtm) comes with i-net OPTA™.

WithJDBCRowSet.java

How to use the JDBCRowSet from i-net software (i-net OPTA™).

WithJNDI.java

This demonstrates the use of a DataSource with a JNDI directory.

WithJndiAndPoolManager.java

This demonstrates the use of a ConnectionPoolDataSource with a JNDI directory and the PoolManager (i-net PLEXA™).

Package sample.xa

A sample with EJB and a XADataSource.

3. Named Pipes

Another solution to connect to the SQL Server are named pipes. Named pipes work only in the Java VM 1.1.7 or higher. Named pipes are equal to files with a UNC path. We have tested named pipes only with the Win32 VM from Sun. If you want to use named pipes from another platform, you have to install SMB (server message block) on the client or you must install NFS (network file system) on the SQL server.

The default pipe of the SQL server is ”/sql/query”, but you can change this pipe name in the server network manager.

4. Escape Clauses

The driver implements all escape clauses as follows:

4.1 Date and Time

{d 'yyyy-mm-dd'}
{t 'hh:mm:ss[.fff]'}
{ts 'yyyy-mm-dd hh:mm:ss[.fff]'}

4.2 Stored Procedures

{call storedProcedures('Param1'[,'Param2'][,?][...])}
{? = call storedProcedures('Param1'[,'Param2'][,?][...])}

examples:

st = con.createStatement();
st.execute("{call MyProc('value')}");
 
pr = con.prepareStatement("{call MyProc('value')}");
pr.execute();
 
pr = con.prepareStatement("{call MyProc( ? )}");
pr.setString("value");
pr.execute();

4.3 Functions

Numeric Functions

{fn ABS(number)}
{fn ACOS(float)}
{fn ASIN(float)}
{fn ATAN(float)}
{fn ATAN2(float1,float2)}
{fn CEILING(number)}
{fn COS(float)}
{fn COT(float)}
{fn DEGREES(number)}
{fn EXP(float)}
{fn FLOOR(number)}
{fn LOG(float)}
{fn LOG10(float)}
{fn PI()}
{fn POWER(number, power)}
{fn RADIANS(number)}
{fn RAND(integer)}
{fn ROUND(number, places)}
{fn SIGN(number)}
{fn SIN(float)}
{fn SQRT(float)}
{fn TAN(float)}

Time and Date Functions

{fn now()}
{fn curdate()}
{fn curtime()}
{fn DAYNAME(datetime)}
{fn DAYOFMONTH(datetime)}
{fn DAYOFWEEK(datetime)}
{fn DAYOFYEAR(datetime)}
{fn HOUR(datetime)}
{fn MINUTE(datetime)}
{fn MONTH(datetime)}
{fn MONTHNAME(datetime)}
{fn QUARTER(datetime)}
{fn SECOND(datetime)}
{fn WEEK(datetime)}
{fn YEAR(datetime)}
{fn TIMESTAMPDIFF(interval, count, datetime)}
{fn TIMESTAMPADD(interval, datetime, datetime2)}

interval may be one of the following:

SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

4.4 Outer Join

{oj table1 LEFT OUTER JOIN table2 ON table1.id = table2.id} 
{oj table1 RIGHT OUTER JOIN table2 ON table1.id = table2.id}

4.5 Encrypt

{encrypt N'password'}

5. Character Converting

By default character converting is disabled. To use character converting in the driver you need to append “charset=YourCharSet” to the url.

for example: “jdbc:inetdae:localhost:1433?charset=Cp1250”
or “jdbc:inetdae:localhost:1433?charset=” + sun.io.ByteToCharConverter.getDefault().getCharacterEncoding();
or “jdbc:inetdae:localhost:1433?charset=” + System.getProperty(“file.encoding”);
or “jdbc:inetdae:localhost:1433?charset=” + (new java.io.InputStreamReader(in)).getEncoding();

TIP: The property charset is case-sensitive in JAVA. The name of the character set is also
case-sensitive.

You can test the availability of a character set with:
“test string”.getBytes( charset );
If this line executes correctly the character set is available in the current VM.

The parameter charset is ignored on columns with nchar, nvarchar, and ntext. To save
national characters with this columns you need to use the character N.
Example: “INSERT INTO myTable(ntext field) VALUES( N'national text' )”

5.1 JDBC 4.0 API

JDBC 4.0 API is supported with i-net Merlia driver running in Java 6 environment. With JDBC 4.0 comes new functions to set unicode strings explicit, e.g. PreparedStatement.setNString(int,String). All previous functions like PreparedStatement.setString(int,String) are reduced to support ASCII characters only. If you need this compatibility then you have to use “inetdae7a” subprotocol.
If you want all String setter functions support unicode characters, use “inetdae7” subprotocol.

6. New Datatypes

6.1 New Datatypes with SQL Server 7.0 and 2000

The SQL server 7.0 and 2000 supports new datatypes, i.e. nchar, ntext, nvarchar, bigint, variant, varchar larger than 255 characters.

You can use the new datatypes if you use the jdbc url “jdbc:inetdae7:…” or set the property sql7=true.

If you set the property sql7=true you will not be able to connect to the SQL Server 6.5.

If you want use the ASCII data types Text, Varchar(8000) and Char(8000) then you should use the subprotocol inetdae7a.

The latest version of the ODBC-JDBC-Bridge returns the type value Types.OTHER for this data types. This is not helpful. That's why our driver returns the original SQL99 that the SQL Server sends. The old version of the ODBC-JDBC-Bridge returns also this values.

  • -8 - NCHAR
  • -9 - NVARCHAR
  • -10 - NTEXT
  • -11 - UNIQUEIDENTIFIER
  • -150 - SQL_VARIANT

6.2 New Data types with SQL Server 2005

SQL Server 2005 supports the following new data types:

  • XML

and the enhanced data types:

  • Varchar(max)
  • NVarchar(max)
  • Varbinary(max)

Only i-net Opta and i-net Merlia provide full support of new data types. For using the new or enhanced data types of the SQL Server 2005(Yukon) you don't need to use a new sub protocol or set a special property.

If connecting to a SQL Server 2005, the driver switches to the new communication protocol and the new data types will be enabled automatically internal.

6.3 New Data types with SQL Server 2008

With version 8 of the driver i-net Opta and i-net Merlia supports the following new data types of SQL Server 2008 and returns Java objects:

  • DATE
  • TIME
  • DATETIME2
  • DATETIMEOFFSET

The other new data types:

  • GEOGRAPHY
  • GEOMETRY
  • HIERACHYID

are serialized .NET objects. There is no equivalent in Java currently. The values return as binary (byte array). If you want to use these values in Java then contact the support. We will help you with a solution.

7. PreparedStatement Performance and Data Type Mapping

7.1. Data Type Precedence

When two expressions of different data types are combined by an operator, the data type precedence rules specify which data type is converted into the other. The data type with the lower precedence is converted to the data type with the higher precedence.

This is the precedence order for the MS SQL Server data types:

  • sql_variant (highest)
  • datetime
  • smalldatetime
  • float
  • real
  • decimal
  • money
  • smallmoney
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • ntext
  • text
  • image
  • timestamp
  • uniqueidentifier
  • nvarchar
  • nchar
  • varchar
  • char
  • varbinary
  • binary (lowest)

7.2 Data Type Mapping

If you use the set methods of a PreparedStatement then you will have the following mapping:

Method MS SQL Data Type with protocol inetdae7 MS SQL Data Type with protocol inetdae7a
setBoolean BIT ..
setByte TINYINT ..
setShort SMALLINT ..
setInt INT ..
setLong BIGINT ..
setFloat REAL ..
setDouble FLOAT ..
setBigDecimal DECIMAL ..
setString ⇐ 4000 NVARCHAR
> 4000 NTEXT
⇐ 8000 VARCHAR
> 8000 TEXT
setAsciiStream ⇐ 8000 VARCHAR
> 8000 TEXT
..
setClob ⇐ 4000 NVARCHAR
> 4000 NTEXT
⇐ 8000 VARCHAR
> 8000 TEXT
setCharacterStream ⇐ 4000 NVARCHAR
> 4000 NTEXT
⇐ 8000 VARCHAR
> 8000 TEXT
setDate DATETIME ..
setTime DATETIME ..
setTimestamp DATETIME ..
setBytes ⇐ 8000 –> VARBINARY
> 8000 –> IMAGE
..
setBinaryStream IMAGE ..
setBlob IMAGE ..

7.3 The Performance Problem

If you use a set method that map to a data type with a higher precedence then the MS SQL Server will convert the value in the database column and not the parameter. The MS SQL Server can't use an existing index because it was created for a different data type. The execution will be slow.

A simple statement can be faster because the MS SQL Server needs to use the execution plan only once. In this case the MS SQL Server can detect that it can convert downward the static parameter.

Solutions:
1.) Use the correct set method.

2.) Use a cast around the parameter to inform the MS SQL Server that it can convert the parameter. This looks like:
… cast(? as TINYINT)

3.) Change the data type of the table.

7.4 Numeric columns and SQL Server 2000 SP4

BigDecimal parameter send by driver will processed up to 20% slower. This happens if parameter definition send by Driver don't match exactly the column type. For example Driver sends parameter as “decimal(38,36)”, the column type is “numeric (7,0)”. It seems that the index for the numeric column won't used with SP4. With SQL Server 2005 or earlier SQL Server version such a behavior was not seen. A workaround for that problem is to cast to column type.
E.g. “ “select * from testnumeric where a = CAST(? AS numeric(7,0))””

8. Scrolling Cursor Types

id Description
TYPE_FORWARD_ONLY-1 Fast-Forward A forward only cursor with activated optimation.
TYPE_FORWARD_ONLY Forward-only The fetch functions will allow only a fetchtype of FIRST, NEXT, or RELATIVE with a positive rownum.
TYPE_SCROLL_INSENSITIVE Insensitive keyset cursor Use a concurrency of CONCUR_READ_ONLY. SQL Server will generate a temporary table, so changes made to the rows by others will not be visible through the cursor. The fetch functions will allow all fetchtype values.
TYPE_SCROLL_SENSITIVE Keyset cursor The fetch functions will allow all fetchtype values.
TYPE_SCROLL_SENSITIVE+1 Dynamic cursor The fetch functions will allow all fetchtype values except RANDOM. All position-reporting methods returns always false.

9. Locking and Concurrency Control

Concurrency type is one of the following concurrency control options.

concurrency type Description
CONCUR_READ_ONLY Read-only cursor. You cannot modify rows in the cursor result set.
CONCUR_UPDATABLE Optimistic concurrency control using timestamp or values. Changes to a row that are initiated through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing timestamps or by comparing all nontext, nonimage values if timestamps are not available.
CONCUR_UPDATABLE+1 Intent to update locking. Places an update intent lock on the data page that contains each row as it is fetched. If not inside an open transaction, the locks are released when the next fetch is performed. If inside an open transaction, the locks are released when the transaction is closed.
CONCUR_UPDATABLE+2 Optimistic concurrency control using values. Changes to a row through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing all nontext, nonimage values.

10. Failover

10.1 Failover on getConnection()

If you set the property failover=true in the JDBC URL or in a DataSource then after an error has occurred the driver tries to connect to a failover server. All properties in the JDBC url or DataSource that start with “failover” overwrite the properties from the first connection. All other properties will still be in effect. Therefore you can set all or only some properties (e.g. host, port, instance user, password) for the failover connection.

For example:
jdbc:inedae7:YourHost?database=northwind&failover=true&failoverhost=YourHost2
jdbc:inedae7:YourHost?failover=true&failoverport=1500

To set more than one failover possibility, use the following syntax:
jdbc:inedae7:YourHost?failover=true&host1=AFurtherHost&port1=AFurtherPort&host2=AFurtherHost&port2=AFurtherPort
host[N] and port[N] must form pairs, otherwise they will be ignored.

10.2 Failover with Clusters

With the interface FailoverRunnable you have the possibility to write program code that will be executed correctly in the case of a failure of the cluster server, also. You can configure it with the methods setFailoverTimeout() and setFailoverCount(). For more information please refer to the API documentation of the PoolManager.

10.3 Monitored Failover

With the interface FailoverListener it is possible to manage the reconnection process. The FaloverListener receives an event if the Connection was broken, if the reconnection process was successful and if an error occurred be the try of reconnect. In depend of the value returned from FailoverListener, a new try of reconnect will be started or not. The Connection tryes to reconnect to the same server with the same properties. If reconnection was successful, all Statements, PreparedStatements and CallableStatements will be restored. The ResultSets won't be restored. For restore that, you have to executed the Statement again.
For more information see com.inet.pool.PoolManager.addFailoverListener(), com.inet.pool.FailoverListener, com.inet.pool.FailoverEvent and sample.MonitoredFailover

10.3.1 Configuration via Connection properties

There are multiple possibilities about how to configure the Monitored Failover.
It is possible to use the default implementation of the FailoverListener or to use your own implementation.
The default FailoverListener is similar to the TimedFailoverListener in the samples package.
To every created Connection automatically an instance of the configured FailoverListener will be added.

Set a FailoverListener via Connection properties:

name default description
useDefaultFailoverListener false if set on true, a default FailoverListener will be added automatically to every Connection
waitTimeToNextAttempt 500 property for the default FailoverListener. Sets the wait time in millis, that will be wait to the next reconnect attempt.
totalTimeOfAttempts 30 property for the default FailoverListener. Sets the total time in seconds, in that the reconnection process must be finished.
failoverListener - the class name of your own FailoverListener implentation. This class has to implement the FailoverListener interface AND the default public constructor. This is important to create a new instance of this class via reflection.

example URLs:
jdbc:inetpool:inetora:localhost?database=orcl&useDefaultFailoverListener=true&waitTimeToNextAttempt=100&totalTimeOfAttempts=10
jdbc:inetpool:inetora:localhost?database=orcl&failoverListener=test.MyFirtsFailoverListener

10.3.2 Configuration in Java

For more flexibility it is also possible to manage the registered FailoverListener of a Connection in Java.
A FailoverListener can be only added to a Connection, that implements the interface TAFConnection.
Those Connection can be received from:

  • com.inet.pool.PoolDriver
  • com.inet.ora.PDataSource
  • com.inet.tds.PDataSource
  • com.inet.syb.PDataSource
  • com.inet.drda.PDataSource.

The interface TAFConnection allows you to add and remove a FailoverListener to/from the Connection.
example:

//receive a Connection
Class.forName("com.inet.pool.PoolDriver");
Connection con = DriverManager.getConnection("jdbc:inetpool:inetora:localhost?database=orcl", "scott", "tiger");
 
//add a FailoverListener
FailoverListener fl = new samples.MonitoredFailover.CounteredFailoverListener(99);
((TAFConnection)con).addFailoverListener(fl);
 
//remove a FailoverListener
((TAFConnection)con).removeFailoverListener(fl);
 
//remove all FailoverListener
((TAFConnection)con).removeAllFailoverListener();
 
//get all registered FailoverListener
FailoverListener[] fls = getFailoverListener();

11. Snapshot Transaction Isolation

The SQL Server 2005 supports the new transaction isolation level snapshot. This is not a JDBC standard isolation level but you can use it with this driver. There are the following possible solutions:

  1. Modify the database
    You can modify the database with the following SQL statement
    ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON
  2. initSQL
    If you do not change the level at using of connection then you can set it at creation of the connection with:
    initSQL=SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  3. Executing a statement
    You can also change it with the following Java line:
    conn.createStatement().execute(“SET TRANSACTION ISOLATION LEVEL SNAPSHOT”);
  4. setTransactionIsolation
    Since version 6.08.04 you can also set it via API with:
    conn.setTransactionIsolation(4096);

12. Debugging

If you have problems with the driver then you can enable the logging of the driver. There are different solutions.

Solutions examples Comments
DriverManager.setLogStream(PrintStream) DriverManager.setLogStream( System.out );DriverManager.setLogStream( new FileOutputStream(“c:\\driver.log”) ); This method is deprecated but it work with all JDBC drivers.
DriverManager.setPrintWriter(PrintWriter) DriverManager.setPrintWriter( new PrintWriter( System.out ) ) work not with JDBC 1.22 driver like i-net UNA
Application Server GUI WebLogic: Enable the JDBC logging in the server console on the node servers/myserver/logging/JDBCJBoss: You can set it for every DataSource in the config file with:
….
<attribute name=“LoggingEnabled”>true</attribute>
….
myDataSource.setPrintWriter(PrintWriter) myDataSource.setPrintWriter( new PrintWriter( System.out ) ) enable the logging only for connections from this DataSource
JDBC URL option “logging=true” jdbc:inetdae7:localhost?logging=true

13. Support

Before you ask our support staff for assistance please check out our FAQ. Thank you very much for your collaboration.

The latest support information are available at: http://www.inetsoftware.de/support

Support is available per email (replace at with @):

Licensing, Pricing, Updates, … sales at inetsoftware.de
i-net UNA una at inetsoftware.de
i-net SPRINTA sprinta at inetsoftware.de
i-net OPTA opta at inetsoftware.de
i-net MERLIA merlia at inetsoftware.de
i-net GATE opta at inetsoftware.de
i-net GATE3 merlia at inetsoftware.de

14. Documentation

15. Known problems

  • SQL Server 2005: if String parameter are used for integer parameter the following error will received: [HOST]Operand type clash: ntext is incompatible with int
    possible workarounds are:
    • set integer values instead of Strings
    • use JDBC URL “jdbc:inetdae7a” - take care about no unicode support
    • set JDBC URL parameter “tds9=false” - results in unsupport of new sql server 2005 data types
  • When using named pipes, setting timeout has no effect.
  • When using named pipes, cancel() has no effect.
  • The method getTableName() requires a cursor or the “for browse” statement.
  • Calling stored procedures with output parameters from type binary or char produce output with value 0x00 and spaces cut. (e.g., you have a sp with the parameter “char(10) output” and the output value is “abc ” you receive the value “abc”).
  • The classes Array, Ref and URL are not supported because this are not supported by the SQL Server.
  • The following methods are not supported by this release:
    • PrepareStatement.setArray (int i, Array x)
    • PrepareStatement.setRef (int i, Ref x)
    • PrepareStatement.setURL (int i, URL x)
    • CallableStatement.getObject (int i, java.util.Map map)
    • CallableStatement.getRef (int i)
    • CallableStatement.getArray (int i)
    • CallableStatement.getURL (int i)
    • All method with a named parameter.
    • DatabaseMetaData.getUDTs (String catalog, String schemaPattern, String typeNamePattern, int[] types)
    • DatabaseMetaData.getAttributes (String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern)
    • ResultSet.getObject (int i, java.util.Map map)
    • ResultSet.getRef (int i)
    • ResultSet.getArray (int i)
    • ResultSet.getURL (int i )
    • ResultSet.updateArray (int i, Array x)
    • ResultSet.updateRef (int i, Ref x)
    • All setXXX methods in Blob and Clob.

16. Using SSL connections

If you want to use SSL (Secure Sockets Layer) with i-net JDBC drivers and the MS SQL Server then you need to install:

  • a server-wide certificate on the computer with the MS SQL Server 2000.
Note:: You don't need to install a certificate when using SQL Server 2005. The SQL Server 2005 installs a certificate on setup.
WARNING: After you have installed this certificate, connecting via ODBC only works with SSL. The ODBC driver only accepts trusted certificates (from a known agency). If you create your own certificate (not from a public Certificate Authority such as Verisign or Thawty) then you'll need to add it to every ODBC client.
  • the JSSE (Java Secure Socket Extension). The JSSE is included in the JDK 1.4 and in the J2EE. If you use the JDK 1.2 or the JDK 1.3 without J2EE (or another application server) then you need to download the JSSE and add it to your classpath.

SSL is required for the SQL Server 2005 if you want to use all new features like data types or MARS. The following step by step description is not a public documentation of the Microsoft Certificate technology. There might be some errors in the text or better solutions for working with Microsoft certificates.

16.1. Create a Certificate Request

This is only needed for SQL Server 2000. This is not required for SQL Server 2005.

  • Run the Internet Services Manager that is located in the Administrative Tools folder.
  • Right-click on the Default Web Site node for the server and select “Properties” from the context menu.
  • Click on the “Directory Security” tab.
  • Click on the “Server Certificate” button. A wizard should appear.
  • Click the “Next” button.
  • The “Create a new certificate” radio button should be selected. Click the “Next” button.
  • The “Prepare request now, but send it later” radio button should be selected. Click the “Next” button.
  • Click the “Next” button.
  • Enter your organization name and unit and click the “Next” button.
  • In the Common name field enter the fully qualified IP hostname of your server. e.g., www.inetsoftware.de. Click the “Next” button.
  • Enter your State and City. Click the “Next” button.
  • Accept the default file name of c:\certreq.txt and click the “Next” button.
  • Click the Next button, then click Finish.

This Certificate Request must be sent to a Certification Authority. You can work with a public Certification Authority and pay money for your the certificate or you can use your own certificate server.

16.2. Create a Certificate with the Certificate Server

This is only needed for SQL Server 2000. This is not required for SQL Server 2005.

  • Install the Microsoft Certificate Services. You should not do it on the same computer hosting the MS SQL Server.
  • Run Notepad and open c:\certreq.txt. Hit Ctrl+A to select the entire text. Hit Ctrl+C to copy the text to the clipboard.
  • Run Internet Explorer and enter the URL
    http://YourCertificateServer/certsrv.
  • Select “Request a Certificate” and click “Next”.
  • Select “Advanced Request” and click “Next”.
  • Select the PKCS#10 option and click “Next”.
  • Paste the text that you copied in notepad earlier into the “Saved Request” field, then click the “Next” button.
  • It will tell you the request is pending. Click on the home hyperlink at the top.
  • Run the Certificate Authority application that is located under Adminstrative Tools.
  • Click on Pending Requests. You'll see the request that you just made in there.
  • Right-click on the request and select All Tasks/Issue.
  • Now, go back to the computer that runs the SQL Server/IIS where you made the request from. Select the “Check on pending certifcate” option and click the “Next” button.
  • You should see the “Saved-Request Certificate” in there now. Click the “Next” button.
  • Click the Download CA certificate hyperlink and save the file as c:\certnew.cer. Close Internet Explorer.

16.3. Installing a Certificate

This is only needed for SQL Server 2000. This is not required for SQL Server 2005.

  • Right-click on c:\certnew.cer in Explorer and select Install from the context menu.
  • Click next all the way through the wizard selecting the defaults. The certificate is now installed. If you want to view it you can use MMC.EXE and the Certificates snap-in for the “computer account”.

16.4. Uninstalling a Certificate

This is only possible for SQL Server 2000. This is not valid for SQL Server 2005.

  • Start the MMC.EXE and add the Certificates snap-in for the “computer account”.
  • Select the Certificate in the “Personal” or “Own Certificate” Folder
  • Drop the Certificate

16.5. Forcing Protocol Encryption

After you have installed the certificate, the SSL encryption is only valid for this login. This means your username and password are encrypted with SSL. The transferred data are not encrypted. You can also enable the SSL encryption for the transferred data. You can do this in the Server Network Utilities. You can enable it for every MS SQL Server instance.

16.6 The Driver Option secureLevel

value description
0 Does not use SSL. With the SQL Server 2005 it will work in SQL Server 2000 compatible mode. This means SQL Server 2005 feature are not available.
1 (default) Uses SSL if the MS SQL Server supports it (MS SQL Server 2000 with installed Certificate). The SQL Server 2005 ever.
2 Always uses SSL. This will throw an exception if the MS SQL Server does not support SSL.
3 Always uses SSL and verifies the Certificate. This will throw an exception if the MS SQL Server does not support SSL or if the Certificate was created from a non-trusted Certification Authority

17. Changes

17.1 Changes in Version 5.00 (MERLIA 2.00) (9. Dec 2002)

  • For XA support the extended procedures was replaced with a NT-Service.
  • The SQLException toString() from SQL Server looks like the output in the Query Analyzer. It includes info about msg number, level, state, procedure and line. The getMessage() output was not changed.

17.2 Changes in Version 5.01 (MERLIA 2.01) (5. Feb 2003)

  • The driver supports precision 38 with SQL Server Version 7.0 and 6.5 and the /p option now.
  • ArrayIndexOutOfBoundsException:8190 (value 2*string size) with prepare=false, unicode mode and large Strings (approx. > 4095).
  • A bug with wasNull() and getXXX( x, Calendar) was fixed.
  • A bug with isFirst() in CachedRowSet was fixed.
  • A bug with getGeneratedKeys() with PreparedStatements, prepare=true and SQL Server 2000 was fixed.
  • A bug with DTCDataSource, SQL Server 7 and WebLogic was fixed.
  • A ClassCastException in CachedRowSet.acceptChanges() with a Connection from a lookup was fixed.
  • The error “Disallowed implicit conversion …” with CachedRowSet and null values was fixed.
  • Property initSQL was added.
  • A loop for name resolution of the instance name was added.
  • A reconnect for the DTCProxy was added if a network error occur.
  • In CachedRowSet and JDBCRowSet the methods absolute() and relative fire cursorMoved event now.

17.3 Changes in Version 5.02 (MERLIA 2.02) (8. May 2003)

  • A bug with CachedRowSet and setXXX was fixed. It works without clearParmeters() now.
  • A bug with the method updateFloat and updateDouble in CachedRowSet was fixed. The scale was 0, e.g. for the column type “money”.
  • A dead-lock that could occur in PoolManager.closeAll() was fixed.
  • The error “Cookie from MSDTC is null” was fixed.
  • A bug with setCharacterStream() not working with inetdae7a and prepare=true was fixed.
  • Method PoolManager.closeConnection(Connection) was added.
  • Errors in ResultSet.close() are caught now.
  • A very rare protocol violation with many columns, SQL Server 2000 and the datatyp TEXT or NTEXT was fixed.
  • The bug with setMaxRows() not working in conjunction with forward only server cursors was fixed.
  • Connections with a read timeout are removed from the connection pool.

17.4 Changes in Version 5.03 (MERLIA 2.03) (4. Nov 2004)

  • The return value of supportsStatementPooling() was changed.
  • A bug with a DataSource not from the JNDI and equals() was fixed.
  • The method getParameterMetaData() was implemented.
  • The methods setImpltran() and getImpltran() were added.
  • Timestamp objects can be created (read) faster with JDK 1.4.x now.
  • A NullPointerException after deserialize of a CachedRowSet was fixed.
  • The method PreparedStatement.getMetaData() work also with GROUP BY now.
  • Datetime formats with short month and days like '1999-5-5' are supported now.
  • A bug in CallableStatement with different datatypes for registerOutParameter and setXXX was fixed.
  • The AsciiStream always sends Ascii data (also in unicode mode).
  • The error “Unknown parameter datatype:-12347” with setUnicodeStream and server site PreparedStatement was fixed.
  • The call setObject( x, value, Types.BIT ) accepts numeric Strings now.
  • The detecting of the default codepage of the SQL Server 2000 was changed.
  • The error “Connection refused” with TdsDataSource and PoolManager was reduced with a synchronized.
  • The methods getSystemFunctions(), getStringFunctions(), getDateTimeFunctions() and getNumericFunctions() were refactored.
  • The error “Unknown output datatype:2004” with updateBlob(String,Blob) was fixed.
  • The error “Unknown output datatype:2005” with updateClob(String,Clob) was fixed.
  • Clob and Blob support for updateObject(..) was added.
  • The method setMaxFieldSize() has only an effect on the current Statement and not on the connection now.
  • CallableStatement.getObject() returns a Boolean if the value is registered as Types.BIT now.
  • Support for registerOutParameter( x, Types.BOOLEAN) was added.
  • The synchonized from Connection.close() was removed.

17.5 Changes in Version 5.03.01 (MERLIA 2.03.01)

  • The truncate of SP output parameter without input value on length 255 was fixed.

17.6. Changes in Version 6.00 (19. Jan 2004)

  • Support for SSL was added.

17.7. Changes in Version 6.01 (1. Apr 2004)

  • A Bug, that DatabaseMetaData.getColumns returned a ResultSet with 18 instead of 22 columns was fixed.
  • A NullPointerException with DataSources was fixed. The bug exists since 6.00.
  • The methods setSecureLevel() and getSecureLevel() were added.
  • The regression that connect to MS SQL Server 6.5 fails was fixed.
  • The regression that connect to MS SQL Server with named pipes and without SSL fails was fixed.
  • A ClassCastException with CachedRowSet.setTimestamp( x, Timestamp, Calendar) and CachedRowSet.setTime( x, Time, Calendar) was fixed.
  • The ClassNotFoundExption withh SSL on the console with JDK 1.3 was removed.
  • The method getColumnDisplaySize() returns the correct size for NUMERIC and DECIMAL data types now.
  • Fixes in isAfterLast and isBeforeFirst for empty Resultsets
  • A bug with ResultSet.updateString( x, null ) was fixed. The bug exists since 5.03.01.
  • A “connection reset by peer” error with the latest security patch of MS was fixed. The bug exists since 5.03.01.

17.8. Changes in Version 6.02 (26. May 2004)

  • Adjustments for changes in SSL handshaking of JDK 1.5. The MS SQL server ran to 100% CPU with JDK 1.5.
  • A bug with getConnection() was fixed. Formerly, a NullPointerException with named instances to a MS SQL Server with a SSL certificate and the option secureLevel=0 occurred. The drivers UNA and SPRINTA always use the secureLevel=0.
  • The wrong message “Statement.cancel:Statement is currently not running” on running Statements was removed.
  • The issue that the driver could hang for some time after a protocol violation in executeQuery() was fixed.
  • The issue that the driver could hang on getConnection() with login timeout if you connect to a TCP/IP port of another application was fixed. For example port 21 (FTP), 80 (HTTP) or 1521 (Oracle).
  • The garbage output of DatabaseMetaData.getColumns() was fixed. The bug existed since version 6.01.
  • Unicode support for many DatabaseMetaData was added. Now table names, column names, schema names with non English characters work.
  • The methods setAppname() and getAppname() were added to the DataSources.
  • The methods setFulltran() and getFulltran() were added to the DataSources.
  • Support for the new protocol of SQLServer 2005 was added.
  • Support for readout the new datatypes Time, Date, XML, Varchar(max), NVarchar(max) and Varbinary(max) was added.
  • Support for writeout the new datatypes XML, Varchar(max), NVarchar(max) and Varbinary(max) was added.
  • The property lastUpdateCount was added.
  • The sql state “100029” was changed to “07001”. It depends on the SQLException, whether the number of set parameters is correct or not for PreparedStatements/CallableStatements.
  • A bug with Statement.cancel() and SSL and forceEncryption was fixed.

17.9. Changes in Version 6.03 (6. Oct 2004)

  • A call of cancel() on creating of a server cursor now throws an SQLException. Before a corrupt ResultSet was returned.
  • The methods of DatabaseMetadata with the catalog parameter now returns the correct result instead of an Exception if the catalog parameter is different from the Connection catalog.
  • Some changes on the time behavior after a call of cancel() or if a query timeout occurs.
  • The timing for resolving the instance name was changed.
  • The property WSID was added.
  • The restoring of the connection after an query timeout is faster now if the MS SQL Server does not need to roll back.
  • Support for the data type BIGINT in the method updateObject(i,x) was added.
  • The data type UNIQUEIDENTIFIER now returns in uppercase for compatibility with other MS tools.
  • If a scrollable ResultSet is requested you will now always receive a scrollable ResultSet. If the MS SQL Server does not support it then it is a memory management scroll insensitive ResultSet.
  • An ArrayIndexOutOfBoundsException with the setXXX() methods of the RowSets was fixed if the parameter was not set in numerical order.
  • An SQLWarning was added to the to XAConnection from XDataSource if a connection was not closed from the user's side.

17.10. Changes in Version 6.04 (9. Feb 2005)

  • Connection failover was expanded to set more than one failoverhost.
  • Added setLogging(String) and setProperty(String) to class TdsDataSource.
  • The following bug has been fixed: A CallableStatement returned wrong out-parameter values, if the SP was without return-parameter and called himself an other SP with out-parameter.

17.11. Changes in Version 6.05 (13. Sep 2005)

  • Only SQL Server 2005: A bug throwing a NullPointerException in ResultSet.getXXX(String) after calling DatabaseMetaData.getColumns() has been fixed.
  • The bug in DataSource overwriting the original host/port information in failover case has been fixed.
  • The com.inet.pool.TimedFailoverListener / DefaultFailoverListener calculated the end of reconnect process too early. This is fixed.
  • A java.lang.ArrayIndexOutOfBoundsException with DTCDataSource on XA transaction starting with a full expanded Xid was fixed. The DTCProxy was also changed to version 1.04.
  • CachedRowSet no more closes the Connection that was set with acceptChanges(Connection) or execute(Connection)
  • Only i-net MERLIA: Connecting to SQL Server 2005 Beta 2 is possible now.
  • Fix for ResultSet.updateRow() with SQL Server 2005 Beta 2. Previously the error message “Invalid object name” had been issued by mistake.
  • The driver sent a wrong time for following timestamp: Timestamp(X,X,X,23,59,59,999000000). This is fixed.
  • Fix for SQL Server 2005 Beta 2: The information whether the ResultSet cursor received data had been erroneously evaluated before the fix.
  • Support for setting an IPv6 address as a host name. Sample JDBC URL: jdbc:inetdae7:[2002:d201:a413::d201:a413]:1433
  • Bug fixed for requested scrollable ResultSet from stored procedures that also returned a parameter.
  • Method PoolManager.dispose() was added.
  • The exception “Unknown TDS packet type: 18” will not be thrown anymore.
  • Performance increasing: executing a prepared statement runs faster at the first execute call.
  • “Create View …” and “Create Procedure …” executed as PreparedStatement now works with SQL Server 2005.
  • The ResultSet created by Statement.execute() now is from the type defined with Connection.createStatement(int,int)
  • Log output extended with information about Connection ID
  • Performance improvement in ResultSet class.
  • A thread bug was fixed in PoolManager.getFreeConnectionCount(Object) and PoolManager.getUsedConnectionCount(Object).
  • Reading out type money and type smallmoney from sql_variant column was fixed.

17.12. Changes in Version 6.06 (18. Jan 2006)

  • New driver property “dstsave”
  • Receiving a BigDecimal from a BIT column is implemented now. (Threw a NumberFormatException before)
  • Reading NULL values from MONEY columns with getDouble() has been fixed to not throw an Exception anymore.
  • Reading values from MONEY columns with getDouble() has been fixed to return correct values.
  • Since version 6.05 CachedRowSet.populate(ResultSet) didn't worked. This is fixed now.
  • There was a bug in CachedRowSet.createCopy(). The ResultSet hold internally was shared between the copies. This produced Exceptions if one CachedRowSet was closed.
  • An excrescent stacktrace log output was removed. (SocketException)
  • TdsDriver.getPropertyInfo() was extended, more properties. Values and choices were added.
  • Since version 6.05: If a statement was already prepared, executing it as batch failed.
  • SQL Server 2005: Fixed a protocol problem with setting a Boolean value and Types.Bit to PreparedStatement.setObject().
  • Behavior change for input parameter that are streams. Those parameter have to reset for every execution.
  • PreparedStatement.setObject(int, Object, int, int) and PreparedStatement.setObject(int, Object, int) with Types.CLOB parameter accept byte[], char[] String and InputStream values in addition.
  • since 6.04.12, Statement.setMaxRows(int) had no effect if sql query was executed with PreparedSatement.execute() and Driver subprotocol inetdae7 and inetdae7a.
  • since 6.04.12, prepare end execute statements didn't worked with SQL Server 7.
  • Multiple protocol changes to adapt driver to final SQL Sever 2005. Fix the bug with transaction
  • Adapted meta data requests to changes made in final SQL Server 2005.
  • In very seldom cases ResultSet.getString() was returning wrong values in a multiprocessor environment.

17.13. Changes in Version 6.07 (2. June 2006)

  • When the PreparedStatement contained a character sequence in SQL like '–', the parameters following were ignored.
  • Problems with version UNA and SPRINTA with SQL Server 2005 and connecting via instance name was solved. (workaround was to set “tds9=false” in JDBC URL)
  • The Exception regarding conversion of decimal to decimal on sending BigDecimal values was solved.
  • If a statement requested a massive amount of columns, sometimes the Exception “Unknown data type:”… would occur. This is fixed now.
  • Since Version 6.05.10 the query timeout and login timeout in TdsDataSource increased after every getConnection() call.
  • Since 6.04.12, ResultSet.getBytes() returned null although the data was empty and not null.
  • The method PoolManager.getFreeConnectionCount() now again returns the value for all DataSources. Since version 6.05 it only returned the value from the first DataSource.
  • Only SQL Server 2005: ResultSetMetaData.getColumnClassName() didn't regard the max data types of SQL Server 2005. An “unknown type” Exception was thrown before.
  • Only SQL Server 2005: PreparedStatement.setString with empty String value sends null instead of empty String.
  • Only SQL Server 2005: If a TEXT column was selected and the table in FROM clause was fully qualified, the Driver threw an error “column type XX is unknown”.
  • An “ArrayIndexOutOfBoundsException: 2” with PreparedStatement.executeXXX() was fixed. The exception could occur with an additional PreparedStatement pool.
  • Since 6.04.15, in very seldom cases, the data in ResultSet could be corrupt, if multiple ResultSets were executed.
  • NPE fix in Statement close() call.
  • IllegalAccessException fixed when trying to access DatabaseMetaData method via reflection.
  • Calling PreparedStatement.executeQuery() after PreparedStatement.execute() could cause an infinite loop.
  • A protocol violation with the data types VARBINARY(max), VARCHAR(max) and NVARCHAR(max) of the SQL Server 2005 was fixed.
  • If a SP call was prepared then the first call did not return a output parameter. A SP call will be prepared if it has a static parameter or it uses a parameter name.
  • The method sequence Statement.cancel() and Connection.isClosed() would block the connection for too long.
  • A ClassCastException was fixed in method PoolManager.closeAll().
  • Only SQL Server 2005: Output parameters could be returned in wrong order or cause an ArrayIndexOutOfBoundsException when using named parameters.
  • In the SQL Server 6.5 mode the driver now returns null again for null values and not empty strings.

17.14. Changes in Version 6.08 (26. Sep 2006)

  • A NPE with the IBM VM and SSL was compensate through disable the SSL with this Java VM.
  • The method PoolManager.getConnection() is follow more the FIFO concept.
  • The method equals from TdsDataSource and it subclasses does not work after creating a connection. This was critical for the size of a pool.
  • The protocol of the transaction API was changed for the SQL Server 2005 for support MARS in future versions.
  • Support for MARS was added.
  • Data larger as 200 MB are send as bulk. Now you can save date until a size of Interger.MAX_VALUE-24.
  • Only SQL Server 2005: Output parameters returned in wrong order together with a return parameter.
  • With MARS enabled the fetch size hint is ignored.
  • Fast-Forward Cursor is used for ResultSet with FORWARD_ONLY, READ_ONLY and a fetchsize larger 0. Before it was a Forward-Only Cursor.
  • AutoClose was added.
  • In the DTCProxy 2.01 a XA transaction timeout of 10 minutes was added.
  • A bug with the PoolManager inactive timeout was fixed.

17.15. Changes in Version 7.00 (14. Mar 2007)

  • The exception “sp_cursorfetch: The cursor identifier value provided (abcdefXX) is not valid.” on ResultSets with Statement.setFetchSize() was fixed.
  • Every Throwable that occurs in the method Connection.isClosed() now produces a return value of true.
  • The warnings of getTransactionIsolation() are ignored now.
  • The bulk transfer limit was reduced from 200 MB to 100 MB.
  • A bug with commit() and rollback() without a transaction and SQL Server 2005 was fixed.
  • The default value of dstSave was changed to true.
  • Date and Timestamp objects are now checked for a valid range. If the is value outside of a valid SQL Server DATETIME then an exception is thrown.
  • Transaction isolation snapshot was added.
  • The JDBC 4 feature of negative length for streaming values was added.
  • A connection with XA transaction is using the isolation level like before starting the transaction. Older versions of the driver has used the default SERIALIZABLE.
  • ResultSet.getAsciiStream() caused a NullPointerException if the value of the column was NULL.
  • Some failover properties were added.
  • A regression with setMaxIdleTime() was fixed. After the timeout not all idle connections were closed.
  • PreparedStatement.setBigDecimal() has converted the value to BIGINT if there are no decimal points. This is a regression since 6.07.
  • The property typeMapping was added.
  • JDBC4 interface was added for i-net MERLIA.

17.16. Changes in Version 7.01 (27. Jun 2007)

  • A very rare protocol violation (Error in the TDS datastream) was fixed. The error occurred only with SQL Server 2005 and multiple ResultSets.
  • A bug with UNA and SQL Server 2005 SP2 that ResultSets with cursors did not return a row was fixed.
  • A protocol violation (Error in the TDS datastream) with PreparedStatements or stored procedure calls was fixed.
  • The cursor scrolling type TYPE_FORWARD_ONLY-1 as an alias for a FAST FORWARD cursor was added. Now FORWARD ONLY is again the default ResultSet type.
  • A protocol violation with the max data types (like VARCHAR(max)) and empty strings was fixed.
  • Some protocol violations with the data type XML were fixed.
  • The methods insertRow() and updateRow() with the SQL Server 2005 SP2 could throw the exception “Msg 7202, Level 11, State 2, Line 1, Sqlstate 01000
    [xyz2005]Could not find server 'xyz2005' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.”
  • A regression since 7.0 with small BigDecimal values and a scale larger than 38 was fixed.
  • Integrated Windows Authentication for TCP/IP connections was added.
  • An ArrayIndexOutOfBoundsException, which occurred during server-side creation of PreparedStatements, is now caught and the connection is closed. The connection can no longer be used because of a bug in SQL Server.
  • The methods getFunctions and getFunctionColumns are now implemented.
  • The typed SQLExceptions of the JDBC 4 driver did not have the vendor code. The vendor code was always 0.

17.17. Changes in Version 7.02 (13. Sep 2007)

  • Property statusRequest added.
  • An encoding bug with the XML data type of the SQL Server 2005 was fixed.
  • The following properties will now be respected by TdsDataSource.equals(): [appname, dstsave, fulltran, impltran, JAVA_OBJECT, language, lastUpdateCount, mars, prepare, prepareLiveTime, secureLevel, statusRequest, useInsteadOfTrigger, WSID].

17.18. Changes in Version 7.03 (6. Mar 2008)

  • The error “The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly.” with MERLIA, Java 6 and the JDBC subprotocol inetdae7a was fixed.
  • The follow SQL “SELECT {ts '2007-10-19 10:20:30.000'}” returns a DATETIME and not a VARCHAR for SQL Server 7 or higher.
  • A regression in CachedRowSource with data type NTEXT has produce an error like “The data types ntext and nvarchar are incompatible in the is operator.”.
  • Instances of PDataSource could not have been serialized.
  • hostProcess is supported as client property now. It returns the id identifying the connection on the host. This property can also be seen in the system table sysprocesses. It is also used for JDBC logging.
  • With SPRINTA the methods insertRow() and updateRow() with the SQL Server 2005 SP2 could throw the exception “Msg 7202, Level 11, State 2, Line 1, Sqlstate 01000
    [xyz2005]Could not find server 'xyz2005' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.”
  • A NullPointerException with deserialize of the CachedRowSet was fixed.
  • The methods deleteRow() with the SQL Server 2005 SP2 could throw the exception “Msg 7202, Level 11, State 2, Line 1, Sqlstate 01000
    [xyz2005]Could not find server 'xyz2005' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.”
  • Fix a bug with Windows NT authentication and SQL Server 2005.
  • Connection property resultSetType added.

17.19. Changes in Version 7.04 (8. Oct 2008)

  • A fix for the message “Fatal error on prepare. Connection will be closed.” which occurred with mars=true and SQL Server 2005 was fixed.
  • The error “Fatal error on prepare. Connection will be closed.” which occurred with a very fast cancel() was fixed.
  • The instance name is now resolved multiple times on a failover. Before, the TCP/IP port of the first instance was used.
  • The error message “Could not find prepared statement with handle 0.” was fixed.
  • The return value of functions was always NULL with SQL Server 2005. This was a regression in version 6.07.
  • The method DataSource.setUrl was added.
  • The call ResultSet.findColumn( “” ) did not find empty columns.
  • A BigDecimal value with a scale smaller than 38 and a precision larger than 38 was not correctly prepared for SQL Server 2005. It resulted in the error “Error converting data type decimal to decimal.”
  • Some error handling was added for connecting to prevent problems in stress conditions.
  • The methods updateRow(), deleteRow() and insertRow() had problems with SQL Server 2005 if the SELECT statement also included the table owner.
  • A bug with large server certificates (>= 8KBit) was fixed. A connection was only possible with secureLevel=0.
  • There was a NullPointerException with SQL Server 6.5 with ResultSet.getInt(x) on a VARCHAR column that included a NULL value.

17.20. Changes in Version 7.05 (25. Mar 2009)

  • Connection property resultSetType was not used for PreparedStatement and CallableStatement.
  • There could be a very rare exception “You are tried to connect to SQL Server 6.5 with the sql7 mode.” with SQL Server 2005 when calling getConnection().
  • Classes DTCDataSource4 and PDataSource4 were added for 100% JDBC 4.0 compliance.

17.21. Changes in Version 7.06 (2. Oct 2009)

  • Empty strings in CacheRowSet changed to null values after serialization.
  • A very rare “ArrayIndexOutOfBoundsException: Array index out of range: 0” on executeQuery() was fixed. It could occur with scrollable ResultSets if the table had no primary key.
  • The inactive timeout of the PoolManager is now reset with every method call.
  • The property fastbatch was added.
  • A possible hanging in socket.skip() after a timeout or cancel was fixed.
  • An endless loop in ResultSet.findColumn( String ) with empty column name was fixed.
  • Some NullPointerExceptions with setClientInfo and pooled Connection were fixed.
  • The error “Unknown parameter datatype:-16 - null” with PreparedStatement.setNull(x,Types.LONGNVARCHAR) was fixed.
  • Fixed the PreparedStatement.setNxxx methods for the protocol inetdae7a and prepare=false.

17.22. Changes in Version 7.07 (14. Apr 2010)

  • Fixed some bugs in ParameterMetaData.
  • Fixed a possible unnecessary cancel if an exception occurred on prepare or execute.
  • A wrong ResultSet type was returned if a fetch size was set on a forward-only ResultSet.
  • Fixed some bugs in setClientInfo(String,String) and optimized performance.
  • Removed the finalize() method from the Connection object.
  • Fix a hanging of the finalizer thread in SSLSocketImpl.close() if an SSL connection was lost and the default query timeout was unlimited.
  • Statement.RETURN_GENERATED_KEYS disabled server-side prepare to return the right values with triggers.
  • Fixed the very rare exception “java.io.FileNotFoundException: //./pipe/sql/query (All pipe instances are busy)” on getConnection().

17.23. Changes in Version 7.08 (15. Oct 2010)

  • Statement.close() could produce a NPE on a running executeQuery() of the same statement if you set max rows or max field size. If the NPE occurred, max row size and/or max field size were not restored for the connection. The values affected all following calls of executeQuery() for this connection.
  • A very rare deadlock could occur if a prepared query needed 60 seconds and a second thread was running the same prepared query in this time.
  • An ArrayIndexOutOfBoundsException in executeBatch() with fastbatch=true was fixed. The exception occurred if you ran a transact SQL in the batch that did not produce an update count.
  • With SQL Server 2005 or higher, the first call of method rollback() did not work correctly if the transaction was already rolled back from the SQL Server after a deadlock detection. The result was that setAutoCommit(true) did not end the transaction. A second call to rollback() or commit() prevented problems.
  • A memory leak in the DTCProxy was fixed.
  • Long-running XA transactions were rolled back after 10 minutes under very rare conditions.

17.24. Changes in Version 8.00 (23. Mar 2011)

  • Added support for the new data types DATE, TIME, DATETIME2 and DATETIMEOFFSET of the SQL Server 2008. Now the driver returns the equivalent Java objects.
  • Improve support for java.util.Date together with PreparedStatements and updatable ResultSets.
  • Improve the speed for getGeneratedKeys together with PreparedStaments. Now it use server side prepare and no extra roundtrip to the database.

17.25. Changes in Version 8.01 (26. Oct 2011)

  • Added property packetSize and changed the default from server default to 8000.
  • Added support for setObject( x, String, Types.NVARCHAR ) with inetdae7a mode.
  • Added JDBC URL property sqlxml.
  • The method getClob() returned wrong data for data type VARCHAR(MAX).
  • Changed the default value of impltran for PDataSource to false.

17.26. Changes in Version 8.02 (30. Mar 2012)

  • A infinite loop was fixed with 32-bit characters in a PreparedStatement string parameter in ASCII mode (sub protocol inetdae6 and inetdae7a).
  • A regression with ParameterMetaData note setting parameters was fixed.
  • An ArrayIndexOutOfBoundsException on ResulSet.getObject(x) without a starting next() for a scrolling ResultSet was fixed.
  • A failing windows authentication was fixed for the case that user name, domain name and host name together were very long.

17.27. Changes in Version 8.03 (17. Apr 2013)

  • A TYPE_FORWARD_ONLY, CONCUR_UPDATABLE ResultSet was jumped to the first row position if you update a row and access a value on this row.

17.28. Changes in Version 8.04 (2. Oct 2013)

  • Does not use the package sun.misc.* on Java 6 and newer.

17.29. Changes in Version 8.05 (9. Apr 2014)

  • A DatagramSocket leak with JDBC connections with instance name was fixed. In very rare conditions the follow exception can occur: SocketException: maximum number of DatagramSockets reached
  • Add support for setObject with Types.BLOB and a value of byte[] or InputStream was added.
  • Fix an ArrayIndexOutOfBoundsException and/or wrong values with getTimestamp(x,Calendar) on DATETIME2 column with a precision smaller 7.

17.30. Changes in Version 8.06 (15. Apr 2015)

  • Added support for VNN (virtual network names) together with instance names.
  • Fixed the exception with setNull( x, Types.OTHER ). It is matched to type sql_variant and can be slow.
  • No longer write the password to the driver log.

17.31. Changes in Version 8.07 (19. Apr 2016)

  • Add the interface NtlmHandshake to support an external NTLM login source.
  • ResultSetMetaData.getColumnType() has return the wrong type for data type DATETIMEOFFSET. Now Types.TIMESTAMP is return.
  • Fix the date offset of getTimestamp(x,Calendar) for data type “datetime2” with Java7 or higher.
  • Improve reconnect of XA control connection.

17.32. Changes in Version 8.08 (22. Mar 2017)

  • Added property applicationIntent.
  • com.inet.tds.XDataSource was removed. Use the DTCDataSource if you need XA support.

© 1998 - 2017 by i-net software

 

© Copyright 1996 - 2017, i-net software; All Rights Reserved.