Manual for the MS SQL Server JDBC type 4 driver MERLIA

Version: 8.13
Last Modified: 6. Apr 2023

The zip file of the driver includes a jar file merlia.jar. You need to add this jar file to your classpath or extract the jar file in the directory of the application.

The driver support the SQL Server versions 6.5 - 2019 with all Service Packs (32 bit / 64 bit).

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:

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.

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.

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. Since Java version 1.8.0_181 you must also set the property jdk.http.ntlm.transparentAuth. See the FAQ for details.

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

The name of the pooled driver is: com.inet.pool.PoolDriver

DataSources:

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

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

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.

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

Eight examples are included in the download file.

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)

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

How to create a connection with a datasource.

How to use the PooledConnection of the driver.

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

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

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

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

A sample with EJB and a XADataSource.

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.

The driver implements all escape clauses as follows:

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

{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();

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

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

{encrypt N'password'}

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' )”

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.

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

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.

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.

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)

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

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.

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

Concurrency type is one of the following concurrency control options.

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.

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.

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

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

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

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

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

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

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

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

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.

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.

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.

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

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

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.

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

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

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

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

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

• Support for SSL was added.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Prevent a deadlock through a SSL close event from SQL Server.
• Fixed a connection problem with SQL Server 7. The Server version was detected wrong.

• Added JDBC 4.1 methods.
• Fixed a java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter with Java 11 and Windows Authentication.

• Handle limit access to master.dbo.sysprocesses in Azure databases.
• Use fewer temp files for InputStream parameters without length info and delete this files faster.

• Support LocalDateTime values for setObject(i,x).
• Add reconnect to the pooled driver also to the DatabaseMetaData API.
• Support for JAVA_OBJECT has been removed due to possible security issues.

• Small performance optimizing.

© 1998 - 2023 by i-net software