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

Version: 7.06
Last Modified: 2. Oct 2009

Table of Content

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. You need a browser compatible with JDK 1.2.x or higher. Most browsers support JDK 1.1.x, only. A solution is using the Java Plug-In. In this case you need to use the OBJECT tag (IE) or the EMBED tag (NC).

Please note: If you want to employ:
- connection pooling:
You need to download the JDBC 2.0 Optional Package interface (javax.sql.*)
from Sun's website at:
http://java.sun.com/products/jdbc/download.html#binary

- the DataSource interface:
You need to download the JNDI 1.2.1 class libraries (javax.naming.*)
from Sun's website at:
http://java.sun.com/products/jndi/#download
if you do not use the JDK 1.3.x. (J2SE version 1.3).

2. Getting Started

2.1 SQL Server, Java and JDBC Versions

All drivers support the following SQL Server Versions:

• Microsoft SQL Server 2008
• Microsoft SQL Server 2005
• Microsoft SQL Server 2000
• Microsoft SQL Server 7.0
• Microsoft SQL Server 6.5

with all Service Packs (32 bit / 64 bit)

2.2 Implemented Features

A more complete list can be found at at:
http://www.inetsoftware.de/products/jdbc/mssql/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

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

2.6 JDBC URL Subprotocols

2.7 JDBC URL Syntax of the driver

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

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(tm). i-net PLEXA(tm) is an example of a pool manager. i-net PLEXAtm) comes with i-net OPTA(tm).

WithJDBCRowSet.java

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

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

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, or 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.1 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.

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:

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

9. Locking and Concurrency Control

Concurrency type is one of the following concurrency control options.

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

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

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

14. Documentation

You can find more documentation at:
http://www.inetsoftware.de/products/jdbc/mssql/documentation.htm

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

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 occured. 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().
• Behaviour 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)

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)

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.

18. Copyright

© 1998 - 2009 by i-net software

You are here: i-net softwareProductsJDBC DriversMS SQL ServerDocumentation

• i-net Crystal-Clear - Reporting with Java.
• /
• Use i-net JDBC Drivers. Get connected.
• /
• i-net HelpDesk - die Service-Management Lösung für den Mittelstand.
• /
• FAQ & Knowledge-Base