Micorosoft SQL Server JDBC 3.0 Released!!!

Environment

OS: Vista HP SP2 x64

SQLServer: 2008 R2 x64

Java: 6u20 x64

Driver: sqljdbc4-3.0.jar

Running a Java app for retrieving some metadata, the driver still returns java.sql.Types.NVARCHAR for 'date', 'time' and 'datetime2' database types, rather than java.sql.Types.DATE/TIME/TIMESTAMP respectively, which is what I expected.

What is the reason that the Java app (or the JDBC driver itself?) is treated as a "down-level client"?

Do you have a concise repro you can share with us. The 2.0 driver returned nvarchar but the 3.0 should not. Are you sure that you are picking up the right jar? I would print the version of the jar in my application via getDriverVersion()

Yes, I am.

Output (excerpt):

----------------------------------------

General information

----------------------------------------

Database product name   : Microsoft SQL Server

Database product version: 10.50.1600

Driver name             : Microsoft SQL Server JDBC Driver 3.0

Driver version          : 3.0.1301.101

----------------------------------------

Supported data types

----------------------------------------

...

----------------------------------------

TYPE_NAME: Database type name = date

DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

PRECISION: Maximum precision  = 10

----------------------------------------

TYPE_NAME: Database type name = time

DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

PRECISION: Maximum precision  = 16

----------------------------------------

TYPE_NAME: Database type name = datetime2

DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

PRECISION: Maximum precision  = 27

----------------------------------------

TYPE_NAME: Database type name = datetimeoffset

DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

PRECISION: Maximum precision  = 34

----------------------------------------

...

----------------------------------------

TYPE_NAME: Database type name = datetime

DATA_TYPE: SQL/JDBC data type = 93 (corresponds to java.sql.Types.TIMESTAMP)

PRECISION: Maximum precision  = 23

----------------------------------------

TYPE_NAME: Database type name = smalldatetime

DATA_TYPE: SQL/JDBC data type = 93 (corresponds to java.sql.Types.TIMESTAMP)

PRECISION: Maximum precision  = 16

----------------------------------------

Source code:

------------

package test.database;

import static java.sql.Types.*;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

public final class SQLServerInfo

{

 private static final Map<Integer,String>

   TYPES = new HashMap<Integer,String>(37);

 private static final String

   CONNECTION_URL = "jdbc:sqlserver://localhost:1433;databaseName=*;user=*;password=*",  // * to be set!

   NL = System.getProperty("line.separator"),

   SEPARATOR = "------------------------------------------------------------" +

               "------------------------------------------------------------";

 static {

   TYPES.put(ARRAY,         "ARRAY");

   TYPES.put(BIGINT,        "BIGINT");

   TYPES.put(BINARY,        "BINARY");

   TYPES.put(BIT,           "BIT");

   TYPES.put(BLOB,          "BLOB");

   TYPES.put(BOOLEAN,       "BOOLEAN");

   TYPES.put(CHAR,          "CHAR");

   TYPES.put(CLOB,          "CLOB");

   TYPES.put(DATALINK,      "DATALINK");

   TYPES.put(DATE,          "DATE");

   TYPES.put(DECIMAL,       "DECIMAL");

   TYPES.put(DISTINCT,      "DISTINCT");

   TYPES.put(DOUBLE,        "DOUBLE");

   TYPES.put(FLOAT,         "FLOAT");

   TYPES.put(INTEGER,       "INTEGER");

   TYPES.put(JAVA_OBJECT,   "JAVA_OBJECT");

   TYPES.put(LONGNVARCHAR,  "LONGNVARCHAR");

   TYPES.put(LONGVARBINARY, "LONGVARBINARY");

   TYPES.put(LONGVARCHAR,   "LONGVARCHAR");

   TYPES.put(NCHAR,         "NCHAR");

   TYPES.put(NCLOB,         "NCLOB");

   TYPES.put(NULL,          "NULL");

   TYPES.put(NUMERIC,       "NUMERIC");

   TYPES.put(NVARCHAR,      "NVARCHAR");

   TYPES.put(OTHER,         "OTHER");

   TYPES.put(REAL,          "REAL");

   TYPES.put(REF,           "REF");

   TYPES.put(ROWID,         "ROWID");

   TYPES.put(SMALLINT,      "SMALLINT");

   TYPES.put(SQLXML,        "SQLXML");

   TYPES.put(STRUCT,        "STRUCT");

   TYPES.put(TIME,          "TIME");

   TYPES.put(TIMESTAMP,     "TIMESTAMP");

   TYPES.put(TINYINT,       "TINYINT");

   TYPES.put(VARBINARY,     "VARBINARY");

   TYPES.put(VARCHAR,       "VARCHAR");

 }

 public static void main(final String[] args)

 {

   Connection connection;

   DatabaseMetaData md;

   try {

     connection = DriverManager.getConnection(CONNECTION_URL);

     md = connection.getMetaData();

     /* General information */

     System.out.println(SEPARATOR);

     System.out.println("General information");

     System.out.println(SEPARATOR);

     System.out.println(

       "Database product name   : " + md.getDatabaseProductName() + NL +

       "Database product version: " + md.getDatabaseProductVersion() + NL +

       "Driver name             : " + md.getDriverName() + NL +

       "Driver version          : " + md.getDriverVersion()

       );

     /* Supported types */

     System.out.println(SEPARATOR);

     System.out.println("Supported data types");

     System.out.println(SEPARATOR);

     ResultSet rs = md.getTypeInfo();

     while (rs.next())

     {

       int type = rs.getInt("DATA_TYPE");

       System.out.println("TYPE_NAME: Database type name = " + rs.getString("TYPE_NAME"));

       System.out.print("DATA_TYPE: SQL/JDBC data type = " + type);

       System.out.println(" (corresponds to java.sql.Types." + TYPES.get(type) + ")");

       System.out.println("PRECISION: Maximum precision  = " + rs.getLong("PRECISION"));

       System.out.println(SEPARATOR);

     }

   }

   catch (SQLException e) {

     System.err.println(e.getMessage());

     System.exit(-1);

   }

   System.exit(0);

 }

}

Thank you for the detailed repro. It looks like this is a shortcoming in the current release. You can use

sp_datatype_info_100 directly and use this information. In the meantime, we will consider fixing this in a future release. If you need this immediately fixed, you can contact Microsoft customer services in your country.

m using windows 7 and sql server 2005 express edition. m new to java.Try to connect my java proram(written in notepad) .plz tel me how to connect my java to sql server 2005  edition using this jdbc driver.plz help as i m in middle of my engg. project.thanks

We'd like to be able to associate a user with a connection during the time we have it out of the pool.  Oracle lets you run "DBMS_SESSION.SET_IDENTIFIER" with the user name.  Is this possible with the 3.0 MS JDBC driver?  I tried playing around with the Connection.setClientInfo() and .getClientInfo() without luck.

I wasn't logged in when I left the last comment, so if you replied to this one, I'll be notified.

another error found for mapping of varchar(max), nvarchar(max), varbinary(max) incorrectly to java.sql.Types "VARCHAR", "VARCHAR"(JRE5), "VARBINARY" respectively.

table created through:

CREATE TABLE ALL_DATA_TYPES_COLUMNS

(

VARBINARY_MAX_COL VARBINARY(MAX),

CHAR_COL CHAR(30),

VARCHAR_MAX_COL VARCHAR(MAX),

DATETIME2_COL DATETIME2,

NCHAR_COL NCHAR(30),

NVARCHAR_MAX_COL NVARCHAR(MAX),

NUMERIC_P_S_COL NUMERIC(10,3),

NUMERIC_P_COL NUMERIC(10),

FLOAT_COL FLOAT(53),

NVARCHAR_COL NVARCHAR(30),

VARCHAR_COL VARCHAR(30)

);

java source:

package test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.util.Hashtable;

public class DataTypeTestMSSQL {

/**

* @param args

*/

public static void main(String[] args) throws Exception {

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection loConnection = null;

ResultSet loRS = null;

PreparedStatement loStatement = null;

               // putting all java.sql.Types constants into hashtable

Hashtable <Integer, String> loSqlType = new Hashtable();

loSqlType.put(java.sql.Types.ARRAY, "ARRAY");

loSqlType.put(java.sql.Types.BIGINT, "BIGINT");

loSqlType.put(java.sql.Types.BINARY, "BINARY");

loSqlType.put(java.sql.Types.BIT, "BIT");

loSqlType.put(java.sql.Types.BLOB, "BLOB");

loSqlType.put(java.sql.Types.BOOLEAN, "BOOLEAN");

loSqlType.put(java.sql.Types.CHAR, "CHAR");

loSqlType.put(java.sql.Types.CLOB, "CLOB");

loSqlType.put(java.sql.Types.DATALINK, "DATALINK");

loSqlType.put(java.sql.Types.DATE, "DATE");

loSqlType.put(java.sql.Types.DECIMAL, "DECIMAL");

loSqlType.put(java.sql.Types.DISTINCT, "DISTINCT");

loSqlType.put(java.sql.Types.DOUBLE, "DOUBLE");

loSqlType.put(java.sql.Types.FLOAT, "FLOAT");

loSqlType.put(java.sql.Types.INTEGER, "INTEGER");

loSqlType.put(java.sql.Types.JAVA_OBJECT, "JAVA_OBJECT");

loSqlType.put(java.sql.Types.LONGVARBINARY, "LONGVARBINARY");

loSqlType.put(java.sql.Types.LONGVARCHAR, "LONGVARCHAR");

loSqlType.put(java.sql.Types.NULL, "NULL");

loSqlType.put(java.sql.Types.NUMERIC, "NUMERIC");

loSqlType.put(java.sql.Types.OTHER, "OTHER");

loSqlType.put(java.sql.Types.REAL, "REAL");

loSqlType.put(java.sql.Types.REF, "REF");

loSqlType.put(java.sql.Types.SMALLINT, "SMALLINT");

loSqlType.put(java.sql.Types.STRUCT, "STRUCT");

loSqlType.put(java.sql.Types.TIME, "TIME");

loSqlType.put(java.sql.Types.TIMESTAMP, "TIMESTAMP");

loSqlType.put(java.sql.Types.TINYINT, "TINYINT");

loSqlType.put(java.sql.Types.VARBINARY, "VARBINARY");

loSqlType.put(java.sql.Types.VARCHAR, "VARCHAR");

try

{

loConnection = DriverManager.getConnection(

"jdbc:sqlserver://*:*;databaseName=*", "*", "*"); // * to be set

System.out.println("COLUMN_NAME," +

"COLUMN_SIZE,DECIMIAL_DIGITS,TYPE_NAME,DATA_TYPE,java.sql.Types");

loStatement = loConnection.prepareStatement("select * from ALL_DATA_TYPES_COLUMNS");

loRS = loStatement.executeQuery();

ResultSetMetaData loRSMD = loRS.getMetaData();

for (int i = 1; i <= loRSMD.getColumnCount(); i++)

{

System.out.println(loRSMD.getColumnName(i) + ","

+ loRSMD.getColumnDisplaySize(i) + ","

+ loRSMD.getPrecision(i) + ","

+ loRSMD.getScale(i) + ","

+ loRSMD.getColumnTypeName(i) + ","

+ loRSMD.getColumnType(i) + ","

+ loSqlType.get(loRSMD.getColumnType(i))

);

}

loStatement.close();

}

catch (Exception ex)

{

throw ex;

}

finally

{

try{

if (loConnection != null) loConnection.close();

}

catch (Exception ex){throw ex;}

}

}

the output:

COLUMN_NAME,COLUMN_SIZE,DECIMIAL_DIGITS,TYPE_NAME,DATA_TYPE,java.sql.Types

VARBINARY_MAX_COL,2147483647,2147483647,0,varbinary,-3,VARBINARY

CHAR_COL,30,30,0,char,1,CHAR

VARCHAR_MAX_COL,2147483647,2147483647,0,varchar,12,VARCHAR

DATETIME2_COL,27,27,7,datetime2,93,TIMESTAMP

NCHAR_COL,30,30,0,nchar,1,CHAR

NVARCHAR_MAX_COL,1073741823,1073741823,0,nvarchar,12,VARCHAR

NUMERIC_P_S_COL,12,10,3,numeric,2,NUMERIC

NUMERIC_P_COL,12,10,0,numeric,2,NUMERIC

FLOAT_COL,22,15,0,float,8,DOUBLE

NVARCHAR_COL,30,30,0,nvarchar,12,VARCHAR

VARCHAR_COL,30,30,0,varchar,12,VARCHAR

As indicated on the documentation that comes with the Microsoft SQL Server JDBC Driver 3.0, data types varchar(max), nvarchar(max), varbinary(max) should map to java.sql.Types "LONGVARCHAR", "LONGVARCHAR"(JRE5), "LONGVARBINARY" respectively.

Would this be fix any time soon? Or we need to contact Microsoft customer service in our country?

Thanks,

Wilfred

It looks like the documentation is not correct here. From 3.0 the varchar(max) will be reported as varchar and similarly varchar(binary). If you want to know if the type is max type or standard type you have to also use precision to determine whether the type is max type or small type.

Re: changing user contexts, Wes Clarks post

Would execute as work for you?

http://msdn.microsoft.com/en-us/library/ms188354.aspx

Answering question, "Would execute as work for you?," the answer is no.  We don't want to change to the user's credentials after we get the pooled connection.  The JavaDoc at http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setClientInfo(java.lang.String, java.lang.String) says exactly what we want to do.  

I ran a test program using the 3.0 driver, and java.sql.DatabaseMetaData#getClientInfoProperties() returned a result set with now rows.  After calling connection.setClientInfo("ClientUser", "houdini"), a call to connection.getWarnings() returned a SQLWarning which said "This property is not supported: ClientUser."

I also tried workstationID and applicationName, both of which were reported as not supported.  As I stated above, we are setting applicationName on the JDBC URL because it is the same for all the connections in the pool.  We want to change the clientUser property dynamically when we borrow a connection from the pool.  

If we could modify applicationName cheaply and dynamically, we could slip in the user name.

Hi, sorry for distrurbing.

I can't find any help with problem:

Could not load the DLL SQLJDBC_XA.dll, or one of the DLLs it references. Reason: 193 ...

Removing dll lead to same error with different reason 126

I tried all supplied dll versions... same result

My config:

server: win2k8 64bit + mssql2008 64bit  

client: JDBC Driver 3.0 3.0.1301.101

Please help if you can... just simple hint, where is the problem...

DatabaseMetaData.getUserName() method returns login name instead of username.  Is this as per the JDBC specification