Which JDBC subprotocol should I use for MS SQL Server?

JDBC
1

There are three subprotocols inetdae6, inetdae7 and inetdea7a. Which should I use?

2

inetdae6

This subprotocol implements the communication protocol for the SQL Server 6.5. If you want to be compatible with SQL Server 6.5 then you need to use this protocol. SQL Server 2000 is very slow with this protocol (approx 10-100 times slower than inetdae7). This is an ASCII protocol (1 byte per character) and it supports only features of SQL Server 6.5 like

  • column and table names with only 30 characters
  • no empty space strings

inetdae7

This is a unicode communication protocol. Every character has 2 bytes. It is not compatible with SQL Server 6.5. You need SQL Server 7.0 or higher. This subprotocol has a very good support for the nXXX data types. Because JDBC API does not make a difference between ASCII and UNICODE data types and the SQL Server cannot convert NTEXT to TEXT you cannot use the TEXT data type with this subprotocol.

If you have an ASCII column (varchar or char) then some SQL Server versions (without service pack) will have performance problems because they cannot use indexes.

If you save data to an ASCII column then SQL Server will convert the unicode from the driver with the codepage of the SQL Server to ASCII.

If you read ASCII data then the driver convert the ASCII data to UNICODE. The driver uses the codepage that you set with the option charset for converting. If you do not set the charset then the driver will detect the codepage from the SQL Server.

If you want to save include values as UNICODE then the include values will need to be marked with the N prefix.

inetdae7a

The “a” stand for ASCII. This protocol equals inetdae7 except string parameter of PreparedStatements. These parameters are sent as ASCII. But all SQL expressions are sent as UNICODE. This means if you have parameters included in your SQL expression then these will also be sent as UNICODE. If you want to save it as UNICODE then the include values will need to be marked with the N prefix.

If you use ASCII data types this is the recommended subprotocol. This protocol might be a little slower than inetdea7 if the SQL Server has no index problems. This is the case because the character converting of Java is slower than the converting of the SQL Server (Java byte code versus native code).